0

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.

user2813978
  • 131
  • 3
  • 11

1 Answers1

1

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.

Matthew McPeak
  • 17,705
  • 2
  • 27
  • 59
  • Thanks Matthew, few queries from your response. Is the deadlock here because of the long wait of T2 on TABLE A for the lock? Also In Paragraph 3 you mentioned that "when T1 continues and tries to acquire locks that T2 had acquired before it started waiting, a deadlock will occur". How can T1 try to acquire a lock that T2 holds (i.e the lock on TABLE B) when it is trying to only read from TABLE B? Please clarify. – user2813978 Dec 07 '15 at 23:41
  • 1) No, the deadlock is not due to the lock wait. But, deadlocks often (not always) involve some amount of bad/unlucky timing in order to happen. The long wait makes it much easier for them to occur so that bad luck is not required as much (or at all). 2) I'm not sure -- your description of the situation isn't completely clear. I'm guessing that T2 is acquiring multiple locks on records in table A in the "deletes relevant records from table A" step. Again -- I suspect all the locks involve table A. The `ORA-00060` should generate a trace file that indicates the tables involved. – Matthew McPeak Dec 08 '15 at 00:28