Re: HELP! Selecting and locking rows in a transaction

Marco Penengo writes: > within a transaction I need to select some records from a table (in a database > created in MODE ANSI) and lock them in exclusive mode to avoid that other users |> modify these records until the end of the transaction. |> My test contains this 4GL code: |> |> SET ISOLATION TO REPEATABLE READ |> DECLARE jcurscab CURSOR FOR |> SELECT columns FROM table |> WHERE condition |> FOR UPDATE |> FOREACH jcurscab INTO record[].* |> .. |> .. |> END FOREACH |> wait for user input |> COMMIT |> |> Using the code in two different programs with different condition I |> noted that if one of them is blocked waiting for input another copy of |> the program the SELECT fails with error -243. |> |> Do these statements lock all the table or just the rows that satisfy the |> condition ? How do I lock ONLY the records that match condition ? Your problem is the result of the combination of REPEATABLE READ and whatever your "condition" is. A RR isolation level will caused all EXAMINED rows to be locked in, typically in share mode. If your condition uses a column or columns that has a unique key associated with it, then only the rows actually matching your condition will be examined and thus locked. If, however, your condition causes more rows than are returned to be examined, the additional, examined but not returned rows will also be locked (the reason for this revolves around set theory - a RR guarantees the integrety of the set, and thus all elements that are potentially a member of the set must be protected to insure that set will be identical after another repetition of the query within a transaction). In the worst case, if your condition requires a sequential scan of the table, all the rows in the table will be locked (actually, a table lock will be used). Now I am a bit sakey on this next bit, as I don't have time to run a test to confirm it, but I believe that by declaring your cursor for update, rather than shared locks being used, update locks are used. Since 2 transactions cannot have an update lock on the same row, your second program is failing. As a solution, I suggest foregoing the RR, or using a condition that utilizes a primary key in the table(s). You could also forego the FOR UPDATE, then perform a bogus UPDATE on the rows as you fetch them, thus only placing exclusive locks on the rows you actually fetch. You probably want to fetch the rowid, and perform your update where rowid = selected rowid. Dave