1.创建测试表
create table t1 (id int,name varchar(10),age int);
insert into t1 values (1,'a',1);
insert into t1 values (2,'b',2);
insert into t1 values (3,'c',3);
insert into t1 values (4,'d',4);
insert into t1 values (5,'e',5);
commit;
2.在session 1执行命令
SQL> select * from t1 where id = 1;
ID NAME AGE
---------- ------------------------------ ----------
1 a 1
SQL> update t1 set age=age+1 where id=1;
1 row updated.
SQL> select * from t1 where id = 1;
ID NAME AGE
---------- ------------------------------ ----------
1 a 2
3.在session 2执行命令https://www.cndba.cn/hbhe0316/article/4913
SQL> select * from t1 where id=2;
ID NAME AGE
---------- ------------------------------ ----------
2 a 2
SQL> update t1 set age=age+1 where id=2;
1 row updated.
SQL> select * from t1 where id=2;
ID NAME AGE
---------- ------------------------------ ----------
2 a 3
4.在session 1查看https://www.cndba.cn/hbhe0316/article/4913
SQL> select * from t1 where id = 2;
ID NAME AGE
---------- ------------------------------ ----------
2 a 2
5.在session 2查看https://www.cndba.cn/hbhe0316/article/4913https://www.cndba.cn/hbhe0316/article/4913
SQL> select * from t1 where id=1;
ID NAME AGE
---------- ------------------------------ ----------
1 a 1
6.在session 1执行update
SQL> update t1 set age=age-1 where id=2;
出现等待,后来出现错误。
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
7.在session 2执行Updatehttps://www.cndba.cn/hbhe0316/article/4913
SQL> update t1 set age=age-1 where id=1;
8.在session 3执行命令查看
SQL> select * from v$lock;
ADDR KADDR SID TYPE ID1 ID2
---------------- ---------------- ---------- ------ ---------- ----------
LMODE REQUEST CTIME BLOCK CON_ID
---------- ---------- ---------- ---------- ----------
000000008D285158 000000008D285188 305 XR 4 0
1 0 2383 0 0
000000008D284FB8 000000008D284FE8 305 RD 1 0
1 0 2383 0 0
000000008D285088 000000008D2850B8 305 CF 0 0
2 0 2382 0 0
ADDR KADDR SID TYPE ID1 ID2
---------------- ---------------- ---------- ------ ---------- ----------
LMODE REQUEST CTIME BLOCK CON_ID
---------- ---------- ---------- ---------- ----------
000000008D2852F8 000000008D285328 2 RT 1 0
6 0 2378 0 0
000000008D285498 000000008D2854C8 305 RS 25 1
2 0 2378 0 0
000000008D285708 000000008D285738 2730 MR 5 0
4 0 2362 0 0
ADDR KADDR SID TYPE ID1 ID2
---------------- ---------------- ---------- ------ ---------- ----------
LMODE REQUEST CTIME BLOCK CON_ID
---------- ---------- ---------- ---------- ----------
000000008D286EC8 000000008D286EF8 2730 MR 6 0
4 0 2362 0 0
000000008D287548 000000008D287578 2730 MR 14 0
4 0 2376 0 0
000000008D286420 000000008D286450 2730 MR 15 0
4 0 2376 0 0
ADDR KADDR SID TYPE ID1 ID2
---------------- ---------------- ---------- ------ ---------- ----------
LMODE REQUEST CTIME BLOCK CON_ID
---------- ---------- ---------- ---------- ----------
000000008D286830 000000008D286860 2730 MR 16 0
4 0 2376 0 0
000000008D2877D0 000000008D287800 2730 MR 17 0
4 0 2376 0 0
000000008D286690 000000008D2866C0 2730 MR 18 0
4 0 2376 0 0
ADDR KADDR SID TYPE ID1 ID2
---------------- ---------------- ---------- ------ ---------- ----------
LMODE REQUEST CTIME BLOCK CON_ID
---------- ---------- ---------- ---------- ----------
000000008D286760 000000008D286790 2730 MR 19 0
4 0 2376 0 0
000000008D285E70 000000008D285EA0 2730 MR 20 0
4 0 2376 0 0
000000008D2860E0 000000008D286110 2730 MR 21 0
4 0 2376 0 0
ADDR KADDR SID TYPE ID1 ID2
---------------- ---------------- ---------- ------ ---------- ----------
LMODE REQUEST CTIME BLOCK CON_ID
---------- ---------- ---------- ---------- ----------
000000008D287618 000000008D287648 2730 MR 22 0
4 0 2376 0 0
000000008D2878A0 000000008D2878D0 2730 MR 23 0
4 0 2376 0 0
000000008D286900 000000008D286930 2730 MR 24 0
4 0 2376 0 0
ADDR KADDR SID TYPE ID1 ID2
---------------- ---------------- ---------- ------ ---------- ----------
LMODE REQUEST CTIME BLOCK CON_ID
---------- ---------- ---------- ---------- ----------
000000008D286C58 000000008D286C88 2730 MR 25 0
4 0 2376 0 0
000000008D286AB8 000000008D286AE8 2730 MR 1 0
4 0 2376 0 0
000000008D287138 000000008D287168 2730 MR 4 0
4 0 2376 0 0
ADDR KADDR SID TYPE ID1 ID2
---------------- ---------------- ---------- ------ ---------- ----------
LMODE REQUEST CTIME BLOCK CON_ID
---------- ---------- ---------- ---------- ----------
000000008D285DA0 000000008D285DD0 2730 MR 7 0
4 0 2376 0 0
000000008D2865C0 000000008D2865F0 2730 PW 1 0
3 0 2375 0 0
000000008D285F40 000000008D285F70 2730 MR 13 0
4 0 2376 0 0
ADDR KADDR SID TYPE ID1 ID2
---------------- ---------------- ---------- ------ ---------- ----------
LMODE REQUEST CTIME BLOCK CON_ID
---------- ---------- ---------- ---------- ----------
000000008D285978 000000008D2859A8 2730 MR 3 0
4 0 2376 0 0
000000008D286280 000000008D2862B0 2730 MR 204 0
4 0 2376 0 0
000000008D285638 000000008D285668 7 AE 134 1
4 0 2337 0 0
ADDR KADDR SID TYPE ID1 ID2
---------------- ---------------- ---------- ------ ---------- ----------
LMODE REQUEST CTIME BLOCK CON_ID
---------- ---------- ---------- ---------- ----------
000000008D285A48 000000008D285A78 1828 AE 134 1
4 0 2319 0 0
000000008D285CD0 000000008D285D00 307 AE 134 1
4 0 2144 0 0
000000008D286010 000000008D286040 2425 AE 134 1
4 0 2363 0 0
ADDR KADDR SID TYPE ID1 ID2
---------------- ---------------- ---------- ------ ---------- ----------
LMODE REQUEST CTIME BLOCK CON_ID
---------- ---------- ---------- ---------- ----------
000000008D286B88 000000008D286BB8 913 AE 134 1
4 0 2363 0 0
000000008D286DF8 000000008D286E28 2429 AE 134 1
4 0 2338 0 0
000000008D287700 000000008D287730 912 AE 134 1
4 0 2280 0 0
ADDR KADDR SID TYPE ID1 ID2
---------------- ---------------- ---------- ------ ---------- ----------
LMODE REQUEST CTIME BLOCK CON_ID
---------- ---------- ---------- ---------- ----------
000000008D287A40 000000008D287A70 2728 AE 134 1
4 0 2338 0 0
000000008D287970 000000008D2879A0 1215 TS 65542 1
3 0 2369 0 0
000000008D287478 000000008D2874A8 1214 KD 0 0
6 0 2368 0 0
ADDR KADDR SID TYPE ID1 ID2
---------------- ---------------- ---------- ------ ---------- ----------
LMODE REQUEST CTIME BLOCK CON_ID
---------- ---------- ---------- ---------- ----------
000000008D287068 000000008D287098 2730 MR 8 0
4 0 2362 0 0
00007F7E1AE74E28 00007F7E1AE74E50 2425 TM 74013 0
3 0 183 0 1
00007F7E1AE74E28 00007F7E1AE74E50 912 TM 74013 0
3 0 2256 0 1
ADDR KADDR SID TYPE ID1 ID2
---------------- ---------------- ---------- ------ ---------- ----------
LMODE REQUEST CTIME BLOCK CON_ID
---------- ---------- ---------- ---------- ----------
000000008D2858A8 000000008D2858D8 1214 KT 19630 2
4 0 2357 0 0
000000008D286D28 000000008D286D58 1214 KT 19659 1
4 0 2350 0 0
000000008D2853C8 000000008D2853F8 912 TX 524307 1004
0 6 17 0 1
ADDR KADDR SID TYPE ID1 ID2
---------------- ---------------- ---------- ------ ---------- ----------
LMODE REQUEST CTIME BLOCK CON_ID
---------- ---------- ---------- ---------- ----------
00000000859C8108 00000000859C8140 2425 TX 524307 1004
6 0 183 1 1
0000000085CB6378 0000000085CB63B0 912 TX 262155 1044
6 0 2256 0 1
44 rows selected.
9.用下面的SQL语句可以直接得出谁阻塞谁
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
----------
SYS
2425 is blocking
SYS
912
10.查看alert日志
[oracle@db01 trace]$ tail -f alert_orcl.log
2021-10-10T20:21:01.149199+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4200.trc:
2021-10-10T20:21:01.713105+08:00
ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. More info in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4200.trc.
11.查看orcl_ora_4200.trc.
[Transaction Deadlock]
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 serial process session holds waits serial
TX-00080013-000003EC-00000001-00000000 8 2425 X 26570 23 912 X 4453
TX-0004000B-00000414-00000001-00000000 23 912 X 4453 8 2425 X 26570
12.解决方案
SESSION2还在被SESSION1阻塞,只有在SESSION1执行提交或回退才能让SESSION2继续下去。
SESSION1:
commit; —将提交update t1 set age=age-1 where id=2;的修改
或
rollback;—将取消update t1 set age=age-1 where id=2;的修改
随即SESSION2得到1 row updated.的信息。
13.通过查v$session视图可以得出serial#
SQL> select sid,serial# from v$session where sid=2425;
SID SERIAL#
---------- ----------
2425 26570
14.杀死这个会话
SQL> alter system kill session '2425,26570';
System altered.
15.再次查看已经没有了https://www.cndba.cn/hbhe0316/article/4913
SQL> select ( select username from v$session where sid=a.sid) blocker, a.sid,
2 ' is blocking ',
3 (select username from v$session where sid=b.sid) blockee, b.sid
4 from v$lock a, v$lock b
5 where a.block=1 and b.request>0 and a.id1=b.id1 and a.id2=b.id2;
no rows selected
版权声明:本文为博主原创文章,未经博主允许不得转载。
Linux,oracle