Which of the following causes a lock that is being held by an application using the Cursor Stability isolation level to be released?
-
The cursor is moved to another row
-
The row the cursor is on is deleted by the application
-
The row the cursor is on is deleted by another application
-
The row the cursor is on needs to be updated by another application
In Cursor Stability isolation level, locks are released when the cursor moves to another row. This is the defining behavior of CS - it only locks the row currently positioned on. Deleting the row or update attempts do not release the lock until the cursor moves.
Under DB2's Cursor Stability (CS) isolation level, a share lock is held only on the row where the cursor is currently positioned. The lock is released the moment the cursor moves to another row (or the unit of work ends). IBM documents this precisely: 'the lock remains in effect until the cursor is repositioned or the transaction terminates; if repositioned, the held lock is released and a new lock is acquired.' The marked answer, 'the row the cursor is on is deleted by the application,' does the opposite: a delete by the owning application acquires/upgrades to an exclusive lock that is HELD until COMMIT, not released. Options C and D describe actions by another application, which cannot release a lock this application holds. Therefore the correct answer is 'The cursor is moved to another row' (id 515704).