Re: Ora-00060 Deadlock Problem. Select for update behavior.

The crucial point is that "select ... for update" always locks the *entire* resultset. After retrieving all result rows Oracle tries to lock, then it sorts them.

The sequence in which they are retrieved is undefined. So you cannot assume that process A and process B try to retrieve and to lock their respective rows in the same order. Therefore a deadlock may occur.

Easiest solution is to issue a "lock table in exclusive mode", then delete (without selecting for update), then commit. Another option would be to limit the resultset to one row by the condition "and rownum <= 1".

Martin Received on Wed Jun 06 2001 - 03:55:11 CDT