I am having a deadlock which is quite perplexing:
DEADLOCK DETECTED
Current SQL statement for this session:
LOCK TABLE table_1 IN EXCLUSIVE MODE
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
TM-00001927-00000000 15 22 SS 34 46 X table_2
TM-00001814-00000000 34 46 X 15 22 X table_1
session 22: DID 0001-000F-00000012 session 46: DID 0001-0022-0000003F
session 46: DID 0001-0022-0000003F session 22: DID 0001-000F-00000012
Rows waited on:
Session 46: obj - rowid = 0000371E - AAADceAAHAAAAAAAAA
(dictionary objn - 14110, file - 7, block - 0, slot - 0)
Session 22: obj - rowid = 000018CD - AAADWSAAGAAAAAAAAA
(dictionary objn - 6349, file - 6, block - 0, slot - 0)
nformation on the OTHER waiting sessions:
Session 46:
pid=34 serial=360 audsid=221608 user: 222/R_USER
O/S info: user: XXX, term: pts/4, ospid: 8768, machine: m1
program: ? (TNS V1-V3)
application name: ? (TNS V1-V3), hash value=0
Current SQL Statement:
LOCK TABLE table_2 IN EXCLUSIVE MODE
End of information on OTHER waiting sessions.
Before we begin, the exclusive locks on the tables are required to coordinate some complex updates that must be completed before any other user is allowed to read the table again. The two programs are running at the same time and are different programs. Both issue the lock commands in the same sequence: lock table_1, lock table_2. There are other programs also running against the same database at the same time.
Both of the tables involved table_1 and table_2 have parent tables. The childen have foreign keys to the parents. All foreign keys are indexed.
Here is the order of the actions:
Session #22 above makes an update to the parent of table_2.
Session #46 locks table_1.
Session #22 locks table_1 and waits.
Session #46 locks table_2 and waits.
Session #22 get a -60 error. Rolls back.
Session #46 completes.
I have not been able to reproduce this error using sql*plus in two sessions. Using sql*plus I cannot get the SS lock on table_2.
For some reason session #22 aquires an SS lock on table_2. I assume the SS lock is a Row Share Table (RS) lock, also known as, a Subshare Table lock (SS). Have I identified the lock correctly? If so, why is the update to the parent locking the child during normal processing when I cannot get it to do the same using sql*plus?
My next problem is that the "Rows waited on" do not make any sense:
Session 46: obj - rowid = 0000371E - AAADceAAHAAAAAAAAA
(dictionary objn - 14110, file - 7, block - 0, slot - 0)
Session 22: obj - rowid = 000018CD - AAADWSAAGAAAAAAAAA
(dictionary objn - 6349, file - 6, block - 0, slot - 0)
I looked up the obects using
select dbms_rowid.rowid_object( 'AAADceAAHAAAAAAAAA' ) from dual;
select dbms_rowid.rowid_object( 'AAADWSAAGAAAAAAAAA' ) from dual;
select * from dba_objects where object_id = 14110;
select * from dba_objects where object_id = 6349;
select * from dba_objects where data_object_id = 14110;
select * from dba_objects where data_object_id = 6349;
to make sure they matched the 14110 and 6349 above.
The object 14110 is an index of the parent of the parent of the child table in question (p->p->c). 6349 is another table which appears to have only select queries against it.
The deadlock occurs fairly often and each time the "Rows waited on" point to different combinations of tables and indexes. None of them are ever the actual tables indicated in the deadlock: table_1 and table_2. Am I breaking down the deadlock information correctly? Have I correclty identified the "Rows waited on"? Please explain the structure of the rows identified. For example what exactly is the dictionary objn?
How do I proceed to determine exactly what action is causing the SS lock on table_2 which I believe should not be there?
Why can't I reproduce this in sql*plus?
Is this a real deadlock or is this some type of resource conflict?
