I have a situation where I have a parent and child table and a deadlock occurs when I try to update the parent table while deleting from the child table. Someone told me that in order to clear up the deadlock I have to add an index to the child table. I did and it cleared it up. I am not clear as to why the index is needed to clear up the deadlock.
Here is what I did to create the deadlock:
17:01:45 SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
5 rows selected.
17:01:51 SQL> create table t1 (pk1 NUMBER, fk1 number,
17:02:55 2 CONSTRAINT pk_t1 PRIMARY KEY (pk1),
17:02:58 3 CONSTRAINT t1_uc UNIQUE (pk1, fk1));
Table created.
Elapsed: 00:00:00.15
17:03:00 SQL> create table t2 (pk2 NUMBER, fk1 number, fk2 number,
17:03:03 2 CONSTRAINT PK_t2 PRIMARY KEY (pk2),
17:03:05 3 CONSTRAINT FK_t2 FOREIGN KEY (fk2, fk1) REFERENCES t1 (pk1, fk1));
Table created.
Elapsed: 00:00:00.28
17:03:07 SQL> insert into t1 values (1,1);
1 row created.
Elapsed: 00:00:00.00
17:03:11 SQL> insert into t1 values (2,2);
1 row created.
Elapsed: 00:00:00.00
17:03:13 SQL> insert into t1 values (7,7);
1 row created.
Elapsed: 00:00:00.01
17:03:15 SQL> insert into t2 values (1,1,1);
1 row created.
Elapsed: 00:00:00.00
17:03:17 SQL> insert into t2 values (2,2,2);
1 row created.
Elapsed: 00:00:00.00
17:03:20 SQL> commit;
Commit complete.
Elapsed: 00:00:00.08
17:03:21 SQL> select * from t1;
PK1 FK1
---------- ----------
1 1
2 2
7 7
3 rows selected.
Elapsed: 00:00:00.00
17:03:23 SQL> select * from t2;
PK2 FK1 FK2
---------- ---------- ----------
1 1 1
2 2 2
2 rows selected.
17:05:02 SQL> desc t1
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
PK1 NOT NULL NUMBER
FK1 NUMBER
17:05:03 SQL> desc t2
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
PK2 NOT NULL NUMBER
FK1 NUMBER
FK2 NUMBER
Now in this session of SQL*Plus I entered the following command without committing:
17:12:35 SQL> delete from t2 where fk1 = 2 and fk2 = 2;
1 row deleted.
Elapsed: 00:00:00.01
I then opened up SQL*Plus session number 2 and entered an update command. It is then at this point that SQL*Plus session number 2 hangs until I issue a commit or rollback in the first SQL*Plus session. I issue the rollback after waiting 5 minutes.
17:13:03 SQL> update t1 set fk1 = 8 where pk1 = 7;
1 row updated.
Elapsed: 00:00:05.16
Why does this hang?
After I rollback the delete in first SQL*Plus session and update from the second session I create this index:
17:20:26 SQL> create index t2_idx on t2 (fk1,fk2);
Index created.
Elapsed: 00:00:00.03
I then re-enter the same delete command in the first SQL*Plus session
17:28:09 SQL> select * from t2;
PK2 FK1 FK2
---------- ---------- ----------
1 1 1
2 2 2
2 rows selected.
Elapsed: 00:00:00.01
17:28:11 SQL> select * from t1;
PK1 FK1
---------- ----------
1 1
2 2
7 7
3 rows selected.
17:28:17 SQL> delete from t2 where fk1 = 2 and fk2 = 2;
1 row deleted.
Elapsed: 00:00:00.00
17:28:40 SQL> select * from t2;
PK2 FK1 FK2
---------- ---------- ----------
1 1 1
1 row selected.
I then jump to SQL*Plus session number 2 and re-enter the update command:
17:29:55 SQL> update t1 set fk1 = 8 where pk1 = 7;
1 row updated.
Elapsed: 00:00:00.00
And it works immediately.
Why does the index make a difference?