Description:
Below a little example how easy it is to produce a deadlock:
How to repeat:
Table and Data structure:
------------------------------------------------------------------
/*
PARENT
| |
| |
CHILD1 CHILD2
*/
DROP TABLE CHILD1;
DROP TABLE CHILD2;
DROP TABLE PARENT;
CREATE TABLE PARENT(
P INTEGER PRIMARY KEY
);
CREATE TABLE CHILD1(
P INTEGER NOT NULL,
C1 INTEGER PRIMARY KEY,
DATA INTEGER,
CONSTRAINT FK_1 FOREIGN KEY(P) REFERENCES PARENT(P) ON DELETE CASCADE
);
CREATE TABLE CHILD2(
P INTEGER NOT NULL,
C2 INTEGER PRIMARY KEY,
DATA INTEGER,
CONSTRAINT FK_2 FOREIGN KEY(P) REFERENCES PARENT(P) ON DELETE CASCADE
);
INSERT INTO PARENT VALUES(1);
INSERT INTO PARENT VALUES(2);
INSERT INTO CHILD1 VALUES(1,10,-1);
INSERT INTO CHILD2 VALUES(1,20,-2);
SELECT * FROM PARENT;
SELECT * FROM CHILD1;
SELECT * FROM CHILD2;
commit;
###########################################################################################
No deadlock occurs in this scenario:
Transaction 1: | Transaction 2:
---------------------------------------------------------------------------------
(1) UPDATE CHILD1 SET DATA=111 WHERE P=1; |
(2) | DELETE FROM PARENT WHERE P=2;
(3) UPDATE CHILD2 SET DATA=222 WHERE P=1; |
###########################################################################################
DEADLOCK OCCURS IN THIS SCENARIO: (1) and (3) are exchanged
Transaction T1: | Transaction T2:
---------------------------------------------------------------------------------
(1) UPDATE CHILD2 SET DATA=111 WHERE P=1; |
(2) | DELETE FROM PARENT WHERE P=2;
(3) UPDATE CHILD1 SET DATA=222 WHERE P=1; |
DEADLOCK!!!
Cause:
After (1), T1 holds a "row_exclusive" lock on CHILD2.
When T2 gets called with (2) it has to check the referential
integrity and creates "temp" "tab_share" Locks, first on CHILD1 and
then on CHILD2. (Create order of the Foreign Keys FK_1 and FK_2 ???).
But Child 2 is (row_exclusive) Locked by T1, so T2 has to wait.
Now T1 calls the Update of CHILD1 (3). T1 has to wait until T2 releases
the "tab_share" lock on CHILD1.
-> T1 waits for a row_exclusive lock on CHILD1.
-> T2 waits for a tab_share lock on CHILD2.
-> DEADLOCK
The real Problem:
a) The automatic DEADLOCKDETECTION does not detect this DEADLOCK.
b) T2 should set the "temp" "tab_share" locks all at once or none at all.
(I do not really know if this is possible)
Questions:
- Is there any possibility to adjust Deadlockdetection
to detect "tab_share" DEADLOCKS?
- Is there a workaround, like
DELETE FROM PARENT WHERE P=2 WITH LOCK OPTION NOWAIT; ???
The problem for us is:
How to ensure consistent data in a large application
using maxdb with many many tables and many many foreign keys,
when we have many many deadlocks resulting of the
consistency features.
I know that it is possible to change the order of the
SQL Statements in our application. But the problem is
that we have complex data structures, and it would be very
hard (nearly impossible) to find all critical parts in the program
code, which could end up in a deadlock as described.
Description: Below a little example how easy it is to produce a deadlock: How to repeat: Table and Data structure: ------------------------------------------------------------------ /* PARENT | | | | CHILD1 CHILD2 */ DROP TABLE CHILD1; DROP TABLE CHILD2; DROP TABLE PARENT; CREATE TABLE PARENT( P INTEGER PRIMARY KEY ); CREATE TABLE CHILD1( P INTEGER NOT NULL, C1 INTEGER PRIMARY KEY, DATA INTEGER, CONSTRAINT FK_1 FOREIGN KEY(P) REFERENCES PARENT(P) ON DELETE CASCADE ); CREATE TABLE CHILD2( P INTEGER NOT NULL, C2 INTEGER PRIMARY KEY, DATA INTEGER, CONSTRAINT FK_2 FOREIGN KEY(P) REFERENCES PARENT(P) ON DELETE CASCADE ); INSERT INTO PARENT VALUES(1); INSERT INTO PARENT VALUES(2); INSERT INTO CHILD1 VALUES(1,10,-1); INSERT INTO CHILD2 VALUES(1,20,-2); SELECT * FROM PARENT; SELECT * FROM CHILD1; SELECT * FROM CHILD2; commit; ########################################################################################### No deadlock occurs in this scenario: Transaction 1: | Transaction 2: --------------------------------------------------------------------------------- (1) UPDATE CHILD1 SET DATA=111 WHERE P=1; | (2) | DELETE FROM PARENT WHERE P=2; (3) UPDATE CHILD2 SET DATA=222 WHERE P=1; | ########################################################################################### DEADLOCK OCCURS IN THIS SCENARIO: (1) and (3) are exchanged Transaction T1: | Transaction T2: --------------------------------------------------------------------------------- (1) UPDATE CHILD2 SET DATA=111 WHERE P=1; | (2) | DELETE FROM PARENT WHERE P=2; (3) UPDATE CHILD1 SET DATA=222 WHERE P=1; | DEADLOCK!!! Cause: After (1), T1 holds a "row_exclusive" lock on CHILD2. When T2 gets called with (2) it has to check the referential integrity and creates "temp" "tab_share" Locks, first on CHILD1 and then on CHILD2. (Create order of the Foreign Keys FK_1 and FK_2 ???). But Child 2 is (row_exclusive) Locked by T1, so T2 has to wait. Now T1 calls the Update of CHILD1 (3). T1 has to wait until T2 releases the "tab_share" lock on CHILD1. -> T1 waits for a row_exclusive lock on CHILD1. -> T2 waits for a tab_share lock on CHILD2. -> DEADLOCK The real Problem: a) The automatic DEADLOCKDETECTION does not detect this DEADLOCK. b) T2 should set the "temp" "tab_share" locks all at once or none at all. (I do not really know if this is possible) Questions: - Is there any possibility to adjust Deadlockdetection to detect "tab_share" DEADLOCKS? - Is there a workaround, like DELETE FROM PARENT WHERE P=2 WITH LOCK OPTION NOWAIT; ??? The problem for us is: How to ensure consistent data in a large application using maxdb with many many tables and many many foreign keys, when we have many many deadlocks resulting of the consistency features. I know that it is possible to change the order of the SQL Statements in our application. But the problem is that we have complex data structures, and it would be very hard (nearly impossible) to find all critical parts in the program code, which could end up in a deadlock as described.