CODEWITHSUNDEEP
oracle-databasedeadlockundo-redo
user2813978
user2813978

Reputation: 151

Trying to understand combination of ORA-00060 and ORA-01555

I have two tables, TABLE A and TABLE B.

Transaction T1 (auto commit is set to false) updates TABLE A , reads TABLE B and again updates TABLE A then commits.

Transaction 2 deletes from TABLE B and has an after trigger to delete the relevant records from TABLE A.

Transaction 1 and Transaction 2 runs from different applications (i.e. run parallel)

Now I have a condition where the Transactions T1 and T2 got dead locked (ORA-00060) on TABLE A.

Before the deadlock error, I see that the Transaction 1 encountered ORA-01555 when trying to read TABLE B ( which is its second step).

Now I am confused that whether the Transactions T1 and T2 have the potential to deadlock each other under normal circumstances or the ORA-01555 has the part to play in the deadlock.

Any help here would be appreciated.

Upvotes: 0

Views: 319

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17944

I doubt the ORA-1555 has anything to do with the deadlock, unless it's triggering an exception handler in your code that is creating additional locks.

In fact, TABLE B can have nothing to do with the situation at all, since transaction T1 does not lock it. (I am interpreting "reads TABLE B" to mean "selects from TABLE B" and not "selects from TABLE B for update".)

What I do infer from the ORA-1555 is that T1's read from B is taking a long time, meaning that there's plenty to time for T2 to try to acquire locks on A that T1 holds, so that, when T1 continues and tries to acquire locks that T2 had acquired before it started waiting, a deadlock will occur.

It sounds like you've got some serious application design issues. T1 is locking records, for a long time, that are required by other sessions. Even if you work out your deadlock issue, your application has got scalability problems, it seems.

The best way that I know to avoid deadlock issues is to ensure that all transactions locking resources lock them in the same order.

This is a recipe for deadlocks:

  1. T1 ==> Lock record #1
  2. T2 ==> Lock record #2
  3. T2 ==> Attempt to lock record #1 (waits for T1)
  4. T1 ==> Attempt to lock record #2 ==> deadlock!

If, by design in your application, you forced T1 and T2 to work in the same order, it would have been:

  1. T1 ==> Lock record #1
  2. T2 ==> Lock record #1 (waits for T1)
  3. T1 ==> Lock record #2
  4. T1 ==> commit or rollback
  5. T2 ==> (continues) Lock record #2 ==> no deadlock!

Although, as I pointed out, your design is still not scalable because T2 spends a lot of time waiting. That's a harder problem.

Side note: in the above example, T1 has to commit or rollback -- depending on timing, a rollback to savepoint may not be enough because, annoyingly in Oracle database, T1 rolling back to a savepoint would release the lock on record #1 but it wouldn't tell transaction T2 that it could continue.

Upvotes: 1

Related Questions