What's blocking my lock?

如果你曾经接过用户的电话，说数据库事务无法进行，或者开发者不能理解为什么她的应用session 被block住。如果能不仅辨别出谁加的锁，还能知道什么对象被锁住，该多棒。甚至，你还可以准确地知道一个session在等待那一行的锁。

 

创建blocking lock

 

开始，先建立一个situation，用户主动锁住另外一个用户。

打开两个session。 Session1中，用下面的命令创建test 表，

SQL> create table tstlock (foo varchar2(1), bar varchar2(1));

Table created.

SQL> insert into tstlock values (1,'a');

1 row created.

SQL> insert into tstlock values (2, 'b');

 

1 row created.

 

SQL> select * from tstlock ;

 

FOO BAR

--- ---

1   a

2   b

 

2 rows selected.

 

SQL> commit ;

 

Commit complete.

 

现在对该表(test )加一个锁，仍然是session1中。

 

SQL> select *from tstlock for update ;

然后，Session2 中，试着update 一行：

SQL> update tstlock set bar= 2  'a' where bar='a' ;

 

这个statement 会被Session1中的锁block ，因为那个锁是针对整个表的。

 

 

识别阻塞session

Oracle 提供视图，DBA_BLOCKERS，这是一个所有阻塞session的SID列表。

但是以我的经验，这个视图比简单查询V$LOCK要慢得多，除了阻塞其他session的SID，他没有提供任何别的信息。V$LOCK视图查询快，就可以简单的辨别阻塞session以及更多信息。

 

SQL> select * from v$lock ;

 

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK

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

AF9E2C4C AF9E2C60        479 TX     131078      16739          0          6        685          0

ADDF7EC8 ADDF7EE0        422 TM      88519          0          3          0        697          0

ADDF7F74 ADDF7F8C        479 TM      88519          0          3          0        685          0

ADEBEA20 ADEBEB3C        422 TX     131078      16739          6          0        697          1

....     ....            ... ...      ....       ....       ....       ....        ....      ....

 

注意“BLOCK”列。如果一个session拥有一个阻塞其他session的锁，BLOCK=1。而且你可以通过比较ID1和ID2的值，区别哪一个session被block。

被阻塞的session和阻塞它的session有相同的ID1 和ID2值，请求锁的session因为无法得到锁，因为它REQUEST>0。

 

通过上面的查询语句，我们可以看到SID 422 正阻塞SID479。SID 422 就是上面的例子Session1中的产生的，SID 479 就是Session2被阻塞的。

 

为了避免你得死盯着他table，以及交叉比较ID1和ID2的值，我们用下面的query ：

SQL> select l1.sid, ' IS BLOCKING ', l2.sid

  2  from v$lock l1, v$lock l2

  3  where l1.block =1 and l2.request > 0

  4  and l1.id1=l2.id1

  5  and l1.id2=l2.id2

SQL> /

 

       SID 'ISBLOCKING'         SID

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

       422  IS BLOCKING         479

 

1 row selected.

甚至，如果我们同时用到v$session的话，结果会更有可读性。

SQL> select s1.username || '@' || s1.machine

  2  || ' ( SID=' || s1.sid || ' )  is blocking '

  3  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status

  4  from v$lock l1, v$session s1, v$lock l2, v$session s2

  5  where s1.sid=l1.sid and s2.sid=l2.sid

  6  and l1.BLOCK=1 and l2.request > 0

  7  and l1.id1 = l2.id1

  8  and l2.id2 = l2.id2 ;

 

BLOCKING_STATUS

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

BULKLOAD@yttrium ( SID=422 )  is blocking BULKLOAD@yttrium ( SID=479 )

 

1 row selected.

 

在v$lock中还有更多信息，但是为了阅读，关于lock type和ID1，ID2列我们需要理解多一点儿。

 

锁类型和ID1/ID2 列

这个例子中，我已经知道blocking 锁是DML 锁，因为是我们执行的那个statement。但是大多数时候，你不会这么幸运。有幸的是，你可以轻易的从v$lock表读取这些信息。

 

首先看TYPE 列。有十几种锁类型，但是绝大多数是系统类型。通过看v$lock,系统锁，通常只是持有较短的时间，而且一般来说对调整你的内存，undo log等帮助不大。（关于系统所类型，请看Oracle Database Reference 的V$LOCK 一章）

 

只有3种类型的用户锁，TX,TM,和UL.UL 是用户定义的锁—和DBMS_LOCK包一起定义的。TX 锁是行事务锁。对每一个改变数据的事务都要有这种锁，不管该事务中改变多少对象。ID1,ID2列指出该事务的rollback 段和transaction table entries.

 

TM锁是DML锁。每个对象改变的时候需要该锁。ID1列就是正在修改的那个对象。

 

锁模式

