784786 wrote:the deadlock is recorded within alert_SID.log file & associated trace file.
After the Deadlock was released, how can we confirm that particular table was locked?
Gogala Mladen wrote:Mladen,
You can find out which table was locked by reading the trace files produced by Oracle instance. The session which discovers the deadlock will be killed and the trace file will be produced. The trace file usually contains the full lock graph (deadlocks are circular locking waits) and you will be able to see the table involved. Deadlock usually signifies a problem in the application design and is not an Oracle problem.
In Session 2:
CREATE TABLE T1 ( C1 NUMBER PRIMARY KEY, C2 VARCHAR2(10)); INSERT INTO T1 VALUES (1,'1');
(Session 2 hangs)
INSERT INTO T1 VALUES (2,'2'); INSERT INTO T1 VALUES (1,'3');
(Session 1 hangs)
INSERT INTO T1 VALUES (2,'2');
(Session 1 still hangs)
INSERT INTO T1 VALUES (1,'3') * ERROR at line 1: ORA-00060: deadlock detected while waiting for resource
When the deadlock happened, a statement level rollback was performed in Session 2. Session 1 remained hung, and Session 2 was able to issue a query to see that the first row inserted by the session still exists. It very well could be the case that the application will terminate the session when the ORA-00060 is returned.
SELECT * FROM T1; C1 C2 ---------- ---------- 2 2