What's blocking my lock? 转载

原文地址 ：http://www.orafaq.com/node/854

Create a blocking lock

To begin, create a situation where one user isactively blocking another. Open two sessions. Issue the following commands inSession 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 the whole table, still inSession 1:

SQL>select * from tstlock for update ;

And in Session 2, try to update a row:

SQL> update tstlock set bar=

  2  'a' where bar='a' ;

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

Identify the blocking session

Oracle provides a view, DBA_BLOCKERS, which liststhe SIDs of all blocking sessions. But this view is often, in my experience, agood bit slower than simply querying V$LOCK, and it doesn't offer anyinformation beyond the SIDs of any sessions that are blocking other sessions.The V$LOCK view is faster to query, makes it easy to identify the blockingsession, 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. If a session holds a lockthat's blocking another session, BLOCK=1. Further, you can tell which sessionis being blocked by comparing the values in ID1 and ID2. The blocked sessionwill have the same values in ID1 and ID2 as the blocking session, and, since itis requesting a lock it's unable to get, it will have REQUEST > 0.

In the query above, we can see that SID 422 isblocking SID 479. SID 422 corresponds to Session 1 in our example, and SID 479 is our blocked Session 2.

To avoid having to stare at the table andcross-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 throw a little v$session intothe 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 more information in the v$locktable, but in order to read that information, we need to understand a bit moreabout lock types and the cryptically-named ID1 and ID2 columns.

Lock type and the ID1 / ID2columns

In this case, we already know that the blockinglock is an exclusive DML lock, since we're the ones who issued the lockingstatement. But most of the time, you won't be so lucky. Fortunately, you can readthis information from the v$lock table with little effort.

The first place to look is the TYPE column. Thereare dozens of lock types, but the vast majority are system types. System locksare normally only held for a very brief amount of time, and it's not generallyhelpful to try to tune your library cache, undo logs, etc. by looking inv$lock! (See the V$LOCK chapter in the Oracle Database Reference for a list ofsystem lock types.)

There are only three types of user locks, TX, TMand UL. UL is a user-defined lock -- a lock defined with the DBMS_LOCK package.The TX lock is a row transaction lock; it's acquired once for every transactionthat changes data, no matter how many objects you change in that transaction.The ID1 and ID2 columns point to the rollback segment and transaction tableentries for that transaction.

The TM lock is a DML lock. It's acquired once foreach object that's being changed. The ID1 column identifies the object beingmodified.

Lock Modes

You can see more information on TM and TX locksjust by looking at the lock modes. The LMODE and REQUEST columns both use thesame numbering for lock modes, in order of increasing exclusivity: from 0 forno lock, to 6 for exclusive lock. A session must obtain an exclusive TX lock inorder to change data; LMODE will be 6. If it can't obtain an exclusive lockbecause some of the rows it wants to change are locked by another session, thenit will request a TX in exclusive mode; LMODE will be 0 since it does not havethe lock, and REQUEST will be 6. You can see this interaction in the rows weselected 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 in Session 2, which is requesting the TX lock (LMODE=0,REQUEST=6), point back to the rollback and transaction entries for Session 1.That's what lets us determine the blocking session for Session 2.

You may also see TX locks in mode 4, Shared mode.If a block containing rows to be changed doesn't have any interestedtransaction list (ITL) entries left, then the session acquires a TX lock inmode 4 while waiting for an ITL entry. If you see contention for TX-4 locks onan object, you probably need to increase INITRANS for the object.

TM locks are generally requested and acquired inmodes 3, aka Shared-Row Exclusive, and 6. DDL requires a TM Exclusive lock.(Note that CREATE TABLE doesn't require a TM lock -- it doesn't need to lockany objects, because the object in question doesn't exist yet!) DML requires aShared-Row Exclusive lock. So, in the rows we selected earlier from v$lock, youcan see from the TM locking levels that these are 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 that each TM row points to alocked 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 knowing the object is enoughinformation; but we can dig even deeper. We can identify not just the object,but the block and even the row in the block that Session 2 is waiting on.

Identifying the locked row

We can get this information from v$session bylooking at the v$session entry for the blocked session:

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 object ID, the relative filenumber, the block in the datafile, and the row in the block that the session iswaiting on. If that list of data sounds familiar, it's because those are thefour components of an extended ROWID. We can build the row's actual extendedROWID from these components using the DBMS_ROWID package. The ROWID_CREATEfunction 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 lets us inspect the rowdirectly.

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

FOO BAR

--- ---

1   a

Conclusion

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

About the author

Natalka Roshak is a senior Oracle and Sybasedatabase administrator, analyst, and architect. She is based in Kingston, Ontario, andconsults across North America. More of herscripts and tips can be found in her online DBA toolkit at http://toolkit.rdbms-insight.com/ .