数据库lock锁阻塞查询测试报告

数据库lock锁阻塞查询测试报告

 

一、模拟产生锁阻塞的情况

会话一：yyj普通用户登录，创建表t，insert数据，提交，之后update该行，但不进行提交。

SQL> create table t (a varchar(10));

Table created.

 

SQL> select * from t;

no rows selected

 

SQL> insert into t values (1);

1 row created.

 

SQL> commit;

Commit complete.

 

SQL> select * from t;

A

----------

1

 

SQL> update t set a=2 where a=1;

1 row updated.

 

会话二：yyj普通用户登录，update表t相同行数据，命令发出产生等待

SQL> update t set a=3 where a=1;

 

二、 

作为数据库管理员，遇到等待事件这类问题，在不知道是由于锁导致的等待事件的情况下，通常的处理方法，先通过视图v$session_wait查询当前数据库存在哪些等待事件。

SQL> select sid,EVENT from v$session_wait where wait_class<>'Idle';

 

       SID EVENT

---------- ----------------------------------------------------------------

       153 enq: TX - row lock contention

sid为153的会话信息表明，当前数据库存在TX锁使153会话存在等待。

 

我们通过v$lock视图查询阻塞其它会话的sid以及该会话对锁的持有时间

SQL> select sid,type,ctime from v$lock where block=1;

 

       SID TY      CTIME

---------- -- ----------

       151 TX        783

Block字段为1表示该会话阻塞其它会话。为0表示不阻塞其它会话。

CTIME字段表示会话持有该锁的时间，单位为秒。

同样也可以通过v$lock查询得到被阻塞会话的等待时间。

SQL> select sid,type,ctime,request from v$lock where request<>0;

 

       SID TY      CTIME    REQUEST

---------- -- ---------- ----------

       153 TX        927          6

Request字段表示该会话需要某种锁资源，值为0，1，2，3，4，5，6。0表示none

CTIME在此处表示该会话请求相关锁资源的时间，即等待时间，单位为秒。

 

v$lock视图相关字段解释：

ADDR	RAW(4 | 8)	Address of lock state object
KADDR	RAW(4 | 8)	Address of lock
SID	NUMBER	Identifier for session holding or acquiring the lock
TYPE	VARCHAR2(2)	Type of user or system lock The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are: TM - DML enqueue TX - Transaction enqueue UL - User supplied
ID1	NUMBER	Lock identifier #1 (depends on type)
ID2	NUMBER	Lock identifier #2 (depends on type)
LMODE	NUMBER	Lock mode in which the session holds the lock: 0 - none 1 - null (NULL) 2 - row-S (SS) 3 - row-X (SX) 4 - share (S) 5 - S/Row-X (SSX) 6 - exclusive (X)
REQUEST	NUMBER	Lock mode in which the process requests the lock: 0 - none 1 - null (NULL) 2 - row-S (SS) 3 - row-X (SX) 4 - share (S) 5 - S/Row-X (SSX) 6 - exclusive (X)
CTIME	NUMBER	Time since current mode was granted
BLOCK	NUMBER	A value of either 0 or 1, depending on whether or not the lock in question is the blocker.

 

 --查询持有锁导致阻塞其它事务的会话
(select l.id1,l.id2,l.sid hold_sid,
decode(l.type,'RW','RW - Row Wait Enquence',
              'TM','TM - DML Enquence',
              'TX','TX - Trans Enquence',
              'UL','UL - User',l.type||'System') res,
         decode(l.lmode,1,'No Lock',
                        2,'Row Share',
                        3,'Row Exclusive',
                        4,'Share',
                        5,'Share Row Excl',
                        6,'Exclusive',null) lmode,
 l1.sid wait_sid,
 decode(l1.type,'RW','RW - Row Wait Enquence',
              'TM','TM - DML Enquence',
              'TX','TX - Trans Enquence',
              'UL','UL - User',l.type||'System') res,
 decode(l1.request,1,'No Lock',
                        2,'Row Share',
                        3,'Row Exclusive',
                        4,'Share',
                        5,'Share Row Excl',
                        6,'Exclusive',null) request,l1.ctime
 from v$lock l,v$lock l1
 where  l.type='TX'and l.id1=l1.id1 and l.id2=l1.id2
 and l.sid<>l1.sid
 and l1.block=0)

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24005010/viewspace-730680/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24005010/viewspace-730680/