数据库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视图相关字段解释：

 --查询持有锁导致阻塞其它事务的会话
(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)