Help with large Updates/Locks

We have been trying to find a solution to doing large table updates, without running out of locks. The process doing the updates cannot lock the table in exclusive mode, because other never ending processes need un-interrupted access to this same table. Increasing the number of locks is a "tail chasing the dog" solution, because no matter how many lock we allocate, at some point the number of rows being updated will exceed that. So, is anyone aware of a way to use SQL (not esql) to update tables in blocks of rows? Ie 1000 at a time? Is it possible to select 1000 rows, that meet update criteria, update them and then loop until no more records are found? Please send any and all suggestions to zigzag@infi.net and to this newsgroup. Thank you.

Hmmm... What locking mode is already on the table ?? If it's row level, try altering it to page level. Then, at least, if you have lots of rows per page, it will only use a single lock resource to lock the page rather than each row within the page. How many rows per page does the table have ? If its AIX you have a 4k page ( all other systems you have 2K ). You can easily calculate it by dividing the ( page size - overhead ) by the row size. Failing that, if your applications are using the table while it's being updated, the result set returned to the other app can't be too bothered whether or not the information returned is up-to-date or not ?? ( or am i assuming too much ?? ). Can you set the isolation level to DIRTY READ of the app thats reading the table so that you could lock the entire table and STILL get access to it ? Have you run the UPDATE statement through SET EXPLAIN ( not necessarily the update but perhaps the WHERE clause using a SELECT statement ? ) This may indicate the number of locks that you would need by examining the number of rows that get returned. zigzag wrote in message <36AE1BD3.81664588@infi.net>... >We have been trying to find a solution to doing large table updates, >without running out of locks. The process doing the updates cannot lock >the table in exclusive mode, because other never ending processes need >un-interrupted access to this same table. > >Increasing the number of locks is a "tail chasing the dog" solution, >because no matter how many lock we allocate, at some point the number of >rows being updated will exceed that. > >So, is anyone aware of a way to use SQL (not esql) to update tables in >blocks of rows? Ie 1000 at a time? Is it possible to select 1000 rows, >that meet update criteria, update them and then loop until no more >records are found? > >Please send any and all suggestions to zigzag@infi.net and to this >newsgroup. > >Thank you. >

Sean, thank you for your reply. To answer your questions and give a better understanding of the situation I offer the following information: The table is (and must be row level locking). There are approx 50 daemons accessing and updating the table every few seconds and the rows they update are very "close" to each other. Page locking reduces concurrency to unacceptable levels and also has created deadlocks in our transactions. These 50 daemons are set to dirty read rows as you correctly guessed. Page size is 2K as you point out. But this is moot since row level locking is required. Part of the problem is understanding why so many locks are being used in the first place. In my latest example, I have 750 rows that are being updated. There are 8 columns in each row. The engine is configured for 5000 locks and I run out of them. Does the engine use multiple locks (one per column) on a single row. I always understood a maximum of one lock per row. If it uses one per column, that would require 6000 locks and I can understand why I'm running out, if not I can't figure out what is happening to consume all these locks. In either case, I still need to find some way to mass update rows without running out of locks. No matter how many I have at some point it won't be enough unless the job can be broken into reasonable sub jobs of a know quantity that can complete successfully. Jim

I may be wrong but I believe that you will also incur a lock per index, and are you sure your only locking the 750 rows involved in the update? Are the daemons only reading or might they also be updating? It looks like you have two options at this point. Increase the number of locks, or write a program, ESQL/C, 4GL, SPL etc. etc. to update via a cursor with an inbedded COMMIT WORK statement every X nu mber of updates. Good Luck Terry Hillick VP, CSCSi. zigzag wrote: > Sean, thank you for your reply. > > To answer your questions and give a better understanding of the > situation I offer the following information: > > The table is (and must be row level locking). There are approx 50 > daemons accessing and updating the table every few seconds and the rows > they update are very "close" to each other. Page locking reduces > concurrency to unacceptable levels and also has created deadlocks in our > transactions. These 50 daemons are set to dirty read rows as you > correctly guessed. Page size is 2K as you point out. But this is > moot since row level locking is required. > > Part of the problem is understanding why so many locks are being used in > the first place. In my latest example, I have 750 rows that are being > updated. There are 8 columns in each row. The engine is configured > for 5000 locks and I run out of them. Does the engine use multiple > locks (one per column) on a single row. I always understood a maximum > of one lock per row. If it uses one per column, that would require > 6000 locks and I can understand why I'm running out, if not I can't > figure out what is happening to consume all these locks. > > In either case, I still need to find some way to mass update rows > without running out of locks. No matter how many I have at some point > it won't be enough unless the job can be broken into reasonable sub jobs > of a know quantity that can complete successfully. > > Jim

zigzag wrote: > > Sean, thank you for your reply. > > To answer your questions and give a better understanding of the > situation I offer the following information: > > The table is (and must be row level locking). There are approx 50 > daemons accessing and updating the table every few seconds and the rows > they update are very "close" to each other. Page locking reduces > concurrency to unacceptable levels and also has created deadlocks in our > transactions. These 50 daemons are set to dirty read rows as you > correctly guessed. Page size is 2K as you point out. But this is > moot since row level locking is required. > > Part of the problem is understanding why so many locks are being used in > the first place. In my latest example, I have 750 rows that are being > updated. There are 8 columns in each row. The engine is configured > for 5000 locks and I run out of them. Does the engine use multiple > locks (one per column) on a single row. I always understood a maximum > of one lock per row. If it uses one per column, that would require Not one lock per column but not one per row either unless there are no indexed columns being updated (don't forget to count implicit indexes create to support various constraints). The engine also has to lock any index nodes that are being updated. Art S. Kagel