Re: Delete and Locking

From: dc97@my-deja.com > >Question/Issue: > I am deleting from a table (table uses row level locking) within a >4gl application and committing every 1000 transactions. The delete is >constrained on the rowid " delete from table where rowid = ? ". > If I do an onstat -u and find the session_id of the application I >notice that the locks (8th column on onstat -u output) will go up to >10000 and once at 10000 it will start over at 0. I suspect that when >the locks go from 10000 to 0 the application is committing the >transactions. Since the table is setup to use row level locking I >thought that the locks would go to 1000 and not the 10000. I thought >there would be a 1 to 1 relationship between my deletes and locks. > My question is this: During a delete such as the one I mentioned >above, what rows beside the table in which it was deleting from , would >become locked? Will rows of some of the system tables become locked as >well? If so which tables? If it is a 1 to 1 relationship then the >application must be buggy, right? Any referential integrity? ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

No RI, but Art suggested that it has to do with the number of indexes on the table. I have 8, so if I have a lock for every index, that is 8 for every transaction. I wonder where the other 2 are coming from? Thanks In article <8momoq$bv4$1@news.xmission.com>, "Obnoxio The Clown" <obnoxio@hotmail.com> wrote: > > From: dc97@my-deja.com > > > >Question/Issue: > > I am deleting from a table (table uses row level locking) within a > >4gl application and committing every 1000 transactions. The delete is > >constrained on the rowid " delete from table where rowid = ? ". > > If I do an onstat -u and find the session_id of the application I > >notice that the locks (8th column on onstat -u output) will go up to > >10000 and once at 10000 it will start over at 0. I suspect that when > >the locks go from 10000 to 0 the application is committing the > >transactions. Since the table is setup to use row level locking I > >thought that the locks would go to 1000 and not the 10000. I thought > >there would be a 1 to 1 relationship between my deletes and locks. > > My question is this: During a delete such as the one I mentioned > >above, what rows beside the table in which it was deleting from , would > >become locked? Will rows of some of the system tables become locked as > >well? If so which tables? If it is a 1 to 1 relationship then the > >application must be buggy, right? > > Any referential integrity? > ________________________________________________________________________ > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com > > Sent via Deja.com http://www.deja.com/ Before you buy.

dc97@my-deja.com wrote in message <8mpjj1$alf$1@nnrp1.deja.com>... >No RI, but Art suggested that it has to do with the number of indexes on >the table. I have 8, so if I have a lock for every index, that is 8 for >every transaction. I wonder where the other 2 are coming from? > ?? Seems odd to me... go into dbaccess and try ^^^^^^^^^^ CREATE PROCEDURE fred() BEGIN WORK; <<DELETE 20 row from table>>; RUN "/bin/sleep"; <*** Change to wherever the <*** sleep command is ROLLBACK WORK; END PROCEDURE; EXECURE PROCEDURE fred(); DROP PROCEDURE fred() And run an onstat -k whilst it is running. >Thanks > >In article <8momoq$bv4$1@news.xmission.com>, > "Obnoxio The Clown" <obnoxio@hotmail.com> wrote: >> >> From: dc97@my-deja.com >> > >> >Question/Issue: >> > I am deleting from a table (table uses row level locking) >within a >> >4gl application and committing every 1000 transactions. The delete >is >> >constrained on the rowid " delete from table where rowid = ? ". >> > If I do an onstat -u and find the session_id of the application >I >> >notice that the locks (8th column on onstat -u output) will go up to >> >10000 and once at 10000 it will start over at 0. I suspect that >when >> >the locks go from 10000 to 0 the application is committing the >> >transactions. Since the table is setup to use row level locking I >> >thought that the locks would go to 1000 and not the 10000. I >thought >> >there would be a 1 to 1 relationship between my deletes and locks. >> > My question is this: During a delete such as the one I >mentioned >> >above, what rows beside the table in which it was deleting from , >would >> >become locked? Will rows of some of the system tables become locked >as >> >well? If so which tables? If it is a 1 to 1 relationship then the >> >application must be buggy, right? >> >> Any referential integrity? >> >________________________________________________________________________ >> Get Your Private, Free E-mail from MSN Hotmail at >http://www.hotmail.com >> >> > > >Sent via Deja.com http://www.deja.com/ >Before you buy.