- 浏览: 277442 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
somefuture:
为何有文章说asyncAppdender不支持J2ee容器ht ...
log4j 日志异步化AsyncAppender 大幅提升系统性能 -
model2012:
你用adt跑的吧,android里面是不能直接运行main方法 ...
JVM崩溃:Current thread JavaThread "CompilerThread1" daemon [_thre -
di1984HIT:
不错。学习了啊
.rmi.ConnectException: Connection refused to host: 127.0.0.1来龙去脉 -
gygwoaini:
还有可能是第3个问号没给值
索引中丢失 IN 或 OUT 参数:: 3 -
fool2011:
多谢
索引中丢失 IN 或 OUT 参数:: 3
http://www.oratechinfo.co.uk/deadlocks.html
Deadlocks
- What are deadlocks?
- The "classic" deadlock
- Deadlocks with Autonomous Transactions
- Deadlocks with Bitmap Indexes
- Deadlocks with unindexed foreign keys
- Detecting and identifying deadlocks
- Further information
What are deadlocks?
A deadlock is the situation where you have two, or more, Oracle "sessions" (well, transactional "states") competing for mutually locked resources. Oracle deals with deadlocks pretty much immediately by raising an exception (ORA-00060) in one of the sessions.There are a few reasons why your application may experience deadlocks, most of which are about application design. However, there are a few situations when, due to certain architectural design decisions, you may experience deadlocks simply due to the internal mechanisms of Oracle itself.
The "classic" deadlock
If session 1 is locking row 1, session 2 locks row 2, then session 1 attempts to lock row 2 (which will block since session 2 has the lock on that row), and then session 2 attempts to lock row 1 (which will block since session 1 has the lock on that row), then session 1 is waiting for session 2, and session 2 is waiting on session 1, which of course will never be resolved.Here's an actual Oracle example. First of all, setup the environment :
SQL> CREATE TABLE t ( a varchar2(10) ); Table created. SQL> insert into t values ( 'x' ); 1 row created. SQL> insert into t values ( 'y' ); 1 row created. SQL> commit; Commit complete.Now, session 1 attempts to delete row 'x', but doesn't commit :
SESS1> delete from t where a = 'x'; 1 row deleted.Okay, now session 2 attempts to delete row 'y', again, not committing :
SESS2> delete from t where a = 'y'; 1 row deleted.Now, since session 2 has not committed the delete against row 'y', session 1 can still "see" it (and attempt to delete it) :
SESS1> delete from t where a = 'y';However, this will be blocked by session 2 (and hence wait), since row 'y' is locked, so session 1 is holding the lock on row 'x' but is waiting for row 'y'. Now, session 2 attempts to do it the "other way around", i.e. attempting to delete row 'x', while it retains the lock on row 'y' :
SESS2> delete from t where a = 'x';this will be blocked by session 1 (and hence wait). So, the situation is tied, session 2 is waiting for session 1 and session 1 is waiting for session 2.
So, in reality, what happens in this situation? Well, obviously deadlocks just simply can't be allowed to occur, it's basically an infinite loop, the situation can't be resolved. So, Oracle steps in, automatically detecting the deadlock and resolving it by causing one of the sessions (can't actually guarantee which one) to receive an ORA-00060 error, i.e.
ORA-00060: deadlock detected while waiting for resource
What happens to both sessions?
So, we've seen what happens in terms of the deadlock, one of the sessions will error with the ORA-00060 error, but what about the other one? Well, this may be surprising to some people, but it carries on waiting, because the ORA-00060 error in the other session does not commit or rollback, and hence it retains it's previous lock(s), including those involved in the deadlock scenario. It is the responsibility of the client to commit or rollback if an ORA-00060 error is encountered.So, what's the fix?
As with all deadlock scenarios, it's about avoidance rather than remedy. For the classic deadlock situation, you have to ensure that sessions do their operations in the same "order", i.e. if a process updates table t1 then t2, that any other process does so in the same order.Deadlocks with Autonomous Transactions
As mentioned in the previous section, all deadlocks are variations on the same theme, i.e. multiple "sessions" competing for mutually locked resources. Since an Autonomous Transaction (AT) is basically a "session within a session", it's perfectly possible for it to occur here as well. This is maybe a little less obvious, since some people think that somehow they're part of the same "parent" session, but this is not the case. Note, however, that a deadlock is slightly more subtle when using ATs :Showing this is trivial. First, create an AT process :
SQL> CREATE OR REPLACE PROCEDURE p_at 2 AS 3 PRAGMA AUTONOMOUS_TRANSACTION; 4 BEGIN 5 DELETE FROM t; 6 COMMIT; 7 END p_at; 8 / Procedure created.Now, put a single row into a table :
SQL> insert into t values ( 'x' ); 1 row created. SQL> commit; Commit complete.Now, delete the row in the "parent" session :
SQL> delete from t ; 1 row deleted.Now, since the "parent" session is locking the row in "t", any attempt to delete it via an AT from within the "parent" session, will result in ORA-00060, i.e.
SQL> exec p_at; BEGIN p_at; END; * ERROR at line 1: ORA-00060: deadlock detected while waiting for resource ORA-06512: at "MARTIN.P_AT", line 5 ORA-06512: at line 1So, why does the AT simply not block on the 'x' row? Well, if it did, imagine the situation, "parent" session is waiting for the AT to "finish" (like any other PL/SQL call), but it never will since it's blocked waiting for it's parent session to commit or rollback.
Deadlocks with Bitmap Indexes
Bitmap indexes were designed to be used solely within data warehouses, i.e. where the vast majority of the database activity is reading data, and there's very little (or no) data modification, except for batch processes which occasionally re-populate the warehouse. Each "row" in the bitmap index contains references to potentially many different rowids, in contrast to a B*-tree index which references a single rowid. It should be obvious, therefore, that, since the transactional mechanism is the same for all database operations, that any DML on a table which impacts the bitmap index may end up locking (or attempting to lock) many different "rows" within the index. This is the key concept with deadlocks in bitmap indexes, you're not being deadlocked on the underlying table, but on the index blocks.The ability of an INSERT statement to deadlock (which is usually impossible) is therefore explained in that the bitmap index is maintaining other rows as a result of the DML.
Fortunately, it's easy to detect these kind of deadlocks, because the trace file has a very particular format :
Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TX-00080027-0000d2a1 12 37 X 15 35 S TX-000a0016-0000d6d2 15 35 X 12 37 S session 37: DID 0001-000C-00000002 session 35: DID 0001-000F-00000002 session 35: DID 0001-000F-00000002 session 37: DID 0001-000C-00000002 Rows waited on: Session 35: no row Session 37: no rowNote, the "no row" entries. This is the prime indicator that this is a bitmap index deadlock. There's very little you can do about this, apart from not use bitmap indexes in a DML environment.
Deadlocks with unindexed foreign keys
There are various attributeswhich can be specified when creating foreign key constraints. The ones of interest are UPDATE and DELETE CASCADE. These attributes define what happens to the child record(s) when various actions are performed on the parent record.The upshot of the deadlock problem is that in these situations (i.e. using the UPDATE or DELETE CASCADE options of the FK constraint), without an index on the foreign key column(s), Oracle has no option but to attempt to get a table lock on the child table while it performs it's maintenance. In this situation, it's obvious that the potential for deadlocking is vastly increased for having to lock the whole child table.
Fortunately, resolving this is quite straightforward, you simply put an index on the foreign key column(s).
So, is there any way of being proactive about this, and identifying the table(s) / column(s) which are the subject of a foreign key constraint, but do not have indexes? Yes, Tom Kyte has written one and it's located here.
For more information about this problem (and other performance related issues regarding unindexed FKs), see http://asktom.oracle.com/~tkyte/unindex/index.html.
Detecting and identifying deadlocks
Well, the obvious way of detecting a deadlock is when you see the ORA-00060 error, however, sometimes ( especially with complex applications ), it may not be obvious what is actually causing the deadlock itself. Fortunately, whenever a deadlock occurs, Oracle creates a trace file (in user_dump_dest), with information which will allow you to see details about the deadlock, what the shared resource is, what the current SQL statement was, etc. etc.Here's an extract from the trace file generated on a 10g Express Edition (XE) from the above first deadlock situation above. Note, the full trace file contains a lot more information for the developer / DBA to utilise to determine the exact cause of the situation which caused the deadlock.
Trace file extract from two distinct Oracle sessions
*** 2006-11-05 13:29:16.921 *** ACTION NAME:() 2006-11-05 13:29:16.906 *** MODULE NAME:(SQL*Plus) 2006-11-05 13:29:16.906 *** SERVICE NAME:(SYS$USERS) 2006-11-05 13:29:16.906 *** SESSION ID:(27.5) 2006-11-05 13:29:16.906 DEADLOCK DETECTED [Transaction Deadlock] Current SQL statement for this session: delete from t where a = 'x' The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. The following information may aid in determining the deadlock: Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TX-00070023-0000009c 18 27 X 20 38 X TX-00060021-0000008d 20 38 X 18 27 X session 27: DID 0001-0012-00000011 session 38: DID 0001-0014-00000005 session 38: DID 0001-0014-00000005 session 27: DID 0001-0012-00000011 Rows waited on: Session 38: obj - rowid = 0000354E - AAADVOAAEAAAAAPAAB (dictionary objn - 13646, file - 4, block - 15, slot - 1) Session 27: obj - rowid = 0000354E - AAADVOAAEAAAAAPAAA (dictionary objn - 13646, file - 4, block - 15, slot - 0)Note, the fragment (dictionary objn - 13646, file - 4, block - 15, slot - 1), the objn - 13646 relates to the object_id, which can be queried via dba_objects or similar.
Trace file extract for same session with Autonomous Transaction
*** 2006-11-05 14:04:20.328 *** ACTION NAME:() 2006-11-05 14:04:20.328 *** MODULE NAME:(SQL*Plus) 2006-11-05 14:04:20.328 *** SERVICE NAME:(SYS$USERS) 2006-11-05 14:04:20.328 *** SESSION ID:(27.26) 2006-11-05 14:04:20.328 DEADLOCK DETECTED [Transaction Deadlock] Current SQL statement for this session: DELETE FROM T ----- PL/SQL Call Stack ----- object line object handle number name 209DD638 5 procedure MARTIN.P_AT 1D1C566C 1 anonymous block The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. The following information may aid in determining the deadlock: Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TX-00080003-00000091 18 27 X 18 27 X session 27: DID 0001-0012-0000001E session 27: DID 0001-0012-0000001E Rows waited on: Session 27: obj - rowid = 0000354E - AAADVOAAEAAAAAPAAA (dictionary objn - 13646, file - 4, block - 15, slot - 0)As you can see, Oracle does a good job of giving as much information about the involved sessions (including "current" SQL statement, etc.).
Further Information
The issue of deadlocks (and how to deal with them) is quite an extensive subject, and there are plenty of web resources for understanding them.Note, the Oracle Metalink links require a metalink account to access.
- Ask Tom : Determining which query caused deadlock.
- Metalink 164661.1 : ORA-60 / Deadlocks Most Common Causes
- Metalink 171795.1 : Bitmap Indexes and Deadlocks: Deadlocks on Insert Statements
- Metalink 62365.1 : What to do with "ORA-60 Deadlock Detected" Errors
发表评论
-
转:概述 java 数据库连接 3.0 规范的新功能和改进之处
2010-06-02 22:55 1042概述 java 数据库连接 3.0 规范的新功能和 ... -
转:如何利用连接和语句池特性来提高 Oracle 驱动的 JDBC 程序的性能
2010-06-02 22:45 1832开发人员:JDBC ... -
Java用OCI驱连Oracle数据库的实现方法
2009-12-09 22:46 1548Java程序连接oracle数据库时,用oci驱动要比用thi ... -
JDBC OCI——不同平台安装oracle client 配置不同环境变量
2009-12-09 11:17 2477以下内容适用Oracle client安装版: ... -
JDBC OCI的调用过程
2009-12-09 11:13 1624原文:http://hzcaixiao.ite ... -
JBOSS下的OCI配置
2009-12-06 16:25 1058原文:http://hzcaixiao.iteye.com ... -
转载:RAC 专题(英文)
2009-06-11 09:02 988http://www.dba-oracle.com/real_ ... -
转:RAC的TAF简单测试(一)
2009-06-12 01:51 1083http://yangtingkun.itpub.net/po ... -
ASH AWRX相关内容
2009-06-12 05:41 1110ASH AWRX相关内容 -
转:解决Oracle数据库死锁
2009-06-16 09:45 980http://blog.oracle.com.cn/index ... -
转:Oracle性能误区--MTS,RAC,分区,并行查询
2009-06-12 02:11 948http://www.cnblogs.com/zyk/arch ... -
转:死锁的头号原因:不加索引的外键(第6章.锁)
2009-06-16 09:24 1058http://blog.oracle.com.cn/index ... -
转载:enqueue deadlocks,library cache deadlocks.
2009-06-26 03:14 1044http://yong321.freeshell.org/co ... -
查找没有索引的外键
2009-06-26 03:17 751http://asktom.oracle.com/tkyte/ ...
相关推荐
本资源出处:https://github.com/aneasystone/mysql-deadlocks 在工作和学习的过程中偶尔会遇到死锁问题,虽然这种问题遇到的概率不大,但每次遇到的时候要想彻底弄懂其原理并找到解决方案却并不容易。这个项目收集...
标题中的"ch07deadlocks共65页.pdf.zip"表明这是一个关于死锁问题的PDF文档,被压缩在ZIP文件中。"ch07"可能指的是书籍或教程的第七章,而"deadlocks"是数据库管理系统、操作系统或者多线程编程中的一个重要概念,指...
### 死锁(Deadlocks) 死锁是多线程或多进程环境中的一个常见问题,它发生在两个或多个进程因等待对方释放资源而陷入永久等待状态的情况。每个进程都在等待另一个进程释放资源,但后者也在等待前者,从而形成了一...
由于压缩包内只有一个名为 "CH7_Deadlocks.pptx" 的文件,我们可以假设这是一个关于死锁的PowerPoint演示文稿,通常会包含详细的解释、例子和解决方案。 在Python中,死锁通常与`threading`模块相关,该模块提供了...
ch7-Deadlocks.ppt
这个问题在"Chapter 7: Deadlocks"的课件中被详细讨论。一个典型的死锁例子是,当系统有两台磁带驱动器时,进程P1和P2各持有一台,并且都需要对方持有的那台。类似地,如果两个进程分别持有信号量A和B,同时都在等待...
操作系统课件:08_deadlocks.ppt
操作系统英文课件:ch6 Deadlocks.ppt
操作系统英文教学课件:Chapter 7 Deadlocks.ppt
医院信息系统oracle数据库中Deadlocks的探究.pdf
Contents Overview 1 Lesson 1: Concepts – Locks and Lock Manager 3 Lesson 2: Concepts – Batch and Transaction 31 Lesson 3: Concepts – Locks and Applications 51 Lesson 4: Information Collection and ...
21)..Fixed: Rare deadlocks in multi-threaded applications 22)..Fixed: Taking screenshot of minimized window 23)..Fixed: NT service may not log all exceptions 24)..Fixed: SSL port number for Bugzilla ...
java-deadlocks-sample 该存储库包含Java代码示例,这些示例演示Java死锁模式和防止死锁的最佳实践。 在2015年7月4日的EPAM保加利亚TechTalks活动中使用了源代码在他的明确许可下,此示例项目中的源代码来自Heinz ...
SQL Deadlocks With SQL Deadlock Detector, you can: Monitor and detect long-running locks and deadlocks 24/7 Identify blocking SQL code, locked objects and deadlock victims with pinpoint accuracy ...
Locks, Deadlocks, and SynchronizationApril 5, 2006Abstract This paper explains how to use synchronization mechanisms to protect shared memory locations in kernel-mode drivers for the Microsoft:...
- **Single-Threaded Deadlocks:** Even single-threaded applications can experience deadlocks if synchronized blocks are used incorrectly, such as nesting synchronized blocks improperly. - **Thread....