Causes and workarounds for Oracle deadlock generation

If there are two sessions, each session holds the resources that another session wants, and a deadlock occurs.
The following experiments are used to illustrate the causes and solutions of deadlocks.
SESSION1:
Sql> CREATE TABLE t2 as SELECT * from EMP;
Sql> select * from T2 where empno=7369;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7369 SMITH Clerk 7902 19801217 00:00:00 800 20

sql> Update T2 set sal=sal+200 where empno=7369;

Sql> select * from T2 where empno=7369;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7369 SMITH Clerk 7902 19801217 00:00:00 1000 20

SESSION2:

Sql> select * from T2 where empno=7900;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7900 JAMES Clerk 7698 19811203 00:00:00 950 30

sql> Update T2 set sal=sal+200 where empno=7900;

1 row updated.

Sql> select * from T2 where empno=7900;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7900 JAMES Clerk 7698 19811203 00:00:00 1150 30

SESSION1:
Sql> select * from T2 where empno=7900;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7900 JAMES Clerk 7698 19811203 00:00:00 950 30

SESSION2:
Sql> select * from T2 where empno=7369;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7369 SMITH Clerk 7902 19801217 00:00:00 800 20

SESSION1:
sql> Update T2 set sal=sal-200 where empno=7900;
Appear waiting

SESSION2:
sql> Update T2 set sal=sal-200 where empno=7369;
Deadlock occurs, Session2 is blocked
The system rolls back the SQL associated with the deadlock Session1.
Update T2 set sal=sal-200 where empno=7900
*
ERROR at line 1:
Ora-00060:deadlock detected while waiting for resource

SESSION1:
Sql> select * from T2 where empno in (7369,7900);

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7369 SMITH Clerk 7902 19801217 00:00:00 1000 20
7900 JAMES Clerk 7698 19811203 00:00:00 950 30
See that the system only rolls back the changes to the empno=7900.
At this point, the SESSION2 update T2 set sal=sal-200 where empno=7369; is still blocked by the first statement of the SESSION1.


We now look at the situation where the lock was generated
Open a third session log in as DBA
Sql> select * from V$lock;

ADDR kaddr SID TY ID1 ID2 lmode REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
29434224 29434238 165 XR 4 0 1 0 2998 0
29434280 29434294 165 CF 0 0 2 0 2988 0
294342DC 294342f0 167 PW 1 0 3 0 2980 0
29434338 2943434C 165 RS 25 1 2 0 2983 0
294343F0 29434404 166 RT 1 0 6 0 2983 0
2943444C 29434460 153 TX 589868 325 0 4 24 0
29434560 29434574 167 MR 1 0 4 0 2983 0
294345BC 294345d0 167 MR 2 0 4 0 2983 0
29434618 2943462C 167 MR 3 0 4 0 2983 0
29434674 29434688 167 MR 4 0 4 0 2983 0
294346d0 294346E4 167 MR 5 0 4 0 2983 0
2943472C 29434740 167 MR 6 0 4 0 2983 0
29434788 2943479C 167 MR 7 0 4 0 2983 0
294347E4 294347f8 167 MR 201 0 4 0 2983 0
2943489C 294348b0 164 TS 3 1 3 0 2979 0
288f6030 288f6048 144 TM 53257 0 3 0 51 0
288F60DC 288f60f4 153 TM 53257 0 3 0 24 0
28934170 28934194 153 TX 655363 241 6 0 24 0
28969404 28969428 144 TX 589868 325 6 0 51 1

Rows selected.

The following SQL statement can be used to directly draw who blocked who
Sql> Select (select username from v$session where sid=a.sid) blocker, A.sid,
' Is blocking ',
(select username from v$session where sid=b.sid) Blockee, B.sid
From V$lock A, V$lock b
where A.block=1 and B.request>0 and A.id1=b.id1 and A.id2=b.id2;


Blocker sid ' Isblocking ' blockee sid
------------------------------ ---------- ------------- ------------------------------ ----------
Stone 144 is blocking stone 153


When a deadlock occurs, a message is recorded in the alert file.
--To see the $oracle_base/admin/orcl/bdump/alert_orcl.log file under the operating system, the following information will be found
Thu Sep 2 20:41:25 2010
Ora-00060:deadlock detected. More info in FILE/U01/APP/ORACLE/ADMIN/ORCL/UDUMP/ORCL_ORA_4945.TRC.

View trace file Details again
2010-09-02 20:41:25.700
ACTION NAME () 2010-09-02 20:41:25.699
MODULE NAME (sql*plus) 2010-09-02 20:41:25.699
SERVICE NAME (sys$users) 2010-09-02 20:41:25.699
SESSION ID (144.3) 2010-09-02 20:41:25.699
DEADLOCK detected (ORA-00060)
[Transaction Deadlock]
The following deadlock is not a ORACLE error. It is a
Deadlock due to user error in the design of an application
or from issuing incorrect Ad-hoc SQL. The following
Information may aid in determining the deadlock:
Deadlock Graph:
---------blocker (s)-----------------Waiter (s)---------
Resource Name Process session holds waits process session holds waits
TX-00090015-00000146 144 x 148 x
.......

The SESSION2 is still being blocked by SESSION1, and the SESSION2 can continue only if the SESSION1 executes the commit or fallback.
SESSION1:
Commit --Changes to the update T2 set sal=sal+200 where empno=7369 will be submitted
Or
rollback;--will cancel changes to update T2 set sal=sal+200 where empno=7369

Immediately SESSION2 get the information of 1 row updated.

We can also execute the KILL command with the administrator account to kill the blocked session SESSION1 to resolve the deadlock.
Sqlplus/as SYSDBA
Information from the above can be used to know the blocking sid=144.
By checking the V$session view you can draw serial#

Saddr SID serial# audsid paddr user# USERNAME COMMAND ownerid taddr
-------- ---------- ---------- ---------- -------- ---------- ----------- ---------- ---------- -----
29F0BED4 144 3 210009 29E24174 STONE 0 2147483644

Kill this session with the following command
Alter system kill session ' 144,3 ';

Summarize:
Oralce that deadlocks are errors caused by the application, and that there are few deadlocks in Oracle. The number one killer that causes deadlocks is that foreign keys are not indexed, and the second killer is that the bitmap index is being updated concurrently.
If the foreign key does not have an index, we will lock the entire child table when we update the parent table's primary key. If a foreign key is not indexed, a row in the parent table is deleted, and the entire child table is locked. This can cause a lot of problems, and a deadlock occurs if any of the blocked sessions lock up the resources needed for a session. As for concurrent updates of bitmap indexes, all associated rows are locked, which can also cause problems.
In Oracle9i and above, these full-table locks are short-lived, that is, only during DML operations, not during the entire transaction.

This article transferred from: http://www.itpub.net/thread-1347577-1-1.html

Causes and workarounds for Oracle deadlock generation