Question about promotable locks

Here's the scenario: 1. DECLARE a cursor with FOR UPDATE and WITH HOLD 2. FETCH a record 3. DECLARE a second cursor 4. FETCH a record from this second cursor and UPDATE or DELETE it. 5. End the transaction (COMMIT) The question is, is the promotable lock placed on the first row retrieved still in place? The informix documentation says that all lock are release when a transaction ends. If that is the case the first row will be available for update/delete by another process while the first process still has it and thinks no one else can update it. Thanks

V Pastore wrote in message <93pltt$3sa$1@bob.news.rcn.net>... >Here's the scenario: >1. DECLARE a cursor with FOR UPDATE and WITH HOLD >2. FETCH a record >3. DECLARE a second cursor >4. FETCH a record from this second cursor and UPDATE or DELETE it. >5. End the transaction (COMMIT) > >The question is, is the promotable lock placed on the first row retrieved >still in place? The informix documentation says that all lock are release >when a transaction ends. If that is the case the first row will be >available for update/delete by another process while the first process still >has it and thinks no one else can update it. No, the lock is not still in place. Even though the cursor is HELD (open), it kinda doesn't point to the row any more. With normal cursors (not UPDATE) you wouldn't really notice, because you've already fetched the row and you can't refetch it unless it's a scoll cursor; but they can't be combined with an update cursor anyway. If you fetch the next record through the update cursor, then the promotable lock is freshly applied to the new row. In the scenario above, you could be using the same cursor. The update cursor may fetch all the fields of the record, and then it may be used to update the row using the WHERE CURRENT OF syntax of the UPDATE or DELETE statements. However you could get into trouble with the locking problem that prompted this thread in the first place. Your scenario could also suffer if step 1 has to perform a bit of searching. I recommend that the master cursor (finding the rows) be a non-UPDATE cursor. It may be a HOLD cursor according to needs. If you wish to reapply the lock to the same row, then reopen the cursor. If that leads to a lot of work re-seeking through other rows, then apply the following logic. I'm imagining here that you are presenting a selection set after a Find operation performed by a user, or some similar situation where you may have more than one row of interest in a selection set. 1) Declare a normal cursor or a scroll cursor. Include the PK or the rowid in the selected fields. This cursor could or should be a hold cursor. You may also fetch the set of interesting rows into a temp table or program array as an alternative, and then process from that temp table or program array. 2) Begin work 3) Declare an update cursor which goes exactly to the target row. This cursor does not need to be a HOLD cursor. Open this cursor using the PK or the rowid stored in step 1. You now have your lock if it succeeds. If this cursor fails to open (I mean, fails to fetch), then either some other process now owns the row and you should wait for it or take avoidance actions, or maybe the row has been deleted since the time it was originally found by this process. If you are fetching by rowid, then in principle, talking safe-programming here, you really should check that it's truly the same record by comparing the PK with the remembered one, or testing that the original WHERE clause would still apply. In practice, you don't often have to test this. It depends on the likelihood of the row being deleted, or someone applying a cluster index on a live system (who said you could do that???). Knowing your data, software and people is a great help here. By the way, applying a cluster index on a busy system is naughty for other reasons: mainly, it may provoke long transaction rollbacks which are very unpleasant. Fetching by PK saves you from this extra theoretical work, but makes it hard to implement library routines which can do the work on behalf of 50 - 2000 programs. It can be done but it just gets ugly in 4GL. It would be a breeze in Perl::DBI or ESQL/C with suitable coding. If you are hand-writing all parts of each 4GL program then use the PK and stay away from rowid's because they are the devil's spawn. 4) Do the work. You may update or delete the row using WHERE CURRENT OF cursor. See the cursor_name() function if you wish to prepare an UPDATE or DELETE statement from a string. 5) Commit or rollback. The update cursor loses the lock and the row. With the programming model here, I hope you can see that it doesn't need to be a hold cursor, because the stored row info from step 1 will let you move onto the next row, or to reassert the lock of the current row if your user chooses to mess with the same row. NOTE: when I say "declare a cursor ..." I don't necessarily mean every time. If the WHERE clause is NOT flexible (ie coming from a CONSTRUCT statement) then you should declare as many cursors as possible at the start of the program and then merely open and fetch from it. This can lead to tremendous performance benefits because the engine doesn't have to do all the work of analysing the SQL, looking up the table definitions and associated permissions, statistics, contraints etc etc etc. For many cursors, the engine can often also pick the optimal query path once only. I've seen reports go from 16 hours to 20 minutes runtime (an actual case) when all the inline select statements were converted to cursors. Strive to use cursors for any SQL that is used more than two or three* times in a particular run of a program. * depends how fussy you are about the small-time SQL statements that all programs inevitably have. Is that enough information?

Thanks, that's a lot of information to digest. You say that the first FETCHed row is no longer available, I guess that would mean I would get a error of some sort if I tried to do an update where CURRENT OF? If that's the case it wouldn't be so bad because at least it would prevent me from overwriting some other processes update. I'll study all you said, and again, thanks. Ray Pastore Andrew Hamm <ahamm@sanderson.net.au> wrote in message news:3a623d74@news.iprimus.com.au... > V Pastore wrote in message <93pltt$3sa$1@bob.news.rcn.net>... > >Here's the scenario: > >1. DECLARE a cursor with FOR UPDATE and WITH HOLD > >2. FETCH a record > >3. DECLARE a second cursor > >4. FETCH a record from this second cursor and UPDATE or DELETE it. > >5. End the transaction (COMMIT) > > > >The question is, is the promotable lock placed on the first row retrieved > >still in place? The informix documentation says that all lock are release > >when a transaction ends. If that is the case the first row will be > >available for update/delete by another process while the first process > still > >has it and thinks no one else can update it. > > No, the lock is not still in place. Even though the cursor is HELD (open), > it kinda doesn't point to the row any more. With normal cursors (not UPDATE) > you wouldn't really notice, because you've already fetched the row and you > can't refetch it unless it's a scoll cursor; but they can't be combined with > an update cursor anyway. If you fetch the next record through the update > cursor, then the promotable lock is freshly applied to the new row. > > In the scenario above, you could be using the same cursor. The update cursor > may fetch all the fields of the record, and then it may be used to update > the row using the WHERE CURRENT OF syntax of the UPDATE or DELETE > statements. However you could get into trouble with the locking problem that > prompted this thread in the first place. Your scenario could also suffer if > step 1 has to perform a bit of searching. I recommend that the master cursor > (finding the rows) be a non-UPDATE cursor. It may be a HOLD cursor according > to needs. > > If you wish to reapply the lock to the same row, then reopen the cursor. If > that leads to a lot of work re-seeking through other rows, then apply the > following logic. I'm imagining here that you are presenting a selection set > after a Find operation performed by a user, or some similar situation where > you may have more than one row of interest in a selection set. > > 1) Declare a normal cursor or a scroll cursor. Include the PK or the rowid > in the selected fields. This cursor could or should be a hold cursor. You > may also fetch the set of interesting rows into a temp table or program > array as an alternative, and then process from that temp table or program > array. > > 2) Begin work > > 3) Declare an update cursor which goes exactly to the target row. This > cursor does not need to be a HOLD cursor. Open this cursor using the PK or > the rowid stored in step 1. You now have your lock if it succeeds. If this > cursor fails to open (I mean, fails to fetch), then either some other > process now owns the row and you should wait for it or take avoidance > actions, or maybe the row has been deleted since the time it was originally > found by this process. > > If you are fetching by rowid, then in principle, talking safe-programming > here, you really should check that it's truly the same record by comparing > the PK with the remembered one, or testing that the original WHERE clause > would still apply. In practice, you don't often have to test this. It > depends on the likelihood of the row being deleted, or someone applying a > cluster index on a live system (who said you could do that???). Knowing your > data, software and people is a great help here. By the way, applying a > cluster index on a busy system is naughty for other reasons: mainly, it may > provoke long transaction rollbacks which are very unpleasant. > > Fetching by PK saves you from this extra theoretical work, but makes it hard > to implement library routines which can do the work on behalf of 50 - 2000 > programs. It can be done but it just gets ugly in 4GL. It would be a breeze > in Perl::DBI or ESQL/C with suitable coding. If you are hand-writing all > parts of each 4GL program then use the PK and stay away from rowid's because > they are the devil's spawn. > > 4) Do the work. You may update or delete the row using WHERE CURRENT OF > cursor. See the cursor_name() function if you wish to prepare an UPDATE or > DELETE statement from a string. > > 5) Commit or rollback. The update cursor loses the lock and the row. With > the programming model here, I hope you can see that it doesn't need to be a > hold cursor, because the stored row info from step 1 will let you move onto > the next row, or to reassert the lock of the current row if your user > chooses to mess with the same row. > > NOTE: when I say "declare a cursor ..." I don't necessarily mean every time. > If the WHERE clause is NOT flexible (ie coming from a CONSTRUCT statement) > then you should declare as many cursors as possible at the start of the > program and then merely open and fetch from it. This can lead to tremendous > performance benefits because the engine doesn't have to do all the work of > analysing the SQL, looking up the table definitions and associated > permissions, statistics, contraints etc etc etc. For many cursors, the > engine can often also pick the optimal query path once only. > > I've seen reports go from 16 hours to 20 minutes runtime (an actual case) > when all the inline select statements were converted to cursors. Strive to > use cursors for any SQL that is used more than two or three* times in a > particular run of a program. > > * depends how fussy you are about the small-time SQL statements that all > programs inevitably have. > > Is that enough information? > > >

Andrew Hamm wrote: > > V Pastore wrote in message <93pltt$3sa$1@bob.news.rcn.net>... > >Here's the scenario: > >1. DECLARE a cursor with FOR UPDATE and WITH HOLD > >2. FETCH a record > >3. DECLARE a second cursor > >4. FETCH a record from this second cursor and UPDATE or DELETE it. > >5. End the transaction (COMMIT) > > > >The question is, is the promotable lock placed on the first row retrieved > >still in place? The informix documentation says that all lock are release > >when a transaction ends. If that is the case the first row will be > >available for update/delete by another process while the first process > still > >has it and thinks no one else can update it. > > No, the lock is not still in place. That depends on the isolation level, of course. At REPEATABLE READ, there will still be a shared lock on the row after the cursor moves past so that the row won't be altered by anybody else, thus breaking the repeatability of the REPEATABLE READ isolation level. But there is also truth in what you say: the lock will no longer be promotable. And at lower levels of isolation (DIRTY READ, COMMITTED READ or CURSOR STABILITY), the lock is not held after the cursor moves on unless the row is updated. And only MODE ANSI databases operate at REPEATABLE READ by default. > Even though the cursor is HELD (open), > it kinda doesn't point to the row any more. >[...lots of interesting stuff snipped...] > > Is that enough information? -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"