本文主要向大家介绍了Oracle数据库之Oracle未提交事务引起的锁，通过具体的内容向大家展现，希望对大家学习Oracle数据库有所帮助。

解释   笔者所在公司最近上了一套系统，系统使用中间件连接Oracle数据库，使用一段时间之后系统就会停止响应。发现问题在于2点：
1. 中间件有线程执行了Delete语句之后，一直处于等待状态，没有COMMIT提交事务，对表施加了行锁且该线程无法复用（中间件总线程数有限制）
2. 对表施加了行锁之后，后续中间件线程如果需要修改该行，将被阻塞。
以上两个因素不断发生，最终导致中间件线程数耗尽，系统停止响应。
 
下面会分两个部分，第一个部分是直接给出判断以上故障的SQL语句，第二部分是通过做实验的方法重现问题
 
 
 1.直接判断未提交事务引起的表的行锁
1.1　　判断哪个SESSION执行了DML（Insert/Update/Delete） 但是未提交 （Commit ），引起的行锁
1.2　　判断哪些SESSION被阻塞了，且定位到是谁阻塞了，是由于哪个表哪个行引起的阻塞
 
2.通过实验过程重现整个过程
首先说明，Oracle数据库是写阻塞写，读写之间是互相不阻塞的，也就是以下两个语句在不同的窗口（SESSION）执行的话，由于它们都是更新同一行，如果第一个执行的没有COMMIT，后执行那个会一直处于被阻塞状态：
--第一个窗口执行如下语句
UPDATE TESTLOCK
SET AAA=11
WHERE AAA=1
--第二个窗口执行以下语句
UPDATE TESTLOCK
SET AAA=12
WHERE AAA=1
 
下面开始我们的实验，Oracle的版本是11G 带着如下问题：
如何知道一个连接修改了数据，但是未提交，导致对表产生了锁定
对于1的疑问，可否知道具体锁定了哪个表的哪个记录？
首先我们在PS/SQL客户端建立三个窗口,分别为：
测试窗口1
测试窗口2
观察窗口
在测试窗口1建立测试用的表，并插入10条记录
/*初始化测试表*/
--建立一个测试表
create table TESTLOCK
(
  aaa number not null,
  bbb nvarchar2(10) not null,
  ccc nvarchar2(10) not null
);
create INDEX PK_TESTLOCK on TESTLOCK (aaa);
 
--随便插入点数据
INSERT INTO TESTLOCK VALUES (‘1‘,‘BBB‘,‘CCC‘);
INSERT INTO TESTLOCK VALUES (‘2‘,‘BBB‘,‘CCC‘);
INSERT INTO TESTLOCK VALUES (‘3‘,‘BBB‘,‘CCC‘);
INSERT INTO TESTLOCK VALUES (‘4‘,‘BBB‘,‘CCC‘);
INSERT INTO TESTLOCK VALUES (‘5‘,‘BBB‘,‘CCC‘);
INSERT INTO TESTLOCK VALUES (‘6‘,‘BBB‘,‘CCC‘);
INSERT INTO TESTLOCK VALUES (‘7‘,‘BBB‘,‘CCC‘);
INSERT INTO TESTLOCK VALUES (‘8‘,‘BBB‘,‘CCC‘);
INSERT INTO TESTLOCK VALUES (‘9‘,‘BBB‘,‘CCC‘);
INSERT INTO TESTLOCK VALUES (‘10‘,‘BBB‘,‘CCC‘);
 
--然后我们的表里面就有了一些数据
SELECT * FROM TESTLOCK;
 
COMMIT;
 
在测试窗口2，查询测试表
SELECT * FROM TESTLOCK;
 
在观察窗口观察当前Session的情况和表锁的情况
--当前的Session情况
SELECT SID,SERIAL#,AUDSID,PADDR,USER#,USERNAME,EVENT,WAIT_CLASS,SECONDS_IN_WAIT,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#,
        BLOCKING_SESSION_STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION
FROM V$SESSION
WHERE USERNAME=‘TEST‘;
 
--当前TESTLOCK表锁的情况
SELECT * FROM V$LOCK
WHERE TYPE=‘TM‘
AND ID1=(SELECT OBJECT_ID FROM DBA_OBJECTS
                 WHERE OBJECT_NAME=‘TESTLOCK‘);
 
SELECT XIDUSN,XIDSLOT,XIDSQN,OBJECT_ID,SESSION_ID,ORACLE_USERNAME,PROCESS,LOCKED_MODE
FROM V$LOCKED_OBJECT
WHERE OBJECT_ID=(SELECT OBJECT_ID FROM DBA_OBJECTS
                 WHERE OBJECT_NAME=‘TESTLOCK‘);
 
从下图可以看出TEST账号一共产生了4个SESSION，分别是PLSQL本身连接到数据库和我们建立的三个窗口
 
 
 
 
 
 
 
我们关注几个字段：
EVENT:oracle的session正在等待的数据或者事件
WAIT_CLASS:等待事件的名称
blocking_session_status:如果blocking_session_status字段是VALID,表示该SESSION被阻塞了
blocking_session:被哪个Session阻塞
 
通过以上4项观察，没有任何Session被阻塞，当前三个Session处于等待客户端消息状态（EVENT=SQL*Net message from client， WAIT_CLASS=Idle）,剩下一个Session正在向客户端发送消息（EVENT=SQL*Net message to client，WAIT_CLASS=Network）,就是我们当前的观察窗口
 
后面两个查询V$LOCK和V$LOCKED_OBJECT的语句没有任何返回，表示当前TESTLOCK表没有被锁定
 
 
 
 
 
在测试窗口1更新TESTLOCK，但是不要Commit
UPDATE TESTLOCK
SET AAA=11
WHERE AAA=1
 
 
再次在观察窗口执行步骤3的语句：
通过对V$LOCK和V$LOCKED_OBJECT的查询可以知道，SID=1947 锁定了TESTLOCK表，其中LMODE=3（行级排他锁，我们这里是通过UPDATE产生的）
 
 
 
 
 
 
 
锁模式
锁描述
解释
SQL操作
0
none
 
 
1
NULL
空
Select
2
SS(Row-S)
行级共享锁，其他对象只能查询这些数据行
Select for update
Lock for update
Lock row share
3
SX(Row-X)
行级排它锁,在提交前不允许做DML操作
Insert/update/Delete
Lock row share
4
S(Share)
共享锁
Create index
Lock share
5
SSX(S/Row-X)
共享行级排它锁
Lock share row exclusive
6
X(Exclusive)
排它锁
Alter table
Drop able
Drop index
Truncate table
Lock exclusive
 
 
回到之前的第一个问题，如何知道一个SESSION修改了数据但是没COMMIT，在观察窗口执行如下语句：
--找到修改了数据，但是未提交的Session,选择WAIT_CALSS=‘Idle‘，也就是Session处于休息状态，但是有锁定的表
SELECT A.SID,A.SERIAL#,A.USERNAME,A.EVENT,A.WAIT_CLASS,A.SECONDS_IN_WAIT,A.PREV_EXEC_START,b.LOCKED_MODE,C.OWNER,C.OBJECT_NAME,C.OBJECT_TYPE
FROM V$SESSION A
INNER JOIN V$LOCKED_OBJECT B
ON A.SID=b.SESSION_ID
INNER JOIN DBA_OBJECTS C
ON B.OBJECT_ID=c.OBJECT_ID
WHERE A.WAIT_CLASS=‘Idle‘
AND A.SECONDS_IN_WAIT>10/*SESSION空闲后一段时间还锁定的才算有问题,这里随便给了个数值10秒*/
AND USERNAME=‘TEST‘;
 
只需要判断WAIT_CLASS=‘Idle‘,同时在V$LOCKED_OBJECT存在锁定的对象且SESSION空闲了一段时间，如图，就可以判断SID=1947 锁定了TESTLOCK表，SECONDS_IN_WAIT就可以认为是锁定的时长，单位是秒
 
 
 
 
在观察窗口执行以下语句，从事务的角度观察
--从事务角度观察，连接v$session和v$transaction
SELECT A.SID,A.SERIAL#,A.USERNAME,A.EVENT,A.WAIT_CLASS,A.SECONDS_IN_WAIT,A.PREV_EXEC_START,b.START_DATE
FROM v$session a
INNER JOIN v$transaction b
ON a.taddr=b.addr
WHERE USERNAME=‘TEST‘
 
可以观察到PREV_EXEC_START 和v$transaction的START_DATE 是一致的
 
 
 
在前面一个UPDATE没有提交的情况下，另外一个Session修改TESTLOCK的同一条记录，会发生什么？
在测试窗口2执行以下语句：
--更新和测窗口1相同的记录
UPDATE TESTLOCK
SET AAA=12
WHERE AAA=1
 
该语句会一直处于“正在执行”状态，实际上就是TESTLOCK上面有行锁，该SESSION一直在等待之前的行锁释放
 
 
 
再次在观察窗口执行步骤3的语句：
 
观察SID=9，显示EVENT=‘enq: TX - row lock contention‘ ,表示正在等待一个行锁释放，BLOCKING_SESSION 说明该SESSION被SID=1947 阻塞了，也就是测试窗口1的SESSION
 
 
观察V$LOCK其实区分不了哪个LOCK是没提交，哪个是，两个LOCK的显示都是一样的，这点倒很奇怪
 
 
 
观察V$LOCKED_OBJECT，可以通过XINUSN/XIDSLOT/XIDSQN 判断，这三个字段是和回滚相关的字段，如果都为0，可以判断为被阻塞
 
 
有没有办法知道表具体的哪行被锁定了？
在观察窗口执行如下语句：
--当SESSION被阻塞，通过ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#这几个字段找到ROWID，然后通过ROWID找到被锁定的记录
SELECT SID,SERIAL#,AUDSID,PADDR,USER#,USERNAME,EVENT,WAIT_CLASS,SECONDS_IN_WAIT,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#,
        BLOCKING_SESSION_STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION,C.OWNER,C.OBJECT_NAME,C.OBJECT_TYPE
        ,dbms_rowid.rowid_create(1,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#)
FROM V$SESSION A
INNER JOIN V$LOCKED_OBJECT B
ON A.SID=b.SESSION_ID
INNER JOIN DBA_OBJECTS C
ON B.OBJECT_ID=c.OBJECT_ID
WHERE USERNAME=‘TEST‘
AND BLOCKING_SESSION IS NOT NULL ;
 
--通过前面的函数rowid_create获得具体的ROWID，然后在锁定表中查询记录
SELECT * FROM TESTLOCK
where ROWID=‘AAJ2QDAAnAAGrwnAAA‘

获得具体被阻塞表的ROWID

查询获得具体的被阻塞记录，正好是我们在测试窗口1  Update的记录
 

V$SESSION的字段解释可参见：
https://docs.oracle.com/cd/E18283_01/server.112/e17110/dynviews_3016.htm

本文由职坐标整理并发布，希望对同学们学习Oracle有所帮助，更多内容请关注职坐标数据库Oracle数据库频道！