- 浏览: 697429 次
- 性别:
- 来自: 上海
文章分类
最新评论
-
yzs5273:
没什么用。都试过了
WIN7下CS不能全屏的解决方法 -
di1984HIT:
不错,学习了
读取本地计算机中的安装程序列表 -
ffedu:
[flash=200,200][url][img][list] ...
linux/unix中如何用find命令详解,非常详细的介绍,比man find强100倍(转) -
lintghi:
...
Log4j使用相对路径指定log文件及使用总结 -
nick.s.ni:
唉,Java中引用的包没有介绍啊,如果数据库用UTF-8的格式 ...
Oracle 中Java 对象与PL/SQL类型的映射及使用(转)
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/
发表评论
-
Mongo vs. Couchbase
2017-07-05 22:21 0https://www.couchbase.com/com ... -
Backup DDL on Oracle DB
2016-03-01 10:31 777set trimspool on lines 500 pa ... -
Identifying Relationship vs. Non-identifying Relationship
2015-10-20 23:49 1244Identifying vs non-identifying ... -
Re-post: DBMS_XPLAN : Display Oracle Execution Plans
2014-07-23 16:14 1039DBMS_XPLAN : Display Oracle Ex ... -
Optimistic Locking with Concurrency in Oracle
2014-04-16 11:37 678A very good document about opti ... -
SQL*PLUS - SET Statement
2012-10-24 19:38 930Syntax: SET option value ... -
Get DDL via SQLPLUS
2012-10-24 19:36 975set pagesize 0 -- The height of ... -
Oracle 子程序参数模式,IN,OUT,NOCOPY ( 转)
2012-03-06 14:54 1871Oracle 子程序参数模式主 ... -
TNSNAMES配置中空格导致的bug(转)
2011-12-29 21:55 1180发现一个很有趣的bug ... -
Oracle 中Java 对象与PL/SQL类型的映射及使用(转)
2011-11-05 23:45 4730一、Jpublisher概述 Oracle JPu ... -
Refresh statistics for all tables
2011-11-03 14:10 956select 'analyze table '||tab ... -
Oracle exp direct(Reposted)
2011-06-15 14:55 2186使用直接导出模式 direct=y exp 比传统模式导 ... -
九大Oracle性能优化基本方法详解(转)
2011-05-12 18:18 1080Oracle性能优化基本方法包括一下几个步骤,包括: 1)设 ... -
深入了解oracle的高水位(HWM)
2011-05-12 09:23 1151转自http://tolywang.itpub.net ... -
Oracle 聚簇(收集)
2011-05-11 22:02 19171. 什么是聚簇 聚簇是 ... -
怎样正确显示oracle异常行数(转)
2011-01-20 16:17 2611在oracle中的存储过程中,使用的EXCEPTION并用 ... -
Oracle function translate(Reposted)
2011-01-08 22:51 1659一、语法: TRANSLATE(string,f ... -
Oracle用Start with...Connect By子句递归查询(转)
2011-01-07 11:39 4253一、语法 大致写法:sel ... -
Spool和SQLLDR--Oracle数据导出导入(传)
2010-12-02 14:58 4494对于SPOOL数据的SQL,最好要自己定义格式,以方便程序直 ... -
TNS:listener could not find instance with matching protocol stack
2010-09-27 15:41 1412When the listener believes the ...
相关推荐
- **锁定分析**:`v$lock`视图可以显示锁定详细信息,帮助识别哪些会话持有锁,哪些会话正在等待锁。 - **会话监控**:`v$session`视图可以跟踪会话状态,找出长时间运行的事务或SQL语句。 - **资源争用**:在RAC中...
i upload the matlab code related to blocking probability in cellular systems
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 ...
在Windows操作系统下,Socket有两种工作模式:阻塞模式(Blocking Mode)和非阻塞模式(Non-blocking Mode)。本篇文章将主要探讨阻塞模式Socket在Windows环境下的工作原理、特点以及使用方法。 ### 1. 阻塞模式...
在现代网络编程中,非阻塞套接字(non-blocking socket)和多路复用(multiplexing)是处理高并发连接的关键技术之一。这些技术能够帮助服务器高效地管理多个客户端连接,避免因等待某个操作完成而浪费资源。本文将...
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 ...
3. `g_blocking_queue.h` 和 `g_blocking_queue_impl.h`:可能是阻塞队列的实现,用于对比或配合无锁队列使用。 4. `atomic_ops.h`:包含了原子操作的定义和实现,是实现无锁队列的基础。 在无锁队列的实现中,通常...
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/...
博主的博客Verilog之blocking & nonblocking assignments有些内容是参考了这篇英文文献的,其中对verilog中有关阻塞与非阻塞赋值语句的8种准则进行了详细的举例说明,读者可以下载文章进行详细阅读,以便更好地理解...
检查当前oracle数据库中的阻塞情况。
lock = MemcacheLock('my_lock', memcache_client) if lock.acquire(blocking=True): # 在这里执行需要加锁的操作 ... lock.release() ``` 这段代码展示了如何创建一个`MemcacheLock`实例,然后尝试获取锁。`...
分布式装配流水线(Distributed Assembly Blocking Flow Shop,简称DABFS)是一种在现代工业生产中广泛应用的优化问题。它涉及到多个工作站点,每个站点执行不同的装配任务,且工件需要按照特定顺序通过这些站点。...
标题中的"Blocking_Socket_File.zip_blocking socket"表明这个压缩包主要关注的是阻塞式套接字(Blocking Socket)的相关知识。在计算机网络编程中,套接字是进程间通信的一种方式,而阻塞式套接字是默认的工作模式...
python-redis-lock 多个redis客户端访问同一个redis服务端,控制并发。 github:...if lock.acquire(blocking=False): print(Got the lock.) lock.release() else: print(Some
提出了一种基于免疫的自适应异常检测算法SAIM,该算法通过对训练抗原的学习,形成最优的抗体对记忆细胞集进行进化和更新,通过记忆细胞集采用KNN方法投票进行异常检测。实验采用著名UCI机器学习数据库的Hepatitis...
《Fast Portable non-blocking network programming with Libevent》是一本关于Libevent库的书籍,它专注于指导读者如何使用Libevent 2.0或更高版本来编写快速、可移植且支持异步I/O的网络程序。本书的作者是Nick ...
标题“bp.rar_blocking probability_cell”和描述“blocking probability versus new cell rate”暗示了我们讨论的主题集中在无线通信领域,特别是关于小区阻塞概率与新建小区速率之间的关系。在移动通信网络中,...
- **实现方式**:使用不同类型的锁来控制对数据的访问,比如共享锁(S)和排他锁(X)。 - **持久性**:一旦事务完成,其效果是永久的,即使系统出现故障,事务的结果也不会丢失。 - **实现方式**:通过日志...