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

Question

In versions of Oracle 10+, can you update and delete in the same table, causing deadlocks, even if they work simultaneously with different rows of the same table?

The table has a primary key consisting of two columns, and it does not have FK linked / linked to any other table. And there is no parent / child relationship with another table

I believe that this will not create a dead end, but I ran into a problem in my application.

add oracle track:

The following deadlock is not an ORACLE error. This is a dead end due to a user error in the design of the application or due to an incorrect SQL query. The following information may help determine the impasse:

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 

Answer 1

If you can update your question with a deadlock, this will be useful information. (When your application encounters a dead end, Oracle will raise ORA-00060 and the trace file will be written to user_dump_dest.) If you look in the trace file, you will find a section called "Deadlock Graph". If you can publish this, as well as publish the expression that caused the impasse and other statements related to the impasse, we can begin to draw some conclusions. (All requested information is available in the trace file.)

As Alessandro mentioned, it is possible that sessions block different rows in the same table until a dead end is due to unindexed foreign keys in the child table of the parent / child relationship. In addition, it is possible that you can have deadlocks in two sessions, updating different rows of the same table, even if the table is not part of the parent / child relationship, if, for example, the table has a shortage of ITL records.

Resend the information above, and I’m sure that we can determine the root cause of your deadlock.

Added on 7/30/2012 **

Add the following, now that the deadlock trace file has been set: Well, first, based on the contents of the trace file, this is a simple dead end due to matches / collisions of sessions in the lines that they are trying to block. Despite your previous comments about the deadlock on different lines, I have to tell you that this particular deadlock is related to row level locking on the same lines.

The fact that the deadlock diagram shows the lock mode is held in the “X” position (exception), and the standby mode is “X”, it tells me that this is a simple row level lock.

In this case, SID 20 performs "delete from RPT_TABLE.TEMP_TABLE_T1, where TEMP_T1_ID =: 1", and already has a lock on rowid AAAPDIAAMAAAEfIAAA.

Meanwhile, SID 790 performs "RPT_TABLE.T1_UPDATE_StoredProc", and already holds the lock on rowid AAAPDIAAMAAAEfGAAA.

A note from the “Lines Waiting in” section of the trace file that SID 20 expects in a row that SID 790 supports, and SID 790 expects in a row that SID 20 holds. This is a classic dead end.

Additional Information:

• The queue type is TX (see deadlock schedule), so it is definitely not blocked due to unindexed foreign keys. If it were blocked due to non-indexed FKs, the queue type would be TM, not TX. (There is at least one other case where TM enqueues are involved, and these are not unindexed FKs. Therefore, do not assume that TM enqueue always means unindexed FKs.)

• Standby is “X” (exception), so it is row-level locking. If the standby mode was "S" (general), then this will not be row-level locking. Rather, it could be a lack of ITL or enforcement of a PC or the UK.

Hope this helps!

Answer 2

I don’t know if you have any foreign keys involved in your application, but perhaps this is 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/datainte.htm#CNCPT1657

Oracle Database maximizes concurrency parental key management relative to dependent foreign keys. The behavior of the lock depends on the indexing of the foreign key columns. If foreign keys are not indexed, then the child table will probably be locked more often, deadlocks will occur, and concurrency will decrease. For this reason, foreign keys should almost always be indexed. The one exception is that the corresponding unique or primary key is never updated or deleted.

Locks and non-indexed foreign keys

If both conditions are true, the database receives the full lock table in the child table:

There is no index in the foreign key column of the child table.

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

If this is not your case , try providing more information about it. Tell us about the type of Holden / locks requested by sessions and look at the system tables V $ LOCK, V $ LOCKED_OBJECT, DBA_DDL_LOCKS, DBA_DML_LOCKS or V $ SESSION_WAIT.