Deadlock при наличии индекса на внешний ключ

AntonGart
Дата: 22.09.2004 19:39:32
Объясните мне пожалуйста в чем дело. Сделал простой тест. Создал 2 таблицы главную master и подчиненную child с внешним ключом и соответствующим Constraint on delete cascade. Делаю в первой сессии:
insert into master (masterID) values (1);
commit;
Во второй:
insert into child(masterID,field) values (1,'test1');
В первой:
delete from master where masterID=1; --> заблокирован
Во второй:
insert into child(masterID,field) values (1,'test2'); --> Ок, если же я индексирую внешний ключ в child, то в первой: ORA-00060 Deadlock detected...

Хотя вроде должно быть наоборот: неиндексированные внешние ключи могут стать причиной deadlock.

Oracle 9.2.0.1 Win2K

Спасибо!
Stax
Дата: 22.09.2004 20:18:43
Oracle8i Enterprise Edition Release 8.1.6.0.0
Без индекса слетает сессия 2
Stax
Дата: 22.09.2004 21:05:52
В догонку
И с индексом тоже
SY
Дата: 22.09.2004 23:31:51
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 your 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.
AntonGart
Дата: 23.09.2004 11:23:00
Большое спасибо, особенно SY!
Нормально если я буду делать так ?
Во второй сессии:

SELECT NULL FROM master where masterID=1 FOR UPDATE;

INSERT INTO child VALUES (1,'test1');
INSERT INTO child VALUES (1,'test2');
...
INSERT INTO child VALUES (1,'testN');

COMMIT;
AntonGart
Дата: 23.09.2004 17:07:29
Эх, перепутал...
Во второй:
SELECT NULL FROM master where masterID=1 FOR UPDATE;
DELETE FROM master where masterID=1;