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

"datasyst" <datasyst_at_ix.netcom.com> wrote in message news:3B1B1564.E57EFAAE_at_ix.netcom.com...
> We are using oracle 8.1.6.3 on Solaris 2.6 using JDBC. We have a table
> in which different users are inserting. This table is used to process
> the inserted records and delete them once processed. We have a process
> which issues "select for update with order by pk which is a sequence
> column" on these tables, and deletes the records from the result set and
> commits at the end. When multiple instances of these processes are run
> from different sessions, my understanding is that the sessions with
> select for update should wait till the current session which holds the
> lock on active set is committed.
>
> The problem is once that oracle issues ora-60 deadlock detected and
> rollsback one of the select for update statements.
>
> From the trace files it says that process A(session A) acquired X lock
> on record 1 and process B(session B) acquired X lock on record 2. Then
> process A is waiting on record 2 for X lock, and Process B trying to
> acquire X lock on Record 1. Since these processes are using select for
> update shouldn't the process B wait till Process A is commited before
> deleting the records held by Process A.
>
> Our intention is to avoid deadlock by issuing select for update. Any
> ideas as to why oracle is detecting a deadlock.

Oddly enough, Oracle is detecting a deadlock because one exists. If I select for update row A, I take an exclusive lock on it. Until I commit or rollback, I retain that lock. That prevents you from acquiring a lock on row A. But a transaction can consist of selecting for update row A, then selecting for update row B. The commit would release both locks.

So, if I select for update A but don't commit.... Then you select for update row B, but don't commit.... Then I select for update row B, I hang (you have the lock on row B) Now you select for update row A, and you will hang (because I have the lock on row A)

Unless something was done, we'd sit there for ever. Oracle fortunately detects the closed loop, and terminates one of our transaction (and I can never work out which session it will terminate, but I think it is the one that first closes the loop -in this case, youn selecting for update row A). That causes your last select for update to be rolled back. But you retain the lock on row B. I'm still stuck waiting for that lock until you commit or rollback your original select for update.

So the specific answer to your question is that 'select for update' shouldn't 'do' anything that a normal update wouldn't do in terms of 'waiting for something to happen'. A select for update acquires an exclusive row lock on all the rows affected by the select. That's just as exclusive, and just as not likely to wait for anything, as issuing a simple 'update x number of rows' statement.

Regards
HJR
>There are no FKs, and
> we are using dedicated connections.
>
> TIA,
> Madhu
>
Received on Tue Jun 05 2001 - 06:53:46 CDT