要知道更多关于TM /TX 请看锁模式。LMODE /REQUEST列为了加强排他性用一样编号方式：从0 (no lock) to 6(排他锁)。一个session 如果要改变数据一定要得到排他锁（TX lock），LMODE就是6。如果因为它要改变的行被其他的session锁住而得不到排他锁，他就是要请求TX 锁模式；这时LMODE=0，因为它没有得到任何锁，同时REQUEST=6。从前面执行的select 语句(from v$lock)，你可以看到这些。

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK

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

AF9E2C4C AF9E2C60        479 TX     131078      16739          0          6        685          0

ADEBEA20 ADEBEB3C        422 TX     131078      16739          6          0        697          1

 

 

注意，Session2中正在请求TX锁（LMODE=0, REQUEST=6），它的ID1 ,ID2指向session1 的rollback 和transaction entries。这就是为什么说session1 阻塞了session2。

(未完)

 

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

原文（From http://www.orafaq.com/node/854）

 

 

What's blocking my lock?

Submitted by Natalka Roshak on Sat, 2006-04-01 18:00

If you've ever gotten aphone call from an annoyed user whose transaction just won't go through, orfrom a developer who can't understand why her application sessions are blockingeach other, you know how useful it can be to identify not just whose lock isdoing the blocking, but what object is locked. Even better, you can identifythe exact row that a session is waiting to lock.

Create a blocking lock

To begin, create asituation where one user is actively blocking another. Open two sessions. Issuethe following commands in Session 1 to build the test table:

SQL> create table tstlock (foo varchar2(1), bar varchar2(1));

 

Table created.

 

SQL> insert into tstlock values (1,'a');

 

1 row created.

 

SQL> insert into tstlock values (2, 'b');

 

1 row created.

 

SQL> select * from tstlock ;

 

FOO BAR

--- ---

1   a

2   b

 

2 rows selected.

 

SQL> commit ;

 

Commit complete.

Now grab a lock on thewhole table, still in Session 1:

SQL> select *from tstlock for update ;

And in Session 2, try toupdate a row:

SQL> update tstlock set bar=

  2  'a' where bar='a' ;

This statement will hang,blocked by the lock that Session 1 is holding on the entire table.

Identify the blocking session

Oracle provides a view,DBA_BLOCKERS, which lists the SIDs of all blocking sessions. But this view isoften, in my experience, a good bit slower than simply querying V$LOCK, and itdoesn't offer any information beyond the SIDs of any sessions that are blockingother sessions. The V$LOCK view is faster to query, makes it easy to identifythe blocking session, and has a lot more information.

SQL> select * from v$lock ;

 

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK

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

AF9E2C4C AF9E2C60        479 TX     131078      16739          0          6        685          0

ADDF7EC8 ADDF7EE0        422 TM      88519          0          3          0        697          0

ADDF7F74 ADDF7F8C        479 TM      88519          0          3          0        685          0

ADEBEA20 ADEBEB3C        422 TX     131078      16739          6          0        697          1

....     ....            ... ...      ....       ....       ....       ....        ....      ....

Note the BLOCK column. Ifa session holds a lock that's blocking another session, BLOCK=1. Further, youcan tell which session is being blocked by comparing the values in ID1 and ID2.The blocked session will have the same values in ID1 and ID2 as the blockingsession, and, since it is requesting a lock it's unable to get, it will haveREQUEST > 0.

In the query above, wecan see that SID 422 is blocking SID 479. SID 422 corresponds to Session 1 inour example, and SID 479 is our blocked Session 2.

To avoid having to stareat the table and cross-compare ID1's and ID2's, put this in a query:

SQL> select l1.sid, ' IS BLOCKING ', l2.sid

  2  from v$lock l1, v$lock l2

  3  where l1.block =1 and l2.request > 0

  4  and l1.id1=l2.id1

  5  and l1.id2=l2.id2

SQL> /

 

       SID 'ISBLOCKING'         SID

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

       422  IS BLOCKING         479

 

1 row selected.

Even better, if we throwa little v$session into the mix, the results are highly readable:

SQL> select s1.username || '@' || s1.machine

  2  || ' ( SID=' || s1.sid || ' )  is blocking '

  3  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status

  4  from v$lock l1, v$session s1, v$lock l2, v$session s2

  5  where s1.sid=l1.sid and s2.sid=l2.sid

  6  and l1.BLOCK=1 and l2.request > 0

  7  and l1.id1 = l2.id1

  8  and l2.id2 = l2.id2 ;

 

 

BLOCKING_STATUS

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

BULKLOAD@yttrium ( SID=422 )  is blocking BULKLOAD@yttrium ( SID=479 )

 

1 row selected.

There's still moreinformation in the v$lock table, but in order to read that information, we needto understand a bit more about lock types and the cryptically-named ID1 and ID2columns.

Lock type and the ID1 / ID2 columns

In this case, we alreadyknow that the blocking lock is an exclusive DML lock, since we're the ones whoissued the locking statement. But most of the time, you won't be so lucky.Fortunately, you can read this information from the v$lock table with littleeffort.

The first place to lookis the TYPE column. There are dozens of lock types, but the vast majority aresystem types. System locks are normally only held for a very brief amount oftime, and it's not generally helpful to try to tune your library cache, undologs, etc. by looking in v$lock! (See the V$LOCK chapter in the Oracle DatabaseReference for a list of system lock types.)

There are only threetypes of user locks, TX, TM and UL. UL is a user-defined lock -- a lock definedwith the DBMS_LOCK package. The TX lock is a row transaction lock; it'sacquired once for every transaction that changes data, no matter how manyobjects you change in that transaction. The ID1 and ID2 columns point to therollback segment and transaction table entries for that transaction.

The TM lock is a DMLlock. It's acquired once for each object that's being changed. The ID1 columnidentifies the object being modified.

LockModes

You can see moreinformation on TM and TX locks just by looking at the lock modes. The LMODE andREQUEST columns both use the same numbering for lock modes, in order ofincreasing exclusivity: from 0 for no lock, to 6 for exclusive lock. A sessionmust obtain an exclusive TX lock in order to change data; LMODE will be 6. Ifit can't obtain an exclusive lock because some of the rows it wants to changeare locked by another session, then it will request a TX in exclusive mode;LMODE will be 0 since it does not have the lock, and REQUEST will be 6. You cansee this interaction in the rows we selected earlier from v$lock:

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK

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

AF9E2C4C AF9E2C60        479 TX     131078      16739          0          6        685          0

ADEBEA20 ADEBEB3C        422 TX     131078      16739          6          0        697          1

Note that ID1 and ID2 inSession 2, which is requesting the TX lock (LMODE=0, REQUEST=6), point back tothe rollback and transaction entries for Session 1. That's what lets usdetermine the blocking session for Session 2.

You may also see TX locksin mode 4, Shared mode. If a block containing rows to be changed doesn't haveany interested transaction list (ITL) entries left, then the session acquires aTX lock in mode 4 while waiting for an ITL entry. If you see contention forTX-4 locks on an object, you probably need to increase INITRANS for the object.

TM locks are generallyrequested and acquired in modes 3, aka Shared-Row Exclusive, and 6. DDLrequires a TM Exclusive lock. (Note that CREATE TABLE doesn't require a TM lock-- it doesn't need to lock any objects, because the object in question doesn'texist yet!) DML requires a Shared-Row Exclusive lock. So, in the rows weselected earlier from v$lock, you can see from the TM locking levels that theseare DML locks:

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK

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

ADDF7EC8 ADDF7EE0        422 TM      88519          0          3          0        697          0

ADDF7F74 ADDF7F8C        479 TM      88519          0          3          0        685          0

Identifying the locked object

Now that we know thateach TM row points to a locked object, we can use ID1 to identify the object.

SQL> select object_name from dba_objects where object_id=88519 ;

 

OBJECT_NAME

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

TSTLOCK

Sometimes just knowingthe object is enough information; but we can dig even deeper. We can identifynot just the object, but the block and even the row in the block that Session 2is waiting on.

Identifying the locked row

We can get thisinformation from v$session by looking at the v$session entry for the blockedsession:

SQL> select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#

  2* from v$session where sid=479 ;

 

ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#

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

        88519             16          171309             0

This gives us the objectID, the relative file number, the block in the datafile, and the row in theblock that the session is waiting on. If that list of data sounds familiar,it's because those are the four components of an extended ROWID. We can buildthe row's actual extended ROWID from these components using the DBMS_ROWID package.The ROWID_CREATE function takes these arguments and returns the ROWID:

SQL> select do.object_name,

  2  row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,

  3  dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )

  4  from v$session s, dba_objects do

  5  where sid=543

  6  and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

 

OBJECT_NAME     ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C

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

TSTLOCK                 88519             16          171309             0 AAAVnHAAQAAAp0tAAA

And, of course, this letsus inspect the row directly.

SQL> select * from tstlock where rowid='AAAVnHAAQAAAp0tAAA' ;

 

FOO BAR

--- ---

1   a

Conclusion

We've seen how toidentify a blocking session, and how to inspect the very row that the waitingsession is waiting for. And, I hope, learned a bit about v$lock in the process.

Aboutthe author

Natalka Roshak is asenior Oracle and Sybase database administrator, analyst, and architect. She isbased in Kingston, Ontario, and consults across North America. More of her scripts and tips canbe found in her online DBA toolkit at http://toolkit.rdbms-insight.com/.