初探oracle高级队列锁V$LOCK

来源：互联网发布：马赛克复原软件编辑：程序博客网时间：2024/05/17 07:50

先了解V$lock视图字段的意义

SQL> desc 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.0Production
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 400

在开启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 1100
00007F87C64CA7A8 00007F87C64CA808 90 TM 88681 0 3 0 200
00000000BBF13A68 00000000BBF13AE0 90 TX 3932251691 6 0 200

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 3932251691 6 0 730

SQL> select ADDR,XIDUSN,XIDSLOT,XIDSQN from v$transaction;

ADDR XIDUSN XIDSLOT XIDSQN
---------------- ---------- --------- ----------
00000000BBF13A68 6 9 1691

v$transaction显示事务的唯一标识符；
可知：
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 320
00000000BED5D9D0 00000000BED5DA28 88 AE 100 0 4 0 890
00000000BED5C040 00000000BED5C098 88 TX 3932251691 0 6 320
00007F87C64D08F8 00007F87C64D0958 90 TM 88681 0 3 0 2920
00000000BBF13A68 00000000BBF13AE0 90 TX 3932251691 6 0 2921
00000000BED5DFF0 00000000BED5E048 90 AE 100 0 4 0 3820

可知：
1、90号会话TX锁BLOCK字段显示为1，堵塞了其它会话（即4号会话）；
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 7000
00007F87C642C638 00007F87C642C698 90 TM 88681 0 3 0 6100

00000000BBF13A68 00000000BBF13AE0 90 TX 3932251691 6 0 6101

发现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 3932251691 0 6 4030
00000000BBF13A68 00000000BBF13AE0 90 TX 3932251691 6 0 6631

可知被堵塞（或者是请求锁）的会话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 3932251691

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 5431 75736 0
88 enq: TX - row lock contention 1415053318 3932251691 886814 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

堵塞88号会话的sql语句

也可以dump出4号数据文件527号块获取更多信息
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 50510
00000000BED5D9D0 00000000BED5DA28 88 AE 100 0 4 0 51080
00000000BED5C040 00000000BED5C098 88 TX 3932251691 0 6 50510
00007F87C642DA20 00007F87C642DA80 90 TM 88681 0 3 0 53110
00000000BBF13A68 00000000BBF13AE0 90 TX 3932251691 6 0 53111
00000000BED5DFF0 00000000BED5E048 90 AE 100 0 4 0 54010

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 USERNAMECOMMAND 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_ID找到引起堵塞的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 61280
00000000BBF14478 00000000BBF144F0 88 TX 2621611093 6 0 630

00007F87C64AF310 00007F87C64AF370 88 TM 88681 0 3 0 60710

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 660
00000000BBF530E8 00000000BBF53160 175 TX 5243161106 6 0 660
00000000BED5D8F0 00000000BED5D948 175 AE 100 0 4 0 830
00000000BBF53AF8 00000000BBF53B70 217 TX 3932321691 6 0 530
00007F87C642DA20 00007F87C642DA80 217 TM 88681 0 3 0 530
00000000BED5C660 00000000BED5C6B8 217 AE 100 0 4 0 630

此时两会话各持有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;

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 2860
00000000BBF530E8 00000000BBF53160 175 TX 5243161106 6 0 2861
00000000BED5D8F0 00000000BED5D948 175 AE 100 0 4 0 3030
00000000BBF53AF8 00000000BBF53B70 217 TX 3932321691 6 0 2730
00007F87C64AF310 00007F87C64AF370 217 TM 88681 0 3 0 2730
00000000BED5CF38 00000000BED5CF90 217 TX 5243161106 0 6 860
00000000BED5C660 00000000BED5C6B8 217 AE 100 0 4 0 2830

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
===================================================
......
日志中包含产生死锁的会话信息，用户信息及产生死锁的语句等信息，帮助找到产生死锁原因。

阅读全文

0 0