Running out of locks
A user updating ~17,000 rows with row-level locking hit "out of locks" even after raising LOCKS from 50,000 to 100,000, and asked how that was possible. Respondents explained that an update takes a lock on each data row plus a lock for each affected index key, so a table with around five indexes easily exceeds 100,000 locks. The recommended fix, since he was the only user, was to run LOCK TABLE <tab> IN EXCLUSIVE MODE before the update (then UNLOCK), which uses a single lock — with a warning to watch for long transactions/log space if logging is enabled.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Last night while running an update for ~17k rows Informix reported to me that I was out of locks. At the time I was the only user in the database, the table had row level locking on and the max # of locks was set at 50000. I increased the locks to one hundred thousand and still got the same error. How can an update of 17k rows use more than 100,000 locks?
I believe there will be a lock for data and a lock for each index per row. So if you have five indexes, you would need 6 * 17,000 (102,000) locks. Rob Schmitz 913-345-6281 Rob.B.Schmitz@mail.sprint.com -----Original Message----- From: RPhillips [mailto:RPhillips@ce-a.com] Sent: Wednesday, March 05, 2003 8:42 AM To: ids; forum.subscriber Subject: Running out of locks [591] Last night while running an update for ~17k rows Informix reported to me that I was out of locks. At the time I was the only user in the database, the table had row level locking on and the max # of locks was set at 50000. I increased the locks to one hundred thousand and still got the same error. How can an update of 17k rows use more than 100,000 locks?
Hi If you the only user you can do 'lock table tabname in exclusive mode; update ...' If this is DB with logging you sould do it in transaction (Be awere from LONG TRANSACTION !). If the DB is without logs (you can set it for this and set it back) you should do after the update 'unlock table tabname ' Uri Phillips, Rob wrote: >Last night while running an update for ~17k rows Informix reported to me >that I was out of locks. At the time I was the only user in the database, >the table had row level locking on and the max # of locks was set at 50000. >I increased the locks to one hundred thousand and still got the same error. >How can an update of 17k rows use more than 100,000 locks? > > > > > >
Rob As well as locking each row the engine will also lock each index entry that is removed, so you only need 5 indexes on this table to blow 100,000 locks. If you are the only user then issue the command 'lock table nnn in exclusive mode' and watch the engine just use one lock. Your next problem could be a long transaction rollback when you run out of log space :-) Keith -> -----Original Message----- -> From: Phillips, Rob [mailto:RPhillips@ce-a.com] -> Sent: Wednesday, March 05, 2003 2:42 PM -> To: ids@iiug.org -> Subject: Running out of locks [591] -> -> -> Last night while running an update for ~17k rows Informix -> reported to me -> that I was out of locks. At the time I was the only user in -> the database, -> the table had row level locking on and the max # of locks -> was set at 50000. -> I increased the locks to one hundred thousand and still got -> the same error. -> How can an update of 17k rows use more than 100,000 locks? -> ******************************************************************************** ** This message is sent in strict confidence for the addressee only. It may contain legally privileged information. The contents are not to be disclosed to anyone other than the addressee. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission. This footnote also confirms that this email message has been swept for the presence of computer viruses, however we cannot guarantee that this message is free from such problems. ******************************************************************************** **
Look at the indices you have on the table and remember that updating one record will require locks on both the data row and the index keys involved. If you are the only person on the system, you can lock the table in exclusive mode, perform your update, unlock the table and proceed on home. Take care. Clifton ----- Original Message ----- From: "Phillips, Rob" <RPhillips@ce-a.com> To: <ids@iiug.org> Sent: Wednesday, March 05, 2003 8:42 AM Subject: Running out of locks [591] > Last night while running an update for ~17k rows Informix reported to me > that I was out of locks. At the time I was the only user in the database, > the table had row level locking on and the max # of locks was set at 50000. > I increased the locks to one hundred thousand and still got the same error. > How can an update of 17k rows use more than 100,000 locks?
Don't forget to include a lock per index row as well. > -----Original Message----- > From: Phillips, Rob [mailto:RPhillips@ce-a.com] > Sent: Wednesday, March 05, 2003 9:42 AM > To: ids@iiug.org > Subject: Running out of locks [591] > > > Last night while running an update for ~17k rows Informix > reported to me > that I was out of locks. At the time I was the only user in > the database, > the table had row level locking on and the max # of locks was > set at 50000. > I increased the locks to one hundred thousand and still got > the same error. > How can an update of 17k rows use more than 100,000 locks? > "CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel Retail. This email message and all attachments may contain legally privileged and confidential information intended solely for the use of the addressee. If you are not the intended recipient, you should immediately stop reading this message and delete it from the system. Any unauthorized reading, distribution, copying, or other use of this message or its attachments is strictly prohibited. All personal messages express solely the sender's views and not those of WHSmith USA Travel Retail. This message may not be copied or distributed without this disclaimer."