- 浏览: 684905 次
- 性别:
- 来自: 上海
文章分类
最新评论
-
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 726set trimspool on lines 500 pa ... -
Identifying Relationship vs. Non-identifying Relationship
2015-10-20 23:49 1192Identifying vs non-identifying ... -
Re-post: DBMS_XPLAN : Display Oracle Execution Plans
2014-07-23 16:14 997DBMS_XPLAN : Display Oracle Ex ... -
Optimistic Locking with Concurrency in Oracle
2014-04-16 11:37 618A very good document about opti ... -
SQL*PLUS - SET Statement
2012-10-24 19:38 903Syntax: SET option value ... -
Get DDL via SQLPLUS
2012-10-24 19:36 944set pagesize 0 -- The height of ... -
Oracle 子程序参数模式,IN,OUT,NOCOPY ( 转)
2012-03-06 14:54 1814Oracle 子程序参数模式主 ... -
TNSNAMES配置中空格导致的bug(转)
2011-12-29 21:55 1094发现一个很有趣的bug ... -
Oracle 中Java 对象与PL/SQL类型的映射及使用(转)
2011-11-05 23:45 4674一、Jpublisher概述 Oracle JPu ... -
Refresh statistics for all tables
2011-11-03 14:10 869select 'analyze table '||tab ... -
Oracle exp direct(Reposted)
2011-06-15 14:55 2142使用直接导出模式 direct=y exp 比传统模式导 ... -
九大Oracle性能优化基本方法详解(转)
2011-05-12 18:18 1043Oracle性能优化基本方法包括一下几个步骤,包括: 1)设 ... -
深入了解oracle的高水位(HWM)
2011-05-12 09:23 1123转自http://tolywang.itpub.net ... -
Oracle 聚簇(收集)
2011-05-11 22:02 18571. 什么是聚簇 聚簇是 ... -
怎样正确显示oracle异常行数(转)
2011-01-20 16:17 2581在oracle中的存储过程中,使用的EXCEPTION并用 ... -
Oracle function translate(Reposted)
2011-01-08 22:51 1598一、语法: TRANSLATE(string,f ... -
Oracle用Start with...Connect By子句递归查询(转)
2011-01-07 11:39 4177一、语法 大致写法:sel ... -
Spool和SQLLDR--Oracle数据导出导入(传)
2010-12-02 14:58 4439对于SPOOL数据的SQL,最好要自己定义格式,以方便程序直 ... -
TNS:listener could not find instance with matching protocol stack
2010-09-27 15:41 1377When the listener believes the ...
相关推荐
oracle RAC查看blocking的脚本
using select to implement socket multiplexing, and non-blocking, asynchronous IO
i upload the matlab code related to blocking probability in cellular systems
blocking mode socket(阻塞模式socket-windows) 详细介绍请在csdn搜索“blocking mode socket”,是对此代码的介绍。
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 ...
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 ...
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数据库中的阻塞情况。
blocking the socket files with samples
Exact Analysis of Blocking Probability of Multi-class OBS with General Burst Size Distribution
Blocking Confirmation.pdf
The DATA.zip is the run result of SLHH. we provide the original data for readers.
python-redis-lock 多个redis客户端访问同一个redis服务端,控制并发。 github:...if lock.acquire(blocking=False): print(Got the lock.) lock.release() else: print(Some
blocking probability versus new cell rate
提出了一种基于免疫的自适应异常检测算法SAIM,该算法通过对训练抗原的学习,形成最优的抗体对记忆细胞集进行进化和更新,通过记忆细胞集采用KNN方法投票进行异常检测。实验采用著名UCI机器学习数据库的Hepatitis...
spring MVC 初始启动concurrent blocking queue,通过@PostConstruct 注解实现,详情参考我的博客
SQL SERVER 的阻塞和死锁,讲解的比较详细,可以参考看看
Application Technologies of Reverse Blocking IGBT