Deadlockdetecktion and "tab_share" locks resulting from foreign keys...
Below a little example how easy it is to produce a deadlock:
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.
P.S.: how is this problem in other databases solved? (oracle,..)
greetings and thank you very much in advance,
Andre'
--
__________________________________________________________________________
Als Technologieunternehmen konzipieren und entwickeln wir maßgeschneiderte Feedback-
und
Monitoring-Systeme - wie beispielsweise Lösungen für Beschwerde- und Ideenmanagement.
Mit dem Inquery® Survey Server bieten wir eine der leistungsfähigsten Standardlösungen
für
Online-Umfragen mit dem Schwerpunkt auf der Messung von Kundenzufriedenheit an.
__________________________________________________________________________
Inworks GmbH
Andre Reitz, Leiter Entwicklung
Hörvelsinger Weg 39, 89081 Ulm, Germany
Tel +49 (0) 731 / 93807-21
Fax +49(0)731/93807-18
Internet: http://www.inworks.de
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
