|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Deadlock experienced but Blocking lock expected [message #653902 is a reply to message #653896] |
Thu, 21 July 2016 12:20  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 21 July 2016 09:13
Thanks for the feedback and link.
Well, as usual, there are cases where FK index is deadlock cause.
Session 1:
SQL> -- session 1
SQL> drop table child purge
2 /
Table dropped.
SQL> drop table master purge
2 /
Table dropped.
SQL> create table master(
2 master_id number
3 )
4 /
Table created.
SQL> alter table master
2 add constraint master_pk
3 primary key(
4 master_id
5 )
6 /
Table altered.
SQL> create table child(
2 child_id number,
3 master_id number,
4 val number
5 )
6 /
Table created.
SQL> alter table child
2 add constraint child_pk
3 primary key(
4 child_id
5 )
6 /
Table altered.
SQL> alter table child
2 add constraint child_fk
3 foreign key(
4 master_id
5 )
6 references master
7 /
Table altered.
SQL> create index child_fk
2 on child(
3 master_id
4 )
5 /
Index created.
SQL> insert
2 into master
3 values(1)
4 /
1 row created.
SQL> commit
2 /
Commit complete.
SQL>
Session 2:
SQL> -- session 2
SQL> insert
2 into child
3 values(1,1,1)
4 /
1 row created.
SQL>
Back to Session 1:
SQL> -- session 1
SQL> delete master
2 where master_id = 1
3 /
Session 1 waits. Meanwhile Session 2:
SQL> -- session 2
SQL> insert
2 into child
3 values(2,1,2)
4 /
A second later Session 1:
SQL> -- session 1
SQL> delete master
2 where master_id = 1
3 /
where master_id = 1
*
ERROR at line 2:
ORA-00060: deadlock detected while waiting for resource
SQL>
Why?
bug 2546492
Documentation incorrectly describes locking in case of indexed/unindexed foreign keys. Correct behavior is as follows:
Starting in version 9.2.0, a Row-SS lock is taken on the parent table for any DML issued against the child table. This will occur with or without an index on the foreign key column of the child table. Further, there will be a ROW-SS lock taken on the child table for any DELETE/UPDATE issued against the parent table when an index on the foreign key column of the child table is defined. There will be a ROW-SX lock taken on the child table for an DELETE issued against the parent table when no index on the foreign key column of the child table is defined and a delete cascade constraint is defined. In both cases, the lock is held until the transaction is committed or rolled back. This change in behaviour was introduced in version 9.2.0 to address problems with parallel DML hanging in certain situations.
Let's see what happens in our case:
1. First insert into child table performed by "Session 2" results in ROW-SS lock on master table regardless if foreign key is indexed or not.
2. DELETE from master performed by "Session 1" finds that there is a lock, put by "Session 1" in Step 1, so it waits for the lock to be released. However, if foreign key is indexed, DELETE will cause a ROW-SS lock on child table.
3. If foreign key is not indexed, second insert into child table performed by "Session 2" does not encounter any locks preventing the insert. However, if foreign key is indexed, it runs into ROW-SS lock on child table put by "Session 1" in Step 2. As a result "Session 2" has to wait for "Session 1" to release Step 2 lock, while "Session 1" has to wait for "Session 2" to release Step 1 lock - and we have a deadlock.
SY.
[Updated on: Thu, 21 July 2016 12:22] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
