更新和删除表中的不同行时是否可能出现死锁? [英] Is a deadlock possible when updating and deleting different rows in a table?
问题描述
在Oracle 10+版本中，即使在同一表的不同行上同时进行操作，在同一表上进行更新和删除也会导致死锁吗?
该表的主键由两列组成，并且没有与其他任何表关联/引用的FK.而且与其他表没有父/子关系
我相信这不会造成死锁，但我的应用程序中遇到了一个问题.
添加oracle跟踪:
以下死锁不是ORACLE错误.由于应用程序设计中的用户错误或发出不正确的临时SQL导致的死锁.以下信息可能有助于确定死锁:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-0007003e-0081d6c3 45 790 X 104 20 X
TX-00080043-0085e6be 104 20 X 45 790 X
session 790: DID 0001-002D-000035F9 session 20: DID 0001-0068-000007F6
session 20: DID 0001-0068-000007F6 session 790: DID 0001-002D-000035F9
Rows waited on:
Session 790: obj - rowid = 0000F0C8 - AAAPDIAAMAAAEfIAAA
(dictionary objn - 61640, file - 12, block - 18376, slot - 0)
Session 20: obj - rowid = 0000F0C8 - AAAPDIAAMAAAEfGAAA
(dictionary objn - 61640, file - 12, block - 18374, slot - 0)
----- Information for the OTHER waiting sessions ----- Session 20:
sid: 20 ser: 4225 audsid: 57496371 user: 72/RPT_TABLE
flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 104 O/S info: user: oracle, term: UNKNOWN, ospid: 20798
image: oracle@caidb10p-node1
client details:
O/S info: user: gtsgen, term: unknown, ospid: 1234
machine: caiapp08p-node0.nam.nsroot.net program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
current SQL:
delete from RPT_TABLE.TEMP_TABLE_T1 where TEMP_T1_ID=:1
----- End of information for the OTHER waiting sessions -----
Information for THIS session:
----- Current SQL Statement for this session (sql_id=bsaxpc2bdps9q) ----- UPDATE RPT_TABLE.TEMP_TABLE_T1 temp1 SET temp1.CLIENT_ID = (SELECT MIN(INVMAP.CLIENT_ID) FROM LI_REF.REF_CLIENT_MAP INVMAP WHERE INVMAP.F_CODE = :B2 AND INVMAP.AID = temp1.ID AND temp1.R_ID=:B1 )
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
45887d750 24 procedure RPT_TABLE.T1_UPDATE_StoredProc
6399ba188 1 anonymous block
如果可以使用死锁图更新问题，那么这将是有用的信息. (当您的应用程序遇到死锁时，Oracle将引发ORA-00060，并且将跟踪文件写入到user_dump_dest中.)如果您在跟踪文件中查找，则会发现一个名为死锁图"的部分.如果您可以发布该文章，并且还发布导致该死锁的语句以及该死锁中涉及的其他语句，那么我们可以开始得出一些结论. (我要求的所有信息都可以在跟踪文件中找到.)
正如Alessandro所提到的，由于父/子关系的子表上未索引的外键，锁定同一表中不同行的会话可能会死锁.另外，即使该表不属于父/子关系，例如，如果该表缺少ITL条目，您也可能在两个会话上死锁，从而更新同一表的不同行.
再次，发布上面要求的信息，我相信我们可以确定造成僵局的根本原因.
添加于2012年7月30日**
添加以下内容，现在已经提供了死锁跟踪文件: 好的，首先，基于跟踪文件的内容，这是一个简单的死锁，因为会话在尝试锁定的行上重叠/碰撞.尽管您先前曾对不同行上的死锁发表过评论，但我还是要告诉您，此特定死锁是由于相同行上的行级锁定所致.
死锁图显示了持有锁的模式为"X"(不包括)，而等待锁的模式为"X"，这告诉我这是简单的行级锁.
在这种情况下，SID 20正在执行从RPT_TABLE.TEMP_TABLE_T1删除，其中TEMP_T1_ID =:1"，并且已经具有对行ID AAAPDIAAMAAAEfIAAA的锁定.
同时，SID 790正在执行"RPT_TABLE.T1_UPDATE_StoredProc"，同时已经对rowid AAAPDIAAMAAAEfGAAA保持了锁定.
从跟踪文件的行已等待"部分中注意到，SID 20正在等待SID 790保留的行，而SID 790正在等待SID 20所保留的行.这是经典的僵局.
一些其他信息:
-
队列类型是TX(请参阅死锁图)，因此，由于未索引外键，这肯定是 not 锁定.如果由于未索引的FK而被锁定，则入队类型将是TM，而不是TX. (至少有另外一种情况涉及TM排队，并且它不是未索引的FK.因此，请不要假定TM排队总是意味着未索引的FK.)
-
正在等待锁定的模式是"X"(不包括)，因此这是行级锁定.如果等待的模式为"S"(共享)，则不为行级锁定.相反，可能是ITL短缺，PK或英国执法.
希望有帮助！
In Oracle 10+ versions, can update and delete on the same table cause deadlocks even if they are operating on different rows of same table concurrently?
The table has primary key made-up of two columns, and do not have any FK associated/refereed with any other table. And there is no parent/child relation with other table
What I believe is, it will not create a deadlock, but I'm facing a issue in my application.
adding the oracle trace :
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-0007003e-0081d6c3 45 790 X 104 20 X
TX-00080043-0085e6be 104 20 X 45 790 X
session 790: DID 0001-002D-000035F9 session 20: DID 0001-0068-000007F6
session 20: DID 0001-0068-000007F6 session 790: DID 0001-002D-000035F9
Rows waited on:
Session 790: obj - rowid = 0000F0C8 - AAAPDIAAMAAAEfIAAA
(dictionary objn - 61640, file - 12, block - 18376, slot - 0)
Session 20: obj - rowid = 0000F0C8 - AAAPDIAAMAAAEfGAAA
(dictionary objn - 61640, file - 12, block - 18374, slot - 0)
----- Information for the OTHER waiting sessions ----- Session 20:
sid: 20 ser: 4225 audsid: 57496371 user: 72/RPT_TABLE
flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 104 O/S info: user: oracle, term: UNKNOWN, ospid: 20798
image: oracle@caidb10p-node1
client details:
O/S info: user: gtsgen, term: unknown, ospid: 1234
machine: caiapp08p-node0.nam.nsroot.net program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
current SQL:
delete from RPT_TABLE.TEMP_TABLE_T1 where TEMP_T1_ID=:1
----- End of information for the OTHER waiting sessions -----
Information for THIS session:
----- Current SQL Statement for this session (sql_id=bsaxpc2bdps9q) ----- UPDATE RPT_TABLE.TEMP_TABLE_T1 temp1 SET temp1.CLIENT_ID = (SELECT MIN(INVMAP.CLIENT_ID) FROM LI_REF.REF_CLIENT_MAP INVMAP WHERE INVMAP.F_CODE = :B2 AND INVMAP.AID = temp1.ID AND temp1.R_ID=:B1 )
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
45887d750 24 procedure RPT_TABLE.T1_UPDATE_StoredProc
6399ba188 1 anonymous block
If you could update your question with the deadlock graph, that would be useful information. (When your application encounters a deadlock, Oracle will raise an ORA-00060, and a tracefile will be written to the user_dump_dest.) If you look in the trace file, you'll find a section called the "Deadlock Graph". If you can post that, and also post the statement that caused the deadlock and other statements involved in the deadlock, then we can begin to draw some conclusions. (All the information I requested is available in the trace file.)
As Alessandro mentioned, it's possible for sessions locking different rows in the same table to deadlock due to unindexed foreign keys on the child table of a parent/child relationship. Also, It's possible that you could have deadlocks on two sessions updating different rows of the same table, even if the table is not part of a parent/child relationship, if, for example, the table has a shortage of ITL entries.
Again, post the information requested above, and I'm confident we can determine the root cause of your deadlock.
Added on 7/30/2012 **
Adding the following, now that the deadlock trace file has been supplied: Ok, first off, based on the trace file contents, this is a simple deadlock due to sessions overlapping/colliding on the rows they are trying to lock. Despite your previous comments about the deadlock being on different rows, I'm here to tell you that this particular deadlock is due to row-level locking on the same rows.
The fact that the deadlock graph shows mode the lock is held in is 'X' (exclusive) and the mode the lock is waited on is 'X', tells me this is simple row-level locking.
In this case, SID 20 is executing "delete from RPT_TABLE.TEMP_TABLE_T1 where TEMP_T1_ID=:1" and already has a lock on rowid AAAPDIAAMAAAEfIAAA.
Meanwhile, SID 790 is executing "RPT_TABLE.T1_UPDATE_StoredProc", while already holding a lock on rowid AAAPDIAAMAAAEfGAAA.
Note from the "Rows waited on" section of the tracefile, that SID 20 is waiting on the row that SID 790 holds and SID 790 is waiting on the row that SID 20 is holding. This is a classic deadlock.
Some additional information:
Enqueue type is TX (see the deadlock graph), so, this is definitely not locking due to unindexed foreign keys. If it were locking due to unindexed FKs, the enqueue type would be TM, not TX. (There is at least one other case where TM enqueues are involved, and it's not unindexed FKs. So, don't assume that TM enqueue always means unindexed FKs.)
The mode the lock is being waited on is 'X' (exclusive), so this is row-level locking. If the mode waited on was 'S' (shared), then it would not be row-level locking. Rather, it could be ITL shortage or PK or UK enforcement.
Hope that helps!
这篇关于更新和删除表中的不同行时是否可能出现死锁?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
