A deadlock occurs when two or more sessions are waiting for rows that are locked each
session. The next example demonstrates the deadlock error.
| | |
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