Cursor Stability isolation

A reader with Cursor Stability isolation acquires a shared lock on the row that is currently fetched. This action assures that no other user can update the row until the user fetches a new row.

In the example for a cursor in Figure 1, at fetch a row the database server releases the lock on the previous row and places a lock on the row being fetched. At close the cursor, the server releases the lock on the last row.
Figure 1. Locks placed for cursor stability
set isolation to cursor stability
declare cursor for SELECT * FROM customer
open the cursor
while there are more rows
   fetch a row
   do work
end while
close the cursor

If you do not use a cursor to fetch data, Cursor Stability isolation behaves in the same way as Committed Read. No locks are actually placed.

Copyright© 2018 HCL Technologies Limited