先了解V$lock视图的字段含义
Name Null? Type
----------------- -------- ------------
ADDR RAW(8) 锁在内存中的地址
KADDR RAW(8) 锁在内存中的地址
SID NUMBER 持有锁的会话
TYPE VARCHAR2(2) 锁的类型：如TM，TX
ID1 NUMBER 锁的标识符1（不同类型的锁，ID1和ID2的含义也不相同）
ID2 NUMBER 锁的标识符2
LMODE NUMBER 会话持有锁的级别
REQUEST NUMBER 请求持有锁的级别
CTIME NUMBER 请求锁的时间，单位秒
BLOCK NUMBER 是否堵塞了其它会话。0或1,0表示没有阻塞
Session 1
sqlplus / as sysdba
startup
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> select * from v$lock;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- -
00000000BED5BA20 00000000BED5BA78 2 XR 4 0 1 0 52
00000000BED5BB00 00000000BED5BB58 2 RD 1 0 1 0 52
00000000BED5BBE0 00000000BED5BC38 2 CF 0 0 2 0 52
00000000BED5BE80 00000000BED5BED8 2 RS 25 1 2 0 48
00000000BED5CC98 00000000BED5CCF0 43 KD 0 0 6 0 47
00000000BED5C660 00000000BED5C6B8 43 KT 12876 0 4 0 47
00000000BED5D0F8 00000000BED5D150 44 TS 3 1 3 0 47
00000000BED5D730 00000000BED5D788 45 AE 100 0 4 0 47
00000000BED5D650 00000000BED5D6A8 45 TO 79833 1 3 0 47
00000000BED5C4A0 00000000BED5C4F8 129 AE 100 0 4 0 47
00000000BED5C740 00000000BED5C798 170 MR 7 0 4 0 48
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- -
00000000BED5C838 00000000BED5C890 170 MR 8 0 4 0 48
00000000BED5BDA0 00000000BED5BDF8 170 MR 9 0 4 0 48
00000000BED5CD78 00000000BED5CDD0 170 MR 10 0 4 0 48
00000000BED5D018 00000000BED5D070 170 MR 11 0 4 0 48
00000000BED5C120 00000000BED5C178 170 MR 1 0 4 0 48
00000000BED5C200 00000000BED5C258 170 MR 2 0 4 0 48
00000000BED5C918 00000000BED5C970 170 PW 1 0 3 0 46
00000000BED5CBB8 00000000BED5CC10 170 MR 4 0 4 0 48
00000000BED5C580 00000000BED5C5D8 170 MR 5 0 4 0 48
00000000BED5C9F8 00000000BED5CA50 170 MR 3 0 4 0 48
00000000BED5CAD8 00000000BED5CB30 170 MR 6 0 4 0 48
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- -
00000000BED5C2E0 00000000BED5C338 170 MR 201 0 4 0 48
00000000BED5BCC0 00000000BED5BD18 213 RT 1 0 6 0 48
24 rows selected.
查看当前数据库所有持有的锁
Session 2
SQL> select sid from v$mystat where rownum=1;
SID
----------
90
Session 1
SQL> select * from v$lock where sid=90;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000BED5DFF0 00000000BED5E048 90 AE 100 0 4 0 40 0
查看90号会话持有的锁，可见开启90号会话时Oracle自动加了AE锁用来保护会话。
Session 2
SQL> update cheng_t1 set name='BBBBBB' where id=1;
1 row updated.
Session 1
SQL> select * from v$lock where sid=90;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000BED5DFF0 00000000BED5E048 90 AE 100 0 4 0 110 0
00007F87C64CA7A8 00007F87C64CA808 90 TM 88681 0 3 0 20 0
00000000BBF13A68 00000000BBF13AE0 90 TX 393225 1691 6 0 20 0
提交一行数据后（开始了事务），90号会话增加持有了TM和TX锁。
上面提到，不同类型的锁，ID1和ID2的含义也不相同，下面测试下TM锁的ID1含义；
SQL> select object_id,object_name from dba_objects where object_id=88681;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
88681 CHENG_T1
可知TM锁ID1的意义为object_name，也就是表名；
TX锁的ID1和ID2意义：
SQL> select * from v$lock where sid=90 and type='TX';
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000BBF13A68 00000000BBF13AE0 90 TX 393225 1691 6 0 73 0
v$transaction显示事务的唯一标识符
SQL> select ADDR,XIDUSN,XIDSLOT,XIDSQN from v$transaction;
ADDR XIDUSN XIDSLOT XIDSQN
---------------- ---------- ---------- ----------
00000000BBF13A68 6 9 1691
可知：ADDR地址相同，ID1=655369（十进制）=60009（十六进制），6和9，分别对应XIDUSN，XIDSLOT；ID2对应XIDSQN；
再开个会话模拟锁堵塞
Session 3
sqlplus cheng/oracle
SQL> select sid from v$mystat where rownum=1;
SID
----------
88
SQL> update cheng_t1 set name='BBBBBB' where id=1;
没有返回结果，发生堵塞
Session 1
SQL> select * from v$lock where sid in(90,88) order by sid;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00007F87C64D08F8 00007F87C64D0958 88 TM 88681 0 3 0 32 0
00000000BED5D9D0 00000000BED5DA28 88 AE 100 0 4 0 89 0
00000000BED5C040 00000000BED5C098 88 TX 393225 1691 0 6 32 0
00007F87C64D08F8 00007F87C64D0958 90 TM 88681 0 3 0 292 0
00000000BBF13A68 00000000BBF13AE0 90 TX 393225 1691 6 0 292 1
00000000BED5DFF0 00000000BED5E048 90 AE 100 0 4 0 382 0
1、90号会话TX锁BLOCK字段显示为1，堵塞了其它会话（即88号会话）；
2、88号会话TX锁REQUEST显示为6，说明正在请求6号TX锁；
场景1：找出被堵塞的会话ID
SQL> select * from v$lock where sid=90;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000BED5DFF0 00000000BED5E048 90 AE 100 0 4 0 700 0
00007F87C642C638 00007F87C642C698 90 TM 88681 0 3 0 610 0
00000000BBF13A68 00000000BBF13AE0 90 TX 393225 1691 6 0 610 1
已知90号会话将一未知会话堵塞，那么需要找出被堵塞的会话
SQL> select * from v$lock where id1=393225 and id2=1691;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000BED5C040 00000000BED5C098 88 TX 393225 1691 0 6 403 0
00000000BBF13A68 00000000BBF13AE0 90 TX 393225 1691 6 0 663 1
可知被堵塞（或者是请求锁）的会话ID为88。
通常情况下，TX锁的持有锁会话和请求锁会话的ID1和ID2数值是相同的。
场景2：定位行锁竞争数据块及信息
查看会话当前的等待时间信息：
SQL> select sid,event,p1,p2,p3 from v$session where wait_class<>'Idle';
SID EVENT P1 P2 P3
---------- ---------------------------------------------------------------- ---------- ---------- ----------
45 SQL*Net message to client 1650815232 1 0
88 enq: TX - row lock contention 1415053318 393225 1691
SQL> select sid,event,p1,p2,p3,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where wait_class<>'Idle';
SID EVENT P1 P2 P3 ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
---------- ---------------------------------------------------------------- ---------- ---------- ---------- ------------- -------------- --------------- -------------
45 SQL*Net message to client 1650815232 1 0 543 1 75736 0
88 enq: TX - row lock contention 1415053318 393225 1691 88681 4 527 0
意义解释：
EVENT:会话等待的资源或事件
P1 P2 P3：附加参数
ROW_WAIT_OBJ#：指定行的表的对象ID — 即对象号，也是表名
ROW_WAIT_FILE# 指定行的数据文件标示符 — 即数据文件编号
ROW_WAIT_BLOCK#：指定行的块标识符 — 即块号
ROW_WAIT_ROW#：行号
说明：TX - row lock contention的意义为行锁竞争，是队列锁的等待事件。
SQL> select object_id,data_object_id from dba_objects where object_name='CHENG_T1';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
88681 88681
object_id指对象的数据字典号。数据库里对象的唯一，每一个对象都会被分配一个唯一的object_id作为区别的标志；
DATA_OBJECT_ID指包含对象的segment的字典对象编号。当Segment发生变动，这个数值也会改变。
这两个值都存放在Oracle数据字典里。一开始是相同的，当Segment发生了变动（如move表，或truncate表），DATA_OBJECT_ID就会改变（数字就会大一个）。
找到Rowid
SQL> select DBMS_ROWID.ROWID_CREATE(1,88681,4,527,0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAVppAAEAAAAIPAAA
DBMS_ROWID包允许我们使用PL/SQL程序或SQL语句创建rowids和获取rowid的信息。例如可以通过其找到数据对象编号，数据文件编号，包含数据行的数据块编号及数据块中的数据行。
具体用法查看10g官方文档 BOOK — PL/ — PL/SQL Packages and Types Reference — DBMS_ROWID
SQL> select * from cheng.cheng_t1 where rowid='AAAVppAAEAAAAIPAAA';
ID NAME
---------- ------------------------------
1 FFFFFF
通过Rowid查到堵塞88号会话的数据行：
SQL> select sql_text,event from v$session a,v$sql b where a.sql_id=b.sql_id and sid=88;
SQL_TEXT EVENT
--------------------------------------------------------- -------------------------------------------
update cheng_t1 set name='BBBBBB' where id=1 enq: TX - row lock contention
SQL_TEXT即 堵塞88号会话的sql语句
另也可以dump数据块来获取更多信息
SQL> alter system dump datafile 4 block 527;
场景3：查看锁的会话信息：
SQL> select * from v$lock where sid in(90,88) order by sid;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00007F87C642DA20 00007F87C642DA80 88 TM 88681 0 3 0 5051 0
00000000BED5D9D0 00000000BED5DA28 88 AE 100 0 4 0 5108 0
00000000BED5C040 00000000BED5C098 88 TX 393225 1691 0 6 5051 0
00007F87C642DA20 00007F87C642DA80 90 TM 88681 0 3 0 5311 0
00000000BBF13A68 00000000BBF13AE0 90 TX 393225 1691 6 0 5311 1
00000000BED5DFF0 00000000BED5E048 90 AE 100 0 4 0 5401 0
SQL> select sid,LOCKWAIT,status from v$session where sid in(90,88);
SID LOCKWAIT STATUS
---------- ---------------- --------
88 00000000BED5C098 ACTIVE
90 INACTIVE
这里指会话88请求持有一块锁，希望从内存分到地址，表现为LOCKWAIT(锁等待），内存地址即00000000BED5C098 ，与v$LOCK中88号会话TX锁的KADDR地址相对应；
STATUS状态：ACTIVE表示会话正在被堵塞或sql语句未执行结束；INACTIVE表示sql语句已执行完成，但不一定有commit；
查看详细的会话信息：
SQL> select SID,PADDR,PROGRAM,PROCESS,USERNAME,COMMAND,SQL_ID from v$session where sid in(90,88);
SID PADDR PROGRAM PROCESS USERNAME COMMAND SQL_ID
---------- ---------------- ------------------------------------------------ ------------------------ ------------------------------ ---------- -------------
88 00000000BE52B9A0 sqlplus@localhost.localdomain (TNS V1-V3) 3901 CHENG 6 b9qz0gya6zu4f
90 00000000BE531DF0 sqlplus@localhost.localdomain (TNS V1-V3) 3702 CHENG 0
SQL> select sql_text from v$sql where sql_id='b9qz0gya6zu4f';
SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
update cheng_t1 set name='BBBBBB' where id=1
找到引起堵塞的SQL语句
需要中止会话：
SQL> select sid,SERIAL# from v$session where sid in(90,88);
SID SERIAL#
---------- ----------
88 45
90 13
通常有两种情况
本地：事务提交commit，事务回退rollback
远程：alter system kill session‘SID,SERIAL#’; 或者操作系统层kill-9 XXXX
SQL> alter system kill session '90,13';
System altered.
SQL> select * from v$lock where sid in(90,88) order by sid;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000BED5D9D0 00000000BED5DA28 88 AE 100 0 4 0 6128 0
00000000BBF14478 00000000BBF144F0 88 TX 262161 1093 6 0 63 0
00007F87C64AF310 00007F87C64AF370 88 TM 88681 0 3 0 6071 0
可知90号会话已被终止；88号会话成功持有TX6号锁。
SQL> select sid,LOCKWAIT,status from v$session where sid in(90,88);
SID LOCKWAIT STATUS
---------- ---------------- --------
88 INACTIVE
90 KILLED
90号会话STATUS显示KILLED，会话已被终止
88号会话STATUS显示INACTIVE，SQL语句执行完成；LOCKWAIT为空，已没有锁等待。
Session 2
SQL> select sid from v$mystat where rownum=1;
select sid from v$mystat where rownum=1
*
ERROR at line 1:
ORA-00028: your session has been killed
Session 3
SQL> update cheng_t1 set name='BBBBBB' where id=1;
1 row updated.
SQL> commit;
Commit complete.
关于死锁的实验
Session 1 不变，重新开启两会话
Session 2
SQL> select sid from v$mystat where rownum=1;
SID
----------
175
SQL> update cheng_t1 set name='AAAAAA' where id=1;
1 row updated.
Session 3
SQL> select sid from v$mystat where rownum=1;
SID
----------
217
SQL> update cheng_t1 set name='bbbbbb' where id=2;
1 row updated.
Session 1
SQL> select * from v$lock where sid in (175,217) order by sid;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00007F87C642DA20 00007F87C642DA80 175 TM 88681 0 3 0 66 0
00000000BBF530E8 00000000BBF53160 175 TX 524316 1106 6 0 66 0
00000000BED5D8F0 00000000BED5D948 175 AE 100 0 4 0 83 0
00000000BBF53AF8 00000000BBF53B70 217 TX 393232 1691 6 0 53 0
00007F87C642DA20 00007F87C642DA80 217 TM 88681 0 3 0 53 0
00000000BED5C660 00000000BED5C6B8 217 AE 100 0 4 0 63 0
此时两会话各持有TM、TX锁，并互相不堵塞
Session 2
SQL> update cheng_t1 set name='bbbbbb' where id=2;
会话175被堵塞
Session 1
SQL> select * from v$lock where sid in (175,217) order by sid;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00007F87C64AF310 00007F87C64AF370 175 TM 88681 0 3 0 124 0
00000000BBF530E8 00000000BBF53160 175 TX 524316 1106 6 0 124 0
00000000BED5D8F0 00000000BED5D948 175 AE 100 0 4 0 141 0
00000000BED5D398 00000000BED5D3F0 175 TX 393232 1691 0 6 27 0
00007F87C64AF310 00007F87C64AF370 217 TM 88681 0 3 0 111 0
00000000BBF53AF8 00000000BBF53B70 217 TX 393232 1691 6 0 111 1
00000000BED5C660 00000000BED5C6B8 217 AE 100 0 4 0 121 0
175号会话REQUEST=6，正在申请持有6号TX锁；217号会话TX锁BLOCK=1，说明堵塞了216号会话；
Session 3
SQL> update cheng_t1 set name='AAAAAA' where id=1;
会话217被堵塞（构成和会话175相互堵塞条件）
Session 2
SQL> update cheng_t1 set name='bbbbbb' where id=2;
update cheng_t1 set name='bbbbbb' where id=2
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
175号会话报出ORA-00060错误，自动删除死锁deadlock
Session 1
SQL> select * from v$lock where sid in (175,217) order by sid;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00007F87C64AF310 00007F87C64AF370 175 TM 88681 0 3 0 286 0
00000000BBF530E8 00000000BBF53160 175 TX 524316 1106 6 0 286 1
00000000BED5D8F0 00000000BED5D948 175 AE 100 0 4 0 303 0
00000000BBF53AF8 00000000BBF53B70 217 TX 393232 1691 6 0 273 0
00007F87C64AF310 00007F87C64AF370 217 TM 88681 0 3 0 273 0
00000000BED5CF38 00000000BED5CF90 217 TX 524316 1106 0 6 86 0
00000000BED5C660 00000000BED5C6B8 217 AE 100 0 4 0 283 0
175号会话TX锁BLOCK=1，堵塞了217号会话；217号会话TX锁REQUEST=6，在申请持有6号TX锁；
Linux窗口
看看死锁的相关日志
[oracle@localhost ~]$ oerr ora 00060
00060, 00000, "deadlock detected while waiting for resource"
// *Cause: Transactions deadlocked one another while waiting for resources.
// *Action: Look at the trace file to see the transactions and resources
// involved. Retry if necessary.
[oracle@localhost ~]$ cd $ORACLE_BASE
[oracle@localhost app]$ tail -f diag/rdbms/orcl/orcl/trace/alert_orcl.log
Tue Jun 06 23:01:06 2017
ORA-00060: Deadlock detected. More info in file /oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_6747.trc.
Oracle在死锁发生后生成一份日志，有时Oracle接着会自动启动Smon进程
[oracle@localhost app]$ more /oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_6747.trc
......
*** 2017-06-06 23:01:06.270
DEADLOCK DETECTED ( ORA-00060 )
[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 process session holds waits
TX-0008001c-00000452 22 175 X 23 217 X
TX-00060010-0000069b 23 217 X 22 175 X
session 175: DID 0001-0016-00000164 session 217: DID 0001-0017-000000B1
session 217: DID 0001-0017-000000B1 session 175: DID 0001-0016-00000164
Rows waited on:
Session 175: obj - rowid = 00015A69 - AAAVppAAEAAAAIPAAB
(dictionary objn - 88681, file - 4, block - 527, slot - 1)
Session 217: obj - rowid = 00015A69 - AAAVppAAEAAAAIPAAA
(dictionary objn - 88681, file - 4, block - 527, slot - 0)
----- Information for the OTHER waiting sessions -----
Session 217:
sid: 217 ser: 37 audsid: 280258 user: 90/CHENG
flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 23 O/S info: user: oracle, term: UNKNOWN, ospid: 6749
image: oracle@localhost.localdomain (TNS V1-V3)
client details:
O/S info: user: oracle, term: pts/1, ospid: 6748
machine: localhost.localdomain program: sqlplus@localhost.localdomain (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
current SQL:
update cheng_t1 set name='AAAAAA' where id=1
----- End of information for the OTHER waiting sessions -----
Information for THIS session:
----- Current SQL Statement for this session (sql_id=9rzgvs6xydnpk) -----
update cheng_t1 set name='bbbbbb' where id=2
===================================================
......
日志中包含产生死锁的会话信息，用户信息及产生死锁的语句等信息，帮助找到产生死锁原因。
Name Null? Type
----------------- -------- ------------
ADDR RAW(8) 锁在内存中的地址
KADDR RAW(8) 锁在内存中的地址
SID NUMBER 持有锁的会话
TYPE VARCHAR2(2) 锁的类型：如TM，TX
ID1 NUMBER 锁的标识符1（不同类型的锁，ID1和ID2的含义也不相同）
ID2 NUMBER 锁的标识符2
LMODE NUMBER 会话持有锁的级别
REQUEST NUMBER 请求持有锁的级别
CTIME NUMBER 请求锁的时间，单位秒
BLOCK NUMBER 是否堵塞了其它会话。0或1,0表示没有阻塞
Session 1
sqlplus / as sysdba
startup
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> select * from v$lock;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- -
00000000BED5BA20 00000000BED5BA78 2 XR 4 0 1 0 52
00000000BED5BB00 00000000BED5BB58 2 RD 1 0 1 0 52
00000000BED5BBE0 00000000BED5BC38 2 CF 0 0 2 0 52
00000000BED5BE80 00000000BED5BED8 2 RS 25 1 2 0 48
00000000BED5CC98 00000000BED5CCF0 43 KD 0 0 6 0 47
00000000BED5C660 00000000BED5C6B8 43 KT 12876 0 4 0 47
00000000BED5D0F8 00000000BED5D150 44 TS 3 1 3 0 47
00000000BED5D730 00000000BED5D788 45 AE 100 0 4 0 47
00000000BED5D650 00000000BED5D6A8 45 TO 79833 1 3 0 47
00000000BED5C4A0 00000000BED5C4F8 129 AE 100 0 4 0 47
00000000BED5C740 00000000BED5C798 170 MR 7 0 4 0 48
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- -
00000000BED5C838 00000000BED5C890 170 MR 8 0 4 0 48
00000000BED5BDA0 00000000BED5BDF8 170 MR 9 0 4 0 48
00000000BED5CD78 00000000BED5CDD0 170 MR 10 0 4 0 48
00000000BED5D018 00000000BED5D070 170 MR 11 0 4 0 48
00000000BED5C120 00000000BED5C178 170 MR 1 0 4 0 48
00000000BED5C200 00000000BED5C258 170 MR 2 0 4 0 48
00000000BED5C918 00000000BED5C970 170 PW 1 0 3 0 46
00000000BED5CBB8 00000000BED5CC10 170 MR 4 0 4 0 48
00000000BED5C580 00000000BED5C5D8 170 MR 5 0 4 0 48
00000000BED5C9F8 00000000BED5CA50 170 MR 3 0 4 0 48
00000000BED5CAD8 00000000BED5CB30 170 MR 6 0 4 0 48
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- -
00000000BED5C2E0 00000000BED5C338 170 MR 201 0 4 0 48
00000000BED5BCC0 00000000BED5BD18 213 RT 1 0 6 0 48
24 rows selected.
查看当前数据库所有持有的锁
Session 2
SQL> select sid from v$mystat where rownum=1;
SID
----------
90
Session 1
SQL> select * from v$lock where sid=90;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000BED5DFF0 00000000BED5E048 90 AE 100 0 4 0 40 0
查看90号会话持有的锁，可见开启90号会话时Oracle自动加了AE锁用来保护会话。
Session 2
SQL> update cheng_t1 set name='BBBBBB' where id=1;
1 row updated.
Session 1
SQL> select * from v$lock where sid=90;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000BED5DFF0 00000000BED5E048 90 AE 100 0 4 0 110 0
00007F87C64CA7A8 00007F87C64CA808 90 TM 88681 0 3 0 20 0
00000000BBF13A68 00000000BBF13AE0 90 TX 393225 1691 6 0 20 0
提交一行数据后（开始了事务），90号会话增加持有了TM和TX锁。
上面提到，不同类型的锁，ID1和ID2的含义也不相同，下面测试下TM锁的ID1含义；
SQL> select object_id,object_name from dba_objects where object_id=88681;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
88681 CHENG_T1
可知TM锁ID1的意义为object_name，也就是表名；
TX锁的ID1和ID2意义：
SQL> select * from v$lock where sid=90 and type='TX';
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000BBF13A68 00000000BBF13AE0 90 TX 393225 1691 6 0 73 0
v$transaction显示事务的唯一标识符
SQL> select ADDR,XIDUSN,XIDSLOT,XIDSQN from v$transaction;
ADDR XIDUSN XIDSLOT XIDSQN
---------------- ---------- ---------- ----------
00000000BBF13A68 6 9 1691
可知：ADDR地址相同，ID1=655369（十进制）=60009（十六进制），6和9，分别对应XIDUSN，XIDSLOT；ID2对应XIDSQN；
再开个会话模拟锁堵塞
Session 3
sqlplus cheng/oracle
SQL> select sid from v$mystat where rownum=1;
SID
----------
88
SQL> update cheng_t1 set name='BBBBBB' where id=1;
没有返回结果，发生堵塞
Session 1
SQL> select * from v$lock where sid in(90,88) order by sid;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00007F87C64D08F8 00007F87C64D0958 88 TM 88681 0 3 0 32 0
00000000BED5D9D0 00000000BED5DA28 88 AE 100 0 4 0 89 0
00000000BED5C040 00000000BED5C098 88 TX 393225 1691 0 6 32 0
00007F87C64D08F8 00007F87C64D0958 90 TM 88681 0 3 0 292 0
00000000BBF13A68 00000000BBF13AE0 90 TX 393225 1691 6 0 292 1
00000000BED5DFF0 00000000BED5E048 90 AE 100 0 4 0 382 0
1、90号会话TX锁BLOCK字段显示为1，堵塞了其它会话（即88号会话）；
2、88号会话TX锁REQUEST显示为6，说明正在请求6号TX锁；
场景1：找出被堵塞的会话ID
SQL> select * from v$lock where sid=90;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000BED5DFF0 00000000BED5E048 90 AE 100 0 4 0 700 0
00007F87C642C638 00007F87C642C698 90 TM 88681 0 3 0 610 0
00000000BBF13A68 00000000BBF13AE0 90 TX 393225 1691 6 0 610 1
已知90号会话将一未知会话堵塞，那么需要找出被堵塞的会话
SQL> select * from v$lock where id1=393225 and id2=1691;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000BED5C040 00000000BED5C098 88 TX 393225 1691 0 6 403 0
00000000BBF13A68 00000000BBF13AE0 90 TX 393225 1691 6 0 663 1
可知被堵塞（或者是请求锁）的会话ID为88。
通常情况下，TX锁的持有锁会话和请求锁会话的ID1和ID2数值是相同的。
场景2：定位行锁竞争数据块及信息
查看会话当前的等待时间信息：
SQL> select sid,event,p1,p2,p3 from v$session where wait_class<>'Idle';
SID EVENT P1 P2 P3
---------- ---------------------------------------------------------------- ---------- ---------- ----------
45 SQL*Net message to client 1650815232 1 0
88 enq: TX - row lock contention 1415053318 393225 1691
SQL> select sid,event,p1,p2,p3,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where wait_class<>'Idle';
SID EVENT P1 P2 P3 ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
---------- ---------------------------------------------------------------- ---------- ---------- ---------- ------------- -------------- --------------- -------------
45 SQL*Net message to client 1650815232 1 0 543 1 75736 0
88 enq: TX - row lock contention 1415053318 393225 1691 88681 4 527 0
意义解释：
EVENT:会话等待的资源或事件
P1 P2 P3：附加参数
ROW_WAIT_OBJ#：指定行的表的对象ID — 即对象号，也是表名
ROW_WAIT_FILE# 指定行的数据文件标示符 — 即数据文件编号
ROW_WAIT_BLOCK#：指定行的块标识符 — 即块号
ROW_WAIT_ROW#：行号
说明：TX - row lock contention的意义为行锁竞争，是队列锁的等待事件。
SQL> select object_id,data_object_id from dba_objects where object_name='CHENG_T1';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
88681 88681
object_id指对象的数据字典号。数据库里对象的唯一，每一个对象都会被分配一个唯一的object_id作为区别的标志；
DATA_OBJECT_ID指包含对象的segment的字典对象编号。当Segment发生变动，这个数值也会改变。
这两个值都存放在Oracle数据字典里。一开始是相同的，当Segment发生了变动（如move表，或truncate表），DATA_OBJECT_ID就会改变（数字就会大一个）。
找到Rowid
SQL> select DBMS_ROWID.ROWID_CREATE(1,88681,4,527,0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAVppAAEAAAAIPAAA
DBMS_ROWID包允许我们使用PL/SQL程序或SQL语句创建rowids和获取rowid的信息。例如可以通过其找到数据对象编号，数据文件编号，包含数据行的数据块编号及数据块中的数据行。
具体用法查看10g官方文档 BOOK — PL/ — PL/SQL Packages and Types Reference — DBMS_ROWID
SQL> select * from cheng.cheng_t1 where rowid='AAAVppAAEAAAAIPAAA';
ID NAME
---------- ------------------------------
1 FFFFFF
通过Rowid查到堵塞88号会话的数据行：
SQL> select sql_text,event from v$session a,v$sql b where a.sql_id=b.sql_id and sid=88;
SQL_TEXT EVENT
--------------------------------------------------------- -------------------------------------------
update cheng_t1 set name='BBBBBB' where id=1 enq: TX - row lock contention
SQL_TEXT即 堵塞88号会话的sql语句
另也可以dump数据块来获取更多信息
SQL> alter system dump datafile 4 block 527;
场景3：查看锁的会话信息：
SQL> select * from v$lock where sid in(90,88) order by sid;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00007F87C642DA20 00007F87C642DA80 88 TM 88681 0 3 0 5051 0
00000000BED5D9D0 00000000BED5DA28 88 AE 100 0 4 0 5108 0
00000000BED5C040 00000000BED5C098 88 TX 393225 1691 0 6 5051 0
00007F87C642DA20 00007F87C642DA80 90 TM 88681 0 3 0 5311 0
00000000BBF13A68 00000000BBF13AE0 90 TX 393225 1691 6 0 5311 1
00000000BED5DFF0 00000000BED5E048 90 AE 100 0 4 0 5401 0
SQL> select sid,LOCKWAIT,status from v$session where sid in(90,88);
SID LOCKWAIT STATUS
---------- ---------------- --------
88 00000000BED5C098 ACTIVE
90 INACTIVE
这里指会话88请求持有一块锁，希望从内存分到地址，表现为LOCKWAIT(锁等待），内存地址即00000000BED5C098 ，与v$LOCK中88号会话TX锁的KADDR地址相对应；
STATUS状态：ACTIVE表示会话正在被堵塞或sql语句未执行结束；INACTIVE表示sql语句已执行完成，但不一定有commit；
查看详细的会话信息：
SQL> select SID,PADDR,PROGRAM,PROCESS,USERNAME,COMMAND,SQL_ID from v$session where sid in(90,88);
SID PADDR PROGRAM PROCESS USERNAME COMMAND SQL_ID
---------- ---------------- ------------------------------------------------ ------------------------ ------------------------------ ---------- -------------
88 00000000BE52B9A0 sqlplus@localhost.localdomain (TNS V1-V3) 3901 CHENG 6 b9qz0gya6zu4f
90 00000000BE531DF0 sqlplus@localhost.localdomain (TNS V1-V3) 3702 CHENG 0
SQL> select sql_text from v$sql where sql_id='b9qz0gya6zu4f';
SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
update cheng_t1 set name='BBBBBB' where id=1
找到引起堵塞的SQL语句
需要中止会话：
SQL> select sid,SERIAL# from v$session where sid in(90,88);
SID SERIAL#
---------- ----------
88 45
90 13
通常有两种情况
本地：事务提交commit，事务回退rollback
远程：alter system kill session‘SID,SERIAL#’; 或者操作系统层kill-9 XXXX
SQL> alter system kill session '90,13';
System altered.
SQL> select * from v$lock where sid in(90,88) order by sid;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000BED5D9D0 00000000BED5DA28 88 AE 100 0 4 0 6128 0
00000000BBF14478 00000000BBF144F0 88 TX 262161 1093 6 0 63 0
00007F87C64AF310 00007F87C64AF370 88 TM 88681 0 3 0 6071 0
可知90号会话已被终止；88号会话成功持有TX6号锁。
SQL> select sid,LOCKWAIT,status from v$session where sid in(90,88);
SID LOCKWAIT STATUS
---------- ---------------- --------
88 INACTIVE
90 KILLED
90号会话STATUS显示KILLED，会话已被终止
88号会话STATUS显示INACTIVE，SQL语句执行完成；LOCKWAIT为空，已没有锁等待。
Session 2
SQL> select sid from v$mystat where rownum=1;
select sid from v$mystat where rownum=1
*
ERROR at line 1:
ORA-00028: your session has been killed
Session 3
SQL> update cheng_t1 set name='BBBBBB' where id=1;
1 row updated.
SQL> commit;
Commit complete.
关于死锁的实验
Session 1 不变，重新开启两会话
Session 2
SQL> select sid from v$mystat where rownum=1;
SID
----------
175
SQL> update cheng_t1 set name='AAAAAA' where id=1;
1 row updated.
Session 3
SQL> select sid from v$mystat where rownum=1;
SID
----------
217
SQL> update cheng_t1 set name='bbbbbb' where id=2;
1 row updated.
Session 1
SQL> select * from v$lock where sid in (175,217) order by sid;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00007F87C642DA20 00007F87C642DA80 175 TM 88681 0 3 0 66 0
00000000BBF530E8 00000000BBF53160 175 TX 524316 1106 6 0 66 0
00000000BED5D8F0 00000000BED5D948 175 AE 100 0 4 0 83 0
00000000BBF53AF8 00000000BBF53B70 217 TX 393232 1691 6 0 53 0
00007F87C642DA20 00007F87C642DA80 217 TM 88681 0 3 0 53 0
00000000BED5C660 00000000BED5C6B8 217 AE 100 0 4 0 63 0
此时两会话各持有TM、TX锁，并互相不堵塞
Session 2
SQL> update cheng_t1 set name='bbbbbb' where id=2;
会话175被堵塞
Session 1
SQL> select * from v$lock where sid in (175,217) order by sid;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00007F87C64AF310 00007F87C64AF370 175 TM 88681 0 3 0 124 0
00000000BBF530E8 00000000BBF53160 175 TX 524316 1106 6 0 124 0
00000000BED5D8F0 00000000BED5D948 175 AE 100 0 4 0 141 0
00000000BED5D398 00000000BED5D3F0 175 TX 393232 1691 0 6 27 0
00007F87C64AF310 00007F87C64AF370 217 TM 88681 0 3 0 111 0
00000000BBF53AF8 00000000BBF53B70 217 TX 393232 1691 6 0 111 1
00000000BED5C660 00000000BED5C6B8 217 AE 100 0 4 0 121 0
175号会话REQUEST=6，正在申请持有6号TX锁；217号会话TX锁BLOCK=1，说明堵塞了216号会话；
Session 3
SQL> update cheng_t1 set name='AAAAAA' where id=1;
会话217被堵塞（构成和会话175相互堵塞条件）
Session 2
SQL> update cheng_t1 set name='bbbbbb' where id=2;
update cheng_t1 set name='bbbbbb' where id=2
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
175号会话报出ORA-00060错误，自动删除死锁deadlock
Session 1
SQL> select * from v$lock where sid in (175,217) order by sid;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00007F87C64AF310 00007F87C64AF370 175 TM 88681 0 3 0 286 0
00000000BBF530E8 00000000BBF53160 175 TX 524316 1106 6 0 286 1
00000000BED5D8F0 00000000BED5D948 175 AE 100 0 4 0 303 0
00000000BBF53AF8 00000000BBF53B70 217 TX 393232 1691 6 0 273 0
00007F87C64AF310 00007F87C64AF370 217 TM 88681 0 3 0 273 0
00000000BED5CF38 00000000BED5CF90 217 TX 524316 1106 0 6 86 0
00000000BED5C660 00000000BED5C6B8 217 AE 100 0 4 0 283 0
175号会话TX锁BLOCK=1，堵塞了217号会话；217号会话TX锁REQUEST=6，在申请持有6号TX锁；
Linux窗口
看看死锁的相关日志
[oracle@localhost ~]$ oerr ora 00060
00060, 00000, "deadlock detected while waiting for resource"
// *Cause: Transactions deadlocked one another while waiting for resources.
// *Action: Look at the trace file to see the transactions and resources
// involved. Retry if necessary.
[oracle@localhost ~]$ cd $ORACLE_BASE
[oracle@localhost app]$ tail -f diag/rdbms/orcl/orcl/trace/alert_orcl.log
Tue Jun 06 23:01:06 2017
ORA-00060: Deadlock detected. More info in file /oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_6747.trc.
Oracle在死锁发生后生成一份日志，有时Oracle接着会自动启动Smon进程
[oracle@localhost app]$ more /oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_6747.trc
......
*** 2017-06-06 23:01:06.270
DEADLOCK DETECTED ( ORA-00060 )
[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 process session holds waits
TX-0008001c-00000452 22 175 X 23 217 X
TX-00060010-0000069b 23 217 X 22 175 X
session 175: DID 0001-0016-00000164 session 217: DID 0001-0017-000000B1
session 217: DID 0001-0017-000000B1 session 175: DID 0001-0016-00000164
Rows waited on:
Session 175: obj - rowid = 00015A69 - AAAVppAAEAAAAIPAAB
(dictionary objn - 88681, file - 4, block - 527, slot - 1)
Session 217: obj - rowid = 00015A69 - AAAVppAAEAAAAIPAAA
(dictionary objn - 88681, file - 4, block - 527, slot - 0)
----- Information for the OTHER waiting sessions -----
Session 217:
sid: 217 ser: 37 audsid: 280258 user: 90/CHENG
flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 23 O/S info: user: oracle, term: UNKNOWN, ospid: 6749
image: oracle@localhost.localdomain (TNS V1-V3)
client details:
O/S info: user: oracle, term: pts/1, ospid: 6748
machine: localhost.localdomain program: sqlplus@localhost.localdomain (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
current SQL:
update cheng_t1 set name='AAAAAA' where id=1
----- End of information for the OTHER waiting sessions -----
Information for THIS session:
----- Current SQL Statement for this session (sql_id=9rzgvs6xydnpk) -----
update cheng_t1 set name='bbbbbb' where id=2
===================================================
......
日志中包含产生死锁的会话信息，用户信息及产生死锁的语句等信息，帮助找到产生死锁原因。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31467990/viewspace-2140928/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/31467990/viewspace-2140928/
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
