Locking order in cursors (Oracle)

Question

I wonder if the order in which records are locked is guaranteed when I use SELECT ... FOR UPDATE in cursors ? For example,

DECLARE 
CURSOR test_cursor IS 
SELECT field1, field2 
FROM table1 
ORDER BY pk_field
FOR UPDATE OF field1;

When I open such a cursor, will it always lock records in the order of pk_field ?

From what I read so far order of locking is not guaranteed for regular SELECT ... ORDER BY pk_field FOR UPDATE , but I'm not sure if it's true for cursors. I'd expect it does lock in the order set in ORDER BY clause for cursors; however, I cannot find confirmation.

Thank you.

Answer 1

The order in which individual rows will be locked is undefined and certainly not something you should rely on since it will depend on the query plan.

In general, I would expect that the rows would not be locked in order-- the optimizer would generally expect it to be more efficient to access all the data unordered and then apply the ORDER BY. If there was a composite index on pk_field, field1, field2, I could see the optimizer potentially choosing to do an ordered scan of the index and then locking each row in the table but that seems unlikely.