Re: Cursor question: WITH HOLD + FOR UPDATE

> '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? What you are doing is legal but does not do what you want. The WITH HOLD phrase only refers to keeping the cursor open through transactions. This allows you to do process a series of rows within individual transactions without having to re-open the cursor each time. > 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'... No. This is a restriction in SQL not an Informix restriction. SQL states that locks only exist for the duration of a transaction which ends at either a COMMIT or a ROLLBACK. Some time ago I suggested on the net the introduction of a FETCH WITH HOLD phrase that would hold a lock beyond the end of a transaction with the lock being released dependant on the isolation level at next FETCH or ClOSE CURSOR. As I got no response I never formally raised it anybody else interested? > Joseph P. Tokarski tokarski@ttd.teradyne.com Cheers - Jim -------------------------------------------------------------------- Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM Company: DHL Systems Inc Phone: (415) 358-5911 (Work) Address: 1700 S. Amphlett Blvd. (415) 882-9728 (Home) San Mateo, CA 94402 Fax: (415) 571-6429 --------------------------------------------------------------------