oracle Is a deadlock possible when updating and deleting different rows in a table?

Is a deadlock possible when updating and deleting different rows in a table?

提问by Learner

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?

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

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.

What I believe is, it will not create a deadlock, but I'm facing a issue in my application.

adding the oracle trace :

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:

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

回答by Mark J. Bobak

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.)

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.

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.

Again, post the information requested above, and I'm confident we can determine the root cause of your deadlock.

Added on 7/30/2012 **

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 differentrows, I'm here to tell you that this particular deadlock is due to row-level locking on the samerows.

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 differentrows, I'm here to tell you that this particular deadlock is due to row-level locking on the samerows.

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.

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 hasa lock on rowid AAAPDIAAMAAAEfIAAA.

In this case, SID 20 is executing "delete from RPT_TABLE.TEMP_TABLE_T1 where TEMP_T1_ID=:1" and already hasa lock on rowid AAAPDIAAMAAAEfIAAA.

Meanwhile, SID 790 is executing "RPT_TABLE.T1_UPDATE_StoredProc", while already holding a lock on rowid AAAPDIAAMAAAEfGAAA.

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.

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:

Some additional information:

• Enqueue type is TX (see the deadlock graph), so, this is definitely notlocking 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 notbe row-level locking. Rather, it could be ITL shortage or PK or UK enforcement.

• Enqueue type is TX (see the deadlock graph), so, this is definitely notlocking 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 notbe row-level locking. Rather, it could be ITL shortage or PK or UK enforcement.

Hope that helps!

Hope that helps!

回答by Alessandro Rossi

I don't know if you have foreign keys involved in your application but it could probably be the source of your locks. If so take a look at these links:

I don't know if you have foreign keys involved in your application but it could probably be the source of your locks. If so take a look at these links:

http://docs.oracle.com/cd/E11882_01/server.112/e16508/consist.htm#BABCAHDJ

http://docs.oracle.com/cd/E11882_01/server.112/e16508/consist.htm#BABCAHDJ

http://docs.oracle.com/cd/E11882_01/server.112/e16508/datainte.htm#CNCPT1657

http://docs.oracle.com/cd/E11882_01/server.112/e16508/datainte.htm#CNCPT1657

Oracle Database maximizes the concurrency control of parent keys in relation to dependent foreign keys. Locking behaviour depends on whether foreign key columns are indexed. If foreign keys are not indexed, then the child table will probably be locked more frequently, deadlocks will occur, and concurrency will be decreased. For this reason foreign keys should almost always be indexed. The only exception is when the matching unique or primary key is never updated or deleted.

Oracle Database maximizes the concurrency control of parent keys in relation to dependent foreign keys. Locking behaviour depends on whether foreign key columns are indexed. If foreign keys are not indexed, then the child table will probably be locked more frequently, deadlocks will occur, and concurrency will be decreased. For this reason foreign keys should almost always be indexed. The only exception is when the matching unique or primary key is never updated or deleted.

Locks and Unindexed Foreign Keys

Locks and Unindexed Foreign Keys

When both of the following conditions are true, the database acquires a full table lock on the child table:

When both of the following conditions are true, the database acquires a full table lock on the child table:

No index exists on the foreign key column of the child table.

No index exists on the foreign key column of the child table.

A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges rows into the parent table. Inserts into the parent table do not acquire table locks on the child table.

A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges rows into the parent table. Inserts into the parent table do not acquire table locks on the child table.

If this is not your casetry to provide more informations about it. Tell us about the Kind of locks Holden/requested by the sessions and take a look at the system tables V$LOCK, V$LOCKED_OBJECT, DBA_DDL_LOCKS, DBA_DML_LOCKS or V$SESSION_WAIT.

If this is not your casetry to provide more informations about it. Tell us about the Kind of locks Holden/requested by the sessions and take a look at the system tables V$LOCK, V$LOCKED_OBJECT, DBA_DDL_LOCKS, DBA_DML_LOCKS or V$SESSION_WAIT.