`

What's blocking my lock?(Reposted)

 
阅读更多

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

 

Create a blocking lock

 

To begin, create a situation where one user is actively blocking another. Open two sessions. Issue the 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 the whole table, still in Session 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 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 is often, in my experience, a good bit slower than simply querying V$LOCK, and it doesn't offer any information 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 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. If a session holds a lock that's blocking another session, BLOCK=1. Further, you can 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 blocking session, and, since it is requesting a lock it's unable to get, it will have REQUEST > 0.

In the query above, we can see that SID 422 is blocking 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 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 throw a 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 more information in the v$lock table, but in order to read that information, we need to understand a bit more about lock types and the cryptically-named ID1 and ID2 columns.

 

Lock type and the ID1 / ID2 columns

 

In this case, we already know that the blocking lock is an exclusive DML lock, since we're the ones who issued 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 little effort.

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

There are only three types of user locks, TX, TM and 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 transaction that changes data, no matter how many objects you change in that transaction. The ID1 and ID2 columns point to the rollback segment and transaction table entries for that transaction.

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

 

Lock Modes

 

You can see more information on TM and TX locks just by looking at the lock modes. The LMODE and REQUEST columns both use the same numbering for lock modes, in order of increasing exclusivity: from 0 for no lock, to 6 for exclusive lock. A session must obtain an exclusive TX lock in order to change data; LMODE will be 6. If it can't obtain an exclusive lock because some of the rows it wants to change are 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 can see 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 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 interested transaction list (ITL) entries left, then the session acquires a TX lock in mode 4 while waiting for an ITL entry. If you see contention for TX-4 locks on an object, you probably need to increase INITRANS for the object.

TM locks are generally requested and acquired in modes 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 lock any objects, because the object in question doesn't exist yet!) DML requires a Shared-Row Exclusive lock. So, in the rows we selected earlier from v$lock, you can 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 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 knowing the object is enough information; 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 by looking 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 file number, the block in the datafile, and the row in the block 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 build the 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 lets us inspect the row directly.

 

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, I hope, learned a bit about v$lock in the process.

 

About the author

 

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

分享到:
评论

相关推荐

    oracle RAC查看blocking的脚本

    - **锁定分析**:`v$lock`视图可以显示锁定详细信息,帮助识别哪些会话持有锁,哪些会话正在等待锁。 - **会话监控**:`v$session`视图可以跟踪会话状态,找出长时间运行的事务或SQL语句。 - **资源争用**:在RAC中...

    blocking.rar_blocking _blocking probability

    i upload the matlab code related to blocking probability in cellular systems

    cisco生成树端口blocking处理

    cisco设备和其他厂商设备连接,端口被blocking,端口状态是BKN yhsb_dx#show spanning-tree vlan 3 VLAN0003 Spanning tree enabled protocol ieee Root ID Priority 32768 Address 0008.a324.4902 Cost 4 ...

    blocking mode socket(阻塞模式socket-windows)

    在Windows操作系统下,Socket有两种工作模式:阻塞模式(Blocking Mode)和非阻塞模式(Non-blocking Mode)。本篇文章将主要探讨阻塞模式Socket在Windows环境下的工作原理、特点以及使用方法。 ### 1. 阻塞模式...

    non-blocking socket

    在现代网络编程中,非阻塞套接字(non-blocking socket)和多路复用(multiplexing)是处理高并发连接的关键技术之一。这些技术能够帮助服务器高效地管理多个客户端连接,避免因等待某个操作完成而浪费资源。本文将...

    微软内部资料-SQL性能优化3

    An intent lock indicates that SQL Server wants to acquire a shared (S) lock or exclusive (X) lock on some of the resources lower down in the hierarchy. For example, a shared intent lock placed at the ...

    lock-free circular array queue.

    3. `g_blocking_queue.h` 和 `g_blocking_queue_impl.h`:可能是阻塞队列的实现,用于对比或配合无锁队列使用。 4. `atomic_ops.h`:包含了原子操作的定义和实现,是实现无锁队列的基础。 在无锁队列的实现中,通常...

    Java Network Programming 3rd Edition By Elliotte Rusty Harold 2004

    There's More! Chapter 2. Basic Network Concepts Section 2.1. Networks Section 2.2. The Layers of a Network Section 2.3. IP, TCP, and UDP Section 2.4. The Internet Section 2.5. The Client/...

    Nonblocking and blocking Assignments

    博主的博客Verilog之blocking & nonblocking assignments有些内容是参考了这篇英文文献的,其中对verilog中有关阻塞与非阻塞赋值语句的8种准则进行了详细的举例说明,读者可以下载文章进行详细阅读,以便更好地理解...

    oracle blocking check script

    检查当前oracle数据库中的阻塞情况。

    PyPI 官网下载 | memcache_lock-0.0.3.tar.gz

    lock = MemcacheLock('my_lock', memcache_client) if lock.acquire(blocking=True): # 在这里执行需要加锁的操作 ... lock.release() ``` 这段代码展示了如何创建一个`MemcacheLock`实例,然后尝试获取锁。`...

    Distributed Assembly Blocking Flow Shop

    分布式装配流水线(Distributed Assembly Blocking Flow Shop,简称DABFS)是一种在现代工业生产中广泛应用的优化问题。它涉及到多个工作站点,每个站点执行不同的装配任务,且工件需要按照特定顺序通过这些站点。...

    Blocking_Socket_File.zip_blocking socket

    标题中的"Blocking_Socket_File.zip_blocking socket"表明这个压缩包主要关注的是阻塞式套接字(Blocking Socket)的相关知识。在计算机网络编程中,套接字是进程间通信的一种方式,而阻塞式套接字是默认的工作模式...

    Redis分布式锁python-redis-lock使用方法

    python-redis-lock 多个redis客户端访问同一个redis服务端,控制并发。 github:...if lock.acquire(blocking=False): print(Got the lock.) lock.release() else: print(Some

    论文研究-改进的路由协议BLOCKING-COMPOW.pdf

    提出了一种基于免疫的自适应异常检测算法SAIM,该算法通过对训练抗原的学习,形成最优的抗体对记忆细胞集进行进化和更新,通过记忆细胞集采用KNN方法投票进行异常检测。实验采用著名UCI机器学习数据库的Hepatitis...

    Fast Portable non-blocking network programming with Libevent

    《Fast Portable non-blocking network programming with Libevent》是一本关于Libevent库的书籍,它专注于指导读者如何使用Libevent 2.0或更高版本来编写快速、可移植且支持异步I/O的网络程序。本书的作者是Nick ...

    bp.rar_blocking probability_cell

    标题“bp.rar_blocking probability_cell”和描述“blocking probability versus new cell rate”暗示了我们讨论的主题集中在无线通信领域,特别是关于小区阻塞概率与新建小区速率之间的关系。在移动通信网络中,...

    SQL Server Blocking and Deadlock

    - **实现方式**:使用不同类型的锁来控制对数据的访问,比如共享锁(S)和排他锁(X)。 - **持久性**:一旦事务完成,其效果是永久的,即使系统出现故障,事务的结果也不会丢失。 - **实现方式**:通过日志...

Global site tag (gtag.js) - Google Analytics