CODEWITHSUNDEEP
databaseoracle-databasedeadlock
Jaroslav Beňo
Jaroslav Beňo

Reputation: 67

Oracle deadlocks for two uncommon queries

I have dead lock: sql A is waiting on sql B.

----- Information for the OTHER waiting sessions -----

A is: delete from attachment where ID=:1

----- Current SQL Statement for this session (sql_id=audwbsf163w1n) -----

B is: delete from detail where ID=:1

deadlock trace log

My big question is. Why is possible that these two sql scripts are waiting on each other but they have nothing in common in database? It do not make any sense, that they are waiting on each other.

Upvotes: 0

Views: 169

Answers (1)

Roger Cornejo
Roger Cornejo

Reputation: 1547

I'd advise care with the terminology used: "Deadlock" has a very specific meaning (below) and what you are implying in your problem description is a "blocking lock".

A deadlock is the classic “deadly embrace” problem. The “deadly embrace” happens when, say Task B, attempts to lock a row which is being held by another task, say Task A, and Task A is waiting for Task B to release a lock. Oracle will abort one of the transaction to prevent a perpetual wait condition.

Root Cause (general): • Transactions deadlocked one another while waiting for resources. [from ora docs]

General Solution: • Generally, this is a design flaw in the application. • The deadlock is an application issue and must be fixed within the application; there is no DB fix for a deadlock.

A deadlock will result in Oracle terminating one of the sessions and that session will receive an error indicating the reason. This happens with 2 to 3 seconds of it being detected.

A blocking lock on the other hand will remain indefinitely until the blocking session either commits, is rolled-back or is killed (which does a rollback).

Also, check for cascading operations generated as recursive SQL by the DBMS.

Upvotes: 1

Related Questions