Size of Locks

Hi *, I have a table with one million rows and more than 70 Processes that want to modify large parts of it. I set the LOCKS Variable in onconfig to the maximum (8000000) and now I see that this takes about 400MB of RAM. Is this real? I have to do row level locking because else I get lots of deadlocks. Do you have any ideas? Thank you Thomas -- Thomas Mieslinger Mobil: +49 170 510 6095 Koenigstrasse 44 Fon: +49 661 901 3975 36037 Fulda Fax: +49 661 901 4605 Germany eMail: thomas@mieslinger.de

Thomas Mieslinger wrote: > > Hi *, > > I have a table with one million rows and more than 70 Processes that > want to modify large parts of it. I set the LOCKS Variable in onconfig > to the maximum (8000000) and now I see that this takes about 400MB of > RAM. If correct that works out to about 50 bytes per lock. OL5 Admin Guide used to document the size of a lock structure at 32 bytes so that's not unreasonable. > Is this real? > > I have to do row level locking because else I get lots of deadlocks. > > Do you have any ideas? You might redesign the applications to hold fewer locks. Methods include opening cursors WITH HOLD so you can commit partway through the processing either every Nth update or after each logical unit of work. The commit will release locks held by the committed rows. This is the method that dbload and my dbcopy.ec utility use. Another option, used in my dbdelete utility is to suck the data out of the driving cursor into memory or other temporary storage and close it. Then you can update or delete without locking more than one or a few rows based on the stored key data. FWIW. Art S. Kagel

In article <3814BFA4.C6D01000@bloomberg.net>, Art S. Kagel <kagel@bloomberg.net> writes >Thomas Mieslinger wrote: >> >> Hi *, >> >> I have a table with one million rows and more than 70 Processes that >> want to modify large parts of it. I set the LOCKS Variable in onconfig >> to the maximum (8000000) and now I see that this takes about 400MB of >> RAM. > >If correct that works out to about 50 bytes per lock. OL5 Admin Guide >used to document the size of a lock structure at 32 bytes so that's not >unreasonable. > 43.2 bytes to be exact! A number I will never forget... > >> Is this real? >> >> I have to do row level locking because else I get lots of deadlocks. >> >> Do you have any ideas? > >You might redesign the applications to hold fewer locks. Methods include >opening cursors WITH HOLD so you can commit partway through the processing >either every Nth update or after each logical unit of work. The commit >will release locks held by the committed rows. This is the method that >dbload and my dbcopy.ec utility use. Another option, used in my dbdelete >utility is to suck the data out of the driving cursor into memory or other >temporary storage and close it. Then you can update or delete without >locking more than one or a few rows based on the stored key data. FWIW. > >Art S. Kagel -- David Williams

DO what Art said, or :-) Also, you can lock tables in exclusive mode, which only produces one lock. Read your manual on this. The caveat is, you can only do this in a transaction on data base that uses Buffered/Unbuffered logging, or turn off the logging ( ontape -s -N database ) then you can lock the table in exclusive mode without doing the transaction "Begin Work, etc." This produces the one-lock on the table, bypasses the logging and will probably run very fast for you. :-) But of course I'd only do the non-logged approach if you haven't any other way. If you decide to stay logged you'll want to watch your checkpoints so that you don't get long transactions, and make sure you have enough logs to handle the transaction volume. Tim Thomas Mieslinger wrote: > > Hi *, > > I have a table with one million rows and more than 70 Processes that > want to modify large parts of it. I set the LOCKS Variable in onconfig > to the maximum (8000000) and now I see that this takes about 400MB of > RAM. > > Is this real? > > I have to do row level locking because else I get lots of deadlocks. > > Do you have any ideas? > > Thank you > > Thomas > -- > Thomas Mieslinger Mobil: +49 170 510 6095 > Koenigstrasse 44 Fon: +49 661 901 3975 > 36037 Fulda Fax: +49 661 901 4605 > Germany eMail: thomas@mieslinger.de -- . .- .-- .--- .---- Tim Schaefer .----- tschaefe@bellsouth.net .---- http://www.inxutil.com .--- http://www.datad.com .-- .- .

Tim Schaefer wrote: > > DO what Art said, or > > :-) > > Also, you can lock tables in exclusive mode, which only produces one lock. > Read your manual on this. [SNIP} THAT was the third method I wanted to list, but DUH I vegged out. Thanks Tim. Art S. Kagel

Don't worry Art, it doesn't affect your total score for the month. :-) Tim "Art S. Kagel" wrote: > > Tim Schaefer wrote: > > > > DO what Art said, or > > > > :-) > > > > Also, you can lock tables in exclusive mode, which only produces one lock. > > Read your manual on this. > [SNIP} > > THAT was the third method I wanted to list, but DUH I vegged out. Thanks > Tim. > > Art S. Kagel -- . .- .-- .--- .---- Tim Schaefer .----- tschaefe@bellsouth.net .---- http://www.inxutil.com .--- http://www.datad.com .-- .- .