I have a question concerning the FOR UPDATE Clause in CURSORs for IBM DB2 for z/OS. Assume Isolation Level Cursor Stability (standard parameter in BIND command). DB2 Version is 11.
My first question is: can a CURSOR that is coded with the FOR UPDATE clause prevent concurrent transactions form reading the row on which the CURSOR is currently positioned on? My second question is: does the UPDATE ... WHERE CURRENT OF ... statement detect when the updated row has been changed after the CURSOR has been opened and before it has been fetched from the CURSORs resultset?
I have read some contradictory statements on the web regarding these questions. As of my (current) understanding, the FETCH operation only aquires an update lock on the fetched row, so concurrent transactions can at least read the same row. The U-Lock is only promoted to an X-Lock in case the UPDATE WHERE CURRENT OF CURSOR is actually done (dependent on application logic). But this confuses me, because it then would not prevent a lost update phenomenon (when the concurrent process is allowed to read the value before the update in the first process is done it continues its processing with the old value and overwrites the update of the first process which has updated via CURRENT OF CURSOR).
Can a cursor that is coded with the
FOR UPDATEclause prevent concurrent transactions from reading the row on which the cursor is currently positioned?
No - with isolation level CS, Db2 will hold a U lock on the current row which is compatible with the S locks potentially required (see later comments about the CURRENTDATA bind parameter and it's impact on avoidance of the S lock for readers).
Does the
UPDATE ... WHERE CURRENT OFstatement detect when the updated row has been changed after the cursor has been opened and before it has been fetched from the CURSORs result set?
No - with isolation level CS, Db2 will not acquire a lock until the row is read. If you require the data to remain unchanged after OPEN CURSOR you need a different isolation level.
But this confuses me, because it then would not prevent a lost update phenomenon (when the concurrent process is allowed to read the value before the update in the first process is done it continues its processing with the old value and overwrites the update of the first process which has updated via CURRENT OF CURSOR).
Assuming both transactions are using FOR UPDATE and UPDATE ... WHERE CURRENT OF this scenario cannot happen. Each read would attempt to acquire a U lock. Since U locks are incompatible with each other the second read would wait on the first U lock to be released. (https://www.ibm.com/docs/en/db2-for-zos/12?topic=locks-lock-modes-compatibility)
For the more complex case where one (or both) of the transactions are not using FOR UPDATE and UPDATE ... WHERE CURRENT OF there are opportunities for the lost update phenomenon to occur.
Long ago, Db2 introduced bind parameter CURRENTDATA to help control this behavior.
Db2 manual - Choosing CURRENTDATA Option
Gareth has some great articles on this with much more detail - Db2 for z/OS Locking for Application Developers Part 8
To completely guard against the risk of losing an update, a good approach is to add predicates to ensure the update only occurs against the expected data. Gareth provides three options for this in Part 9 of his blog on locking. The general idea is to have something like an update timestamp that is always updated when any part of the row is updated. Then include a predicate in the WHERE clause of the UPDATE statement to ensure that the update will only occur if the update timestamp is the same as when the row was originally read. The ROW CHANGE TIMESTAMP feature in Db2 9 makes this approach easier.