Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks for your answer. Then I think FOR UPDATE in cursors is quite dangerous feature which may cause deadlocks... –  a1ex07 Jun 10 '12 at 15:49
    
@a1ex07 - No more dangerous than, say, an UPDATE statement. It's entirely possible that if you have two sessions trying simultaneously to update the same set of rows that the two sessions would get a different query plan and that they would affect rows in a different order leading to a deadlock. But while it's possible, it's relatively unlikely. –  Justin Cave Jun 10 '12 at 15:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.