标签归档：锁

Oracle中诊断阻塞的session

发表回复

由于锁的机制，当某一条DML或者DDL SQL语句执行被阻塞的时候，需要找出是什么原因导致这条SQL被阻塞了，下面介绍两种常用的诊断方法：
假设有这样一个表：table t(id int primary key,val int)，数据为：

id      val
1       1
2       2
[oracle@10g ~]$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 28 16:48:03 2013
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table t (id int primary key,val int); 
Table created.
SQL> insert into t values (1,1);
1 row created.
SQL> insert into t values (2,2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
        ID        VAL
---------- ----------
         1          1
         2          2

1.在第一个Session，这里把它叫做Session A，做了如下的update语句，没有提交或者回滚：

SQL> update t set val = 3 where id=1;
1 row updated.

2.在另一个Session里，这里把它叫做Session B，做了如下的update语句，Session B会被阻塞：

SQL> update t set val = 4 where id=1;

方法一：
有活动事务对对象加锁的时候，在v$locked_object视图中会有记录，如object_id,session_id等，通常被阻塞session的XIDUSN,XIDSLOT,XIDSQN字段都为空，下面查询中session_id为138的是被阻塞的session：

SQL> conn / as sysdba
Connected.
SQL> select * from v$locked_object;
XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE
------ ------- ------ --------- ---------- --------------- ------------ ------- -----------
     0       0      0     54364        138 SCOTT           oracle       25455             3
     7      24    322     54364        143 SCOTT           oracle       25338             3

通过v$locked_object和dba_objects关联查询出被阻塞session的对象：

SQL> select dbo.*
  2    from v$locked_object lo, dba_objects dbo
  3   where lo.object_id = dbo.object_id
  4     and lo.xidusn = 0;
OWNER OBJECT_NAM SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S
----- ---------- -------------- --------- -------------- ----------- --------- --------- ------------------- ------- - - -
SCOTT T                             54364          54364 TABLE       28-AUG-13 28-AUG-13 2013-08-28:16:48:53 VALID   N N N

通过查询v$lock可以看到是哪一个session阻塞了哪一个session:143阻塞了138

SQL> select blkingsess.sid blockingsid, blkedsess.sid blockedsid
  2    from v$lock blkingsess,
  3         (select * from v$lock where request != 0) blkedsess
  4   where blkingsess.id1 = blkedsess.id1
  5     and blkingsess.id2 = blkedsess.id2
  6     and blkingsess.sid != blkedsess.sid;
BLOCKINGSID BLOCKEDSID
----------- ----------
        143        138

在通过v$session可以查到session相关的信息，被阻塞的status一般为ACTIVE，还可以通过sql_address关联v$sql找到被阻塞的SQL语句：

SQL> select sid, serial#, status, sql_address
  2    from v$session
  3   where sid in (143, 138);
SID SERIAL# STATUS   SQL_ADDRESS
--- ------- -------- ----------------
138    5914 ACTIVE   000000008358A478
143    7044 INACTIVE 000000007AA1DA68
SQL> select sql_id,sql_text from v$sql where address='000000008358A478';
SQL_ID        SQL_TEXT
------------- -------------------------------
cgxjac574kd3q update t set val = 4 where id=1

这时候DBA可以联系造成阻塞的session结束事务或者根据情况用命令终止session：

SQL> alter system kill session '143,7044';
System altered.

其中143为sid，7044为serial#，session 143会收到如下错误，session 138可以执行后续步骤.

ERROR:
ORA-03114: not connected to ORACLE

方法二：
查看等待事件会发现存在锁表：

SQL> select sid, event
  2    from v$session_wait
  3   where event not like 'SQL%'
  4     and event not like 'rdbms%';
       SID EVENT
---------- ----------------------------------------------------------------
       135 jobq slave wait
       138 enq: TX - row lock contention
       140 wait for unread message on broadcast channel
       149 Streams AQ: waiting for time management or cleanup tasks
       150 Streams AQ: qmn slave idle wait
       153 Streams AQ: qmn coordinator idle wait
       164 smon timer
       170 pmon timer
8 rows selected.

查看此锁的持有者与等待着：

SQL> column sess format a15
SQL> SELECT DECODE(request, 0, 'Holder: ', 'Waiter: ') || sid sess,
  2         id1,
  3         id2,
  4         lmode,
  5         request,
  6         type
  7    FROM V$LOCK
  8   WHERE (id1, id2, type) IN
  9         (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
 10   ORDER BY id1, request;
SESS           ID1 ID2 LMODE REQUEST TY
----------- ------ --- ----- ------- --
Holder: 143 458776 322     6       0 TX
Waiter: 138 458776 322     0       6 TX

查出此锁持有者session的session id，机器，程序等信息；

SQL> select s.sid,
  2         s.serial#,
  3         s.program,
  4         s.machine,
  5         s.username,
  6         s.last_call_et,
  7         s.logon_time,
  8         sw.event
  9    from v$session s, v$session_wait sw
 10   where s.sid = sw.sid
 11     and s.sid = 143;
SID SERIAL# PROGRAM                             MACHINE         USERNAME   LAST_CALL_ET LOGON_TIM EVENT
--- ------- ----------------------------------- --------------- ---------- ------------ --------- ------------------------------
143    7044 sqlplus@10g.localdomain (TNS V1-V3) 10g.localdomain SCOTT              3500 28-AUG-13 SQL*Net message from client

这时候DBA可以联系造成阻塞的session结束事务或者根据情况用命令终止session：

SQL> alter system kill session '143,7044';
System altered.

其中143为sid，7044为serial#，session 143会收到如下错误，session 138可以执行后续步骤.

ERROR:
ORA-03114: not connected to ORACLE