Friday, November 25, 2011

Oracle Deadlock

A deadlock occurs when two or more sessions are waiting for rows that are locked each
session. The next example demonstrates the deadlock error.

Time
Session -1
Session - 2









T1






T2



T3



T4


T5

ID NAME                   SAL
---------- ---------------    ----------
1001 TAMIL                 3300
1002 KANNAN              3190
1003 RAJIV                  4000
13:30:02 SQL> prompt time t1
time t1
13:30:21 SQL> update mytable
set sal = sal*1.1 where id = 1001;
1 row updated.
13:30:39 SQL>




13:31:20 SQL> prompt time t3
time t3
13:31:25 SQL> update mytable
set sal = sal*1.1 where id = 1002;




update mytable set sal = sal*1.1 where id =
1002
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting
for resource

















13:30:49 SQL> prompt time t2
time t2
13:30:56 SQL> update mytable
set sal=sal*1.1 where id = 1002;
1 row updated.




13:31:37 SQL> prompt time t4
time t4
13:31:40 SQL> update mytable
set sal=sal*1.1 where id = 1001 ;
.
.
.

The interesting point is after time T3 but before T4, oracle will not give deadlock error even
though the row (ID=1002) has been locked in session 2.
You will see the dead lock situation only when the session -2 updates row(s) on which
session -1 already placed a lock. In this case ID=1001.  You will see the deadlock at time T5,
not in between T3 and T4.
From another session I quried v$lock.
SQL>select * from v$lock
2  where sid in (204, 194)
3* order by 3, 4
SQL> /
ADDR             KADDR                                   SID       TY     ID1        ID2      LMODE    REQUEST  CTIME
---------------- ----------------                         ---------- -- ----------     ---------- -------- ----------
----------
000000043EFA5780 000000043EFA57A8        194 TM     65748          0          3          0         160
000000043EFD24A0 000000043EFD2628        194 TX     655401       7540      6          0         160
000000043B3552F0 000000043B355310        194 TX     327691       4556       0          6         154
000000043EFA5680 000000043EFA56A8        204 TM     65748          0           3         0          382
000000043BF1EF38 000000043BF1F0C0         204 TX     327691       4556      6          0         382
The CTIME columns reports the how long (in seconds) the session is holding locks on the
rows for that lock mode.  This column is updated every 3 seconds. And it’s a good indicator
when  a session gets struck.
Oracle logs the dead lock error in the alert log file.
Fri Mar  2 13:31:49 2007
ORA-00060: Deadlock detected. More info in file /u01/admin/udump/orlab10g_ora_16526.trc.
And the trace file shows more detailed info.
*** 2007-03-02 13:19:17.636
*** ACTION NAME:() 2007-03-02 13:19:17.630
*** MODULE NAME:(SQL*Plus) 2007-03-02 13:19:17.629
*** SERVICE NAME:(SYS$USERS) 2007-03-02 13:19:17.629
*** SESSION ID:(204.49163) 2007-03-02 13:19:17.629
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
update mytable set sal = sal*1.1 where id = 1002
The following deadlock is not an 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-000a0001-00001d74        23     204     X             31     194           X
TX-00090001-00001266        31     194     X             23     204           X
session 204: DID 0001-0017-00000131     session 194: DID 0001-001F-00000792
session 194: DID 0001-001F-00000792     session 204: DID 0001-0017-00000131
Rows waited on:
Session 194: obj - rowid = 000100D4 - AAAQDUAAJAAA3U+AAA
(dictionary objn - 65748, file - 9, block - 226622, slot - 0)
Session 204: obj - rowid = 000100D4 - AAAQDUAAJAAA3U+AAB
(dictionary objn - 65748, file - 9, block - 226622, slot - 1)
A better way of handling dead lock error is to lock the row before update with specified time
interval.
Here is a simple procedure that does the job.
create or replace procedure upd_proc(P_Id IN number) as
L_id int;
already_locked exception;
pragma exception_init(already_locked, -30006);
begin
begin
select id into L_id from mytable where id = p_id
for update wait 3;
update mytable set sal=sal*1.1 where id = p_id;
exception when already_locked then
dbms_output.put_line('already locked '||to_char(P_id));
end;
end;
/
Session 1
SQL> exec upd_proc(1001);
PL/SQL procedure successfully completed.
Session – 2
SQL> exec upd_proc(1002);
PL/SQL procedure successfully completed.
Session -1
SQL> exec upd_proc(1002);
already locked 1002
PL/SQL procedure successfully completed.
Session - 2
SQL> exec upd_proc(1001);
already locked 1001
PL/SQL procedure successfully completed.
In a multiuser system, the developers must write proper application code to track the
deadlock errors and issue appropriate warnings to the end users.
Hope, this helps.
Tamilselvan G


No comments:

Post a Comment