Re: Cursor question: WITH HOLD + FOR UPDATE

>From: uunet!ttd.teradyne.com!tokarski >Message-Id: <1992Apr29.095449.1@ttd.teradyne.com> >Subject: Cursor question: WITH HOLD + FOR UPDATE >Date: 29 Apr 92 14:54:12 GMT >X-Informix-List-Id: <news.1153> > >'Declare cursor' has two optional clauses that I'd like to use together - >WITH HOLD and FOR UPDATE. There is a reference to using this combination, >but it has to do with the opening and closing of the cursor. > >What I want to happen is to be able to lock a record (row) using the FOR >UPDATE clause, but preserve the lock if I perform a ROLLBACK. I was hoping >that the lock would be preserved because I had used the WITH HOLD clause, but >experience seems to indicate otherwise. > >I guess my first question is - am I attempting a 'legal' operation? Or is my >interpretation of the combination incorrect? I think you will find that both COMMIT and ROLLBACK release any locks you currently have. The purpose of a HOLD CURSOR is to ensure that you do not have to re-open the cursor after a COMMIT or ROLLBACK; all cursors except HOLD cursors are closed when either COMMIT or ROLLBACK occurs. Thus, there is no question of legality in your operation -- it simply isn't possible, or isn't what a HOLD CURSOR is designed to do. >If the WITH HOLD - FOR UPDATE combination won't behave as desired, is there >any mechanism that can? I'd rather not have to re-open the cursor and >re-fetch the record; it kind of defeats the purpose of the 'lock'... So what are you doing? An update which you find to be wrong and then retrying a new update? If so, you could consider creating a CURSOR WITH HOLD and not FOR UPDATE, which returns the primary key (PK) information you need. You can then have a second CURSOR FOR UPDATE (hold is unnecessary) which accepts the PK info and fetches all the data you need to see. If you need to do a ROLLBACK, you have lost your lock on the row, but you can re-use the second cursor to fetch the row again (and lock it again). There is obviously a small window of vulnerability during which time a second process might lock the record you're fiddling with -- you will need to decide whether that is either probable or serious, and if so, what to do about it. Yours sincerely, Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h>