Delete and Locking

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? Thanks, dc97 Sent via Deja.com http://www.deja.com/ Before you buy.

You may have 5 indexes on the table and the key itself and the next key in each index have to be locked that's 10 locks per row. Art S. Kagel dc97@my-deja.com wrote: > > 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? > > Thanks, > > dc97 > > Sent via Deja.com http://www.deja.com/ > Before you buy.

I have 8 indexes on the table, so maybe I am getting a lock for each one. Where might the other 2 locks be coming from? In article <398F2BB2.1DF1A9C6@bloomberg.net>, kagel@bloomberg.net wrote: > You may have 5 indexes on the table and the key itself and the next > key in each index have to be locked that's 10 locks per row. > > Art S. Kagel > > dc97@my-deja.com wrote: > > > > 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? > > > > Thanks, > > > > dc97 > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.

Oops, old knowledge surfaced first, hazard of growing older. Online 5.x kept two locks per index 7.xx uses only one per index, that's 8, plus one for the deleted row itself makes 9, unless there is another hidden index, say created by a constraint, that you have forgotten about I do not know where the 10th one came from. Art S. Kagel dc97@my-deja.com wrote: > > I have 8 indexes on the table, so maybe I am getting a lock for each > one. Where might the other 2 locks be coming from? > > In article <398F2BB2.1DF1A9C6@bloomberg.net>, > kagel@bloomberg.net wrote: > > You may have 5 indexes on the table and the key itself and the next > > key in each index have to be locked that's 10 locks per row. > > > > Art S. Kagel > > > > dc97@my-deja.com wrote: > > > > > > 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? > > > > > > Thanks, > > > > > > dc97 > > > > > > Sent via Deja.com http://www.deja.com/ > > > Before you buy. > > > > Sent via Deja.com http://www.deja.com/ > Before you buy.