Oracle做insert或者update时未提交事务导致表锁定解决办法 转载 

//查看被锁定表有几个
select object_name,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s
where l.object_id　=　o.object_id and l.session_id=s.sid;

//清除锁定251,3055为查询出来的sid,serial值
alter system kill session '251,3055';

Oracle未提交事务引起的锁

笔者所在公司最近上了一套系统，系统使用中间件连接Oracle数据库，使用一段时间之后系统就会停止响应。发现问题在于2点：

1. 中间件有线程执行了Delete语句之后，一直处于等待状态，没有COMMIT提交事务，对表施加了行锁且该线程无法复用（中间件总线程数有限制）

2. 对表施加了行锁之后，后续中间件线程如果需要修改该行，将被阻塞。

以上两个因素不断发生，最终导致中间件线程数耗尽，系统停止响应。

下面会分两个部分，第一个部分是直接给出判断以上故障的SQL语句，第二部分是通过做实验的方法重现问题

 1.直接判断未提交事务引起的表的行锁

1.1　　判断哪个SESSION执行了DML（Insert/Update/Delete） 但是未提交 （Commit ），引起的行锁

  1. --找到修改了数据，但是未提交的Session,选择WAIT_CALSS='Idle'，也就是Session处于休息状态，但是有锁定的表
  2. 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
  3. FROM V$SESSION A
  4. INNER JOIN V$LOCKED_OBJECT B
  5. ON A.SID=b.SESSION_ID
  6. INNER JOIN DBA_OBJECTS C
  7. ON B.OBJECT_ID=c.OBJECT_ID
  8. WHERE A.WAIT_CLASS='Idle'
  9. AND A.SECONDS_IN_WAIT>10/*SESSION空闲后一段时间还锁定的才算有问题,这里随便给了个数值10秒*/
  10.  
  11. SID SERIAL# USERNAME EVENT WAIT_CLASS SECONDS_IN_WAIT PREV_EXEC_START LOCKED_MODE OWNER OBJECT_NAME OBJECT_TYPE
  12. ---------- ---------- ------------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- --------------- --------------- ----------- ------------------------------ -------------------------------------------------------------------------------- -------------------
  13. 194 240 TEST SQL*Net message from client Idle 18046 2018/9/17 18:30 3 TEST TESTLOCK TABLE

如果确认这些SESSION确实有问题，可以直接KILL掉，传入前面语句的SID和SERIAL#

ALTER SYSTEM KILL SESSION 'SID,SERIAL#'

注：ORACLE有个很有趣的地方，就是KILL SESSION的时候需要填“”SERIAL#“，是为了避免刚刚查询的时候获得了一个SID，然后你打算杀掉他，在这个时间之间，打算杀掉的SESSION结束了断开连接，然后新的SESSION复用了这个SID，也就是避免误杀，SQL SERVER 直接KILL可以了。

1.2　　判断哪些SESSION被阻塞了，且定位到是谁阻塞了，是由于哪个表哪个行引起的阻塞

  1. --当SESSION被阻塞，通过ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#这几个字段找到ROWID，然后通过ROWID找到被锁定的记录
  2. SELECT BLOCKING_SESSION,SID,SERIAL#,AUDSID,PADDR,USER#,USERNAME,EVENT,WAIT_CLASS,SECONDS_IN_WAIT,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#,
  3. BLOCKING_SESSION_STATUS,BLOCKING_INSTANCE,C.OWNER,C.OBJECT_NAME,C.OBJECT_TYPE
  4. ,dbms_rowid.rowid_create(1,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#)
  5. FROM V$SESSION A
  6. INNER JOIN V$LOCKED_OBJECT B
  7. ON A.SID=b.SESSION_ID
  8. INNER JOIN DBA_OBJECTS C
  9. ON B.OBJECT_ID=c.OBJECT_ID
  10. WHERE BLOCKING_SESSION IS NOT NULL ;
  11.  
  12. 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 OWNER OBJECT_NAME OBJECT_TYPE DBMS_ROWID.ROWID_CREATE(1,ROW_
  13. ---------- ---------- ---------- ---------------- ---------- ------------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- --------------- ------------- -------------- --------------- ------------- ----------------------- ----------------- ---------------- ------------------------------ -------------------------------------------------------------------------------- ------------------- ------------------------------
  14. 12 4449 4294967295 00007FFFF04DDFA0 0 SYS enq: TX - row lock contention Application 18245 75465 4 175 1 VALID 1 194 TEST TESTLOCK TABLE AAASbJAAEAAAACvAAB

 

通过上面返回的OBJECT_NAME 和ROWID，知道是由于那个行引起的阻塞

  1. SELECT * FROM 前面返回的表名称
  2. where ROWID=前面返回的ROWID
  1. SQL> select * from test.TESTLOCK where rowid='AAASbJAAEAAAACvAAB';
  2. ID NAME
  3. ---------- --------------------------------------------------------------------------------
  4. 2 kkkkkkkkkkkkkk

1.3哪些session阻碍了，哪些被阻碍了以及执行的Sql语句

  1. SQL> SELECT s.BLOCKING_SESSION, l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, s.user#,
  2. 2 l.os_user_name,s.machine, s.terminal,a.sql_text, a.action
  3. 3 FROM v$sqlarea a,v$session s, v$locked_object l
  4. 4 WHERE l.session_id = s.sid
  5. 5 AND s.prev_sql_addr = a.address
  6. 6 ORDER BY sid, s.serial#;
  7. BLOCKING_SESSION SID SERIAL# LOCKED_MODE ORACLE_USERNAME USER# OS_USER_NAME MACHINE TERMINAL SQL_TEXT ACTION
  8. ---------------- ---------- ---------- ----------- ------------------------------ ---------- ------------------------------ ---------------------------------------------------------------- ---------------- -------------------------------------------------------------------------------- ----------------------------------------------------------------
  9. 194 12 4449 3 SYS 0 DIGITAL-CAIYH\CaiYH WORKGROUP\DIGITAL-CAIYH DIGITAL-CAIYH update test.testlock set name='kkkkkkkkkkkkkk' where id=2
  10. 194 240 3 TEST 86 DIGITAL-CAIYH\CaiYH WORKGROUP\DIGITAL-CAIYH DIGITAL-CAIYH update testlock set name='eeeeeeeeee' where id=2
  11.  
  12. SQL>

1.4 查询那张表被锁定 被谁锁定

  1. --查询那张表被锁定 被谁锁定
  2. select s.sid,
  3. s.serial#,
  4. lo.oracle_username,
  5. lo.os_user_name,
  6. ao.object_name as 被锁表名称table_locked_name,
  7. s.username,
  8. s.schemaname,
  9. s.osuser,
  10. s.process,
  11. s.machine,
  12. s.terminal,
  13. lo.locked_mode
  14. from v$locked_object lo, all_objects ao, v$session s
  15. where ao.object_id = lo.object_id
  16. and lo.session_id = s.sid
  17. order by s.sid asc;
  18.  
  19. SID SERIAL# ORACLE_USERNAME OS_USER_NAME 被锁表名称TABLE_LOCKED_NAME USERNAME SCHEMANAME OSUSER PROCESS MACHINE TERMINAL LOCKED_MODE
  20. ---------- ---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------ ---------------------------------------------------------------- ---------------- -----------
  21. 12 4449 SYS DIGITAL-CAIYH\CaiYH TESTLOCK SYS SYS DIGITAL-CAIYH\CaiYH 9480:21728 WORKGROUP\DIGITAL-CAIYH DIGITAL-CAIYH 3
  22. 194 240 TEST DIGITAL-CAIYH\CaiYH TESTLOCK TEST TEST DIGITAL-CAIYH\CaiYH 15212:12596 WORKGROUP\DIGITAL-CAIYH DIGITAL-CAIYH 3

2.通过实验过程重现整个过程

首先说明，Oracle数据库是写阻塞写，读写之间是互相不阻塞的，也就是以下两个语句在不同的窗口（SESSION）执行的话，由于它们都是更新同一行，如果第一个执行的没有COMMIT，后执行那个会一直处于被阻塞状态：

 

  1. --第一个窗口执行如下语句
  2. UPDATE TESTLOCK
  3. SET AAA=11
  4. WHERE AAA=1
  5.  
  6. --第二个窗口执行以下语句
  7. UPDATE TESTLOCK
  8. SET AAA=12
  9. WHERE AAA=1

下面开始我们的实验，Oracle的版本是11G 带着如下问题：

  1. 如何知道一个连接修改了数据，但是未提交，导致对表产生了锁定？
  2. 对于1的疑问，可否知道具体锁定了哪个表的哪个记录？

首先我们在PS/SQL客户端建立三个窗口,分别为：测试窗口1、测试窗口2、观察窗口，实验以“TEST”账号登录，为了避免其他SESSION干扰我们的实验，很多语句添加了“WHERE USERNAME='TEST'“作为限定

，如果想重复实验过程，需要修改这部分语句。

1.在测试窗口1建立测试用的表，并插入10条记录

 

  1. /*初始化测试表*/
  2.  
  3. --建立一个测试表
  4. create table TESTLOCK
  5. (
  6. aaa number not null,
  7. bbb nvarchar2(10) not null,
  8. ccc nvarchar2(10) not null
  9. );
  10. create INDEX PK_TESTLOCK on TESTLOCK (aaa);
  11.  
  12.  
  13. --随便插入点数据
  14. INSERT INTO TESTLOCK VALUES ('1','BBB','CCC');
  15. INSERT INTO TESTLOCK VALUES ('2','BBB','CCC');
  16. INSERT INTO TESTLOCK VALUES ('3','BBB','CCC');
  17. INSERT INTO TESTLOCK VALUES ('4','BBB','CCC');
  18. INSERT INTO TESTLOCK VALUES ('5','BBB','CCC');
  19. INSERT INTO TESTLOCK VALUES ('6','BBB','CCC');
  20. INSERT INTO TESTLOCK VALUES ('7','BBB','CCC');
  21. INSERT INTO TESTLOCK VALUES ('8','BBB','CCC');
  22. INSERT INTO TESTLOCK VALUES ('9','BBB','CCC');
  23. INSERT INTO TESTLOCK VALUES ('10','BBB','CCC');
  24.  
  25.  
  26. --然后我们的表里面就有了一些数据
  27. SELECT * FROM TESTLOCK;
  28. COMMIT;

2.在测试窗口2，查询测试表 ，可以看到步骤1添加的10条记录

SELECT * FROM TESTLOCK;

 

3.在观察窗口观察当前Session的情况和表锁的情况 

这里我们用到了Oracle的三个系统试图

V$SESSION：

V$LOCK：

V$LOCKED_OBJEC：

 

  1. --当前的Session情况
  2. 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
  3. FROM V$SESSION
  4. WHERE USERNAME='TEST';
  5.  
  6. --当前TESTLOCK表锁的情况
  7. SELECT * FROM V$LOCK
  8. WHERE TYPE='TM'
  9. AND ID1=(SELECT OBJECT_ID FROM DBA_OBJECTS
  10. WHERE OBJECT_NAME='TESTLOCK');
  11.  
  12. SELECT XIDUSN,XIDSLOT,XIDSQN,OBJECT_ID,SESSION_ID,ORACLE_USERNAME,PROCESS,LOCKED_MOD
  13. FROM V$LOCKED_OBJEC
  14. WHERE OBJECT_ID=(SELECT OBJECT_ID FROM DBA_OBJECTS
  15. 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表没有被锁定

 

4.　在测试窗口1更新表TESTLOCK，但是不要提交（Commit）

  1. UPDATE TESTLOCK
  2. SET AAA=11
  3. WHERE AAA=1

PL/SQL左上角会展示提交和回滚的图标，表示事务没有提交

 

5.再次在观察窗口执行步骤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

 

6.回到之前的第一个问题，如何知道一个SESSION修改了数据但是没COMMIT，在观察窗口执行如下语句：

 

  1. --找到修改了数据，但是未提交的Session,选择WAIT_CALSS='Idle'，也就是Session处于休息状态，但是有锁定的表
  2. 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
  3. FROM V$SESSION A
  4. INNER JOIN V$LOCKED_OBJECT B
  5. ON A.SID=b.SESSION_ID
  6. INNER JOIN DBA_OBJECTS C
  7. ON B.OBJECT_ID=c.OBJECT_ID
  8. WHERE A.WAIT_CLASS='Idle'
  9. AND A.SECONDS_IN_WAIT>10/*SESSION空闲后一段时间还锁定的才算有问题,这里随便给了个数值10秒*/
  10. AND USERNAME='TEST';

只需要判断WAIT_CLASS='Idle',同时在V$LOCKED_OBJECT存在锁定的对象且SESSION空闲了一段时间，如图，就可以判断SID=1947 锁定了TESTLOCK表，SECONDS_IN_WAIT就可以认为是锁定的时长，单位是秒 

 

7.在观察窗口执行以下语句，从事务的角度观察

 

  1. --从事务角度观察，连接v$session和v$transaction
  2. SELECT A.SID,A.SERIAL#,A.USERNAME,A.EVENT,A.WAIT_CLASS,A.SECONDS_IN_WAIT,A.PREV_EXEC_START,b.START_DATE
  3. FROM v$session a
  4. INNER JOIN v$transaction b
  5. ON a.taddr=b.addr
  6. WHERE USERNAME='TEST'

连接两个视图后，可以知道SID=1947启动了事务

 

8.接下来我们来验证在测试窗口2更新和删除记录，但是更新和删除的记录和测试窗口1的不是同一条记录，测试窗口1更新的记录为AAA=1,在更新和删除后马上提交(COMMIT)

 

  1. --更新和测试窗口1不同的记录
  2. UPDATE TESTLOCK
  3. SET AAA=100
  4. WHERE AAA=2;
  5. COMMIT ;
  6.  
  7. --删除和测试窗口1不同的记录
  8. DELETE FROM TESTLOCK
  9. WHERE AAA=3;
  10. COMMIT;
  11.  
  12. SELECT * FROM TESTLOCK;

可以看到更新和删除都没有被阻塞，查询表结果如图，可以看到AAA=2的数据被更新了，AAA=3的数据被删除，AAA=1的数据还是和原来一样，即看不到测试窗口1的数据更新，也就是凡是没有提交的数据都是看不到的。

 

9.下面来验证：在前面一个UPDATE没有提交的情况下，另外一个连接修改TESTLOCK的同一条记录，会发生什么？

在测试窗口2执行以下语句：但是不要提交（COMMIT）

  1. --更新和测窗口1相同的记录
  2. UPDATE TESTLOCK
  3. SET AAA=12
  4. WHERE AAA=1

该语句会一直处于“正在执行”状态，实际上就是TESTLOCK上面有行锁，该SESSION一直在等待之前的行锁释放 

 

10.再次在观察窗口执行步骤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，可以判断为被阻塞

 

11. 寻找被锁定的具体行

在观察窗口执行如下语句：

 

  1. --当SESSION被阻塞，通过ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#这几个字段找到ROWID，然后通过ROWID找到被锁定的记录
  2. 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#,
  3. BLOCKING_SESSION_STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION,C.OWNER,C.OBJECT_NAME,C.OBJECT_TYPE
  4. ,dbms_rowid.rowid_create(1,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#)
  5. FROM V$SESSION A
  6. INNER JOIN V$LOCKED_OBJECT B
  7. ON A.SID=b.SESSION_ID
  8. INNER JOIN DBA_OBJECTS C
  9. ON B.OBJECT_ID=c.OBJECT_ID
  10. WHERE USERNAME='TEST'
  11. AND BLOCKING_SESSION IS NOT NULL ;

 获得具体被阻塞表的ROWID，语句是dbms_rowid.rowid_create(1,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#)

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

  1. --通过前面的函数rowid_create获得具体的ROWID，然后在锁定表中查询记录
  2. SELECT * FROM TESTLOCK
  3. where ROWID='AAJ2QDAAnAAGrwnAAA'

 

 

12.杀死阻塞了其他连接的SESSION

从前面可见是SESSION 1947 阻塞了当前线程，查询获得SID=1947，查询其SERIAL#，然后执行SQL杀死该SESSION

ALTER SYSTEM KILL SESSION '1947,63353'

 13. 再次观察当前SESSION情况，SID=9，不再是阻塞状态

 

  1. 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#,
  2. BLOCKING_SESSION_STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION
  3. FROM V$SESSION
  4. WHERE USERNAME='TEST';

 

oracle 操作表时没及时提交事务造成死锁问题

oracle 操作表时没及时提交事务造成死锁问题，解决方案如下：

– 查死锁
select session_id from v$locked_object;

– 查SERIAL

select * from v$session where session_id = ‘’;

– 杀死锁

alter system kill session ‘sid,#SERIAL’

posted @ 2021-04-06 16:21  CharyGao  阅读(1306)  评论(0)    收藏  举报