InterBase: lock conflict on no wait transaction violation of FOREIGN KEY constraint

[mjustin]

Zitat von jobo:

Daraus ergibt sich die Frage, fliegen hier nebenläufig noch andere Anweisungen rum?

Also so etwas

Es ist reproduzierbar:

* ich starte in einem Datenbankclient eine Transaktion, ändere einen Wert (natürlich nicht den PK!) im Master, aber führe kein COMMIT durch
* ich führe im Programm (andere Transaktion) das INSERT in der Detail-Tabelle aus
* und: die Fehlermeldung erscheint

Schlussfolgerung: Detail-INSERTs sind nur bei geschlossenen Transaktionen auf den MASTER möglich. Nächste Frage ist, ob das immer, nicht nur bei diesem Tabellenpaar auftritt...

[jobo]

Macht Ihr denn da im Programm explizite Transaktionsanweisungen?
Also Connection.BeginTrans oder irgendsowas- weiß nicht mehr wie das da genau genannt wird?
Die Frage wäre auch glaub ich nicht so sehr, was geändert wird, sondern ab welchem Moment die Sperre gesetzt wird und ob es nur eine Schreibsperre oder sogar eben Lesesperre wäre (das Insert verlangt ja per Definition durch FK Constraint nur die Existenz des Mastersatzes).
Also macht Ihr explizit was per Code beim Ändern des Masters oder wenn nicht, welche Sperren setzt der Server und wann genau.

Mit "rumfliegen" meinte ich zunächst nur in der gleichen Sitzung (nicht unbedingt Transaktion). Wenn Ihr fachbedingt Änderung an gleichen Datensätzen aus unterschiedlichen Sitzungen vornehmt, muss man sich das natürlich alles genauer ansehen.
Oder entsteht das (Phänomen) nur, weil ggF. ein User das Programm mehrfach laufen hat und mit mehreren Intanzen des Programms am gleichen Fall arbeitet?

Ps.: Achso, Du machst die Änderung im gleichen Programm/Sitzung?

[mjustin]

Zitat von jobo:

Macht Ihr denn da im Programm explizite Transaktionsanweisungen?

Nein, wir verwenden die Default-Transaktionen die von dbExpress verwaltet werden.

Mit einem anderen SQL Tool (IBExpert) habe ich das ganze reproduziert, sowohl im konkreten Master-Detail Tabellenpaar das die Fehlermeldung erzeugte, als auch in einem ähnlichen Master-Detail Paar. Das Verhalten tritt dort auch auf. In IBExpert habe ich dazu zwei SQL Editor Fenster geöffnet, d.h. UPDATE aus Master und INSERT in Detail erfolgen in verschiedenen Transaktionen.

Nun wage ich zu folgern (zumindest für InterBase 7.5):

INSERTs in eine Detailtabelle, die per Foreign-Key Constraint mit einer Mastertabelle verknüpft sind, schlagen fehl, wenn am Mastersatz Änderungen gemacht wurden, für die noch kein COMMIT erfolgte

[alex517]

Zitat von mjustin:

Nun wage ich zu folgern (zumindest für InterBase 7.5):

INSERTs in eine Detailtabelle, die per Foreign-Key Constraint mit einer Mastertabelle verknüpft sind, schlagen fehl, wenn am Mastersatz Änderungen gemacht wurden, für die noch kein COMMIT erfolgte

Das muss so sein (ist auch schon immer so),
sonst könntest du dein Detail nicht committen,
da der Master ja mit Rollback wieder verworfen werden könnte.

[mjustin]

Zitat von alex517:

Zitat von mjustin:

Nun wage ich zu folgern (zumindest für InterBase 7.5):

INSERTs in eine Detailtabelle, die per Foreign-Key Constraint mit einer Mastertabelle verknüpft sind, schlagen fehl, wenn am Mastersatz Änderungen gemacht wurden, für die noch kein COMMIT erfolgte

Das muss so sein (ist auch schon immer so),
sonst könntest du dein Detail nicht committen,
da der Master ja mit Rollback wieder verworfen werden könnte.

Das Insert ist im Detail, der Master wird nur in irgendeinem Feld (nicht dem PK) geändert. Daher sollte ein ROLLBACK eines Updates der Mastertabelle kein INSERT im Client blockieren.

Natürlich könnte in der selben Transaktion, die das Update im Master vornimmt, auch ein DELETE des Master-Satzes stattfinden und nach dem COMMIT der Satz für andere Transaktionen unsichtbar werden. Beim Isolationslevel READ COMITTED würde ich aber annehmen, dass erst nach dem COMMIT der Änderungen auf dem Master der Client diese sieht. Also sollte bis zum COMMIT des Masters der Client in der Lage sein ein INSERT auszuführen, da aus seiner Sicht der Master existiert.

Aber immerhin ist nun klar, wie es von InterBase 7 gehandhabt wird.

Der einzige Workaround (ausser dem etwas wackligen "so lange versuchen, bis das INSERT klappt") den ich ausprobieren könnte wäre der Verzicht auf die Foreign Key Constraint in der Detailtabelle. Mich wundert nur, dass dieses Problem nicht öfter beobachtet wird. Vielleicht gibt es zu wenige InterBase Datenbanken, in denen mit Foreign Key Constraint gearbeitet wird. Oder neuere Versionen arbeiten etwas schmerzfreier.

[jobo]

Zitat von mjustin:

Der einzige Workaround (ausser dem etwas wackligen "so lange versuchen, bis das INSERT klappt") den ich ausprobieren könnte wäre der Verzicht auf die Foreign Key Constraint in der Detailtabelle. Mich wundert nur, dass dieses Problem nicht öfter beobachtet wird. Vielleicht gibt es zu wenige InterBase Datenbanken, in denen mit Foreign Key Constraint gearbeitet wird. Oder neuere Versionen arbeiten etwas schmerzfreier.

Damit hättest Du ja alles über Bord geworfen, das den Sinn des Constraint ausmacht.
Ich vermute, dass Constraints relativ wenig genutzt werden, spätestens wegen Problemen wie diesem (oder weil der Entwickler sich noch an sowas erinnert, auch wenn es ein anderes / älteres System war). Hinzu kommt, dass eine fachliche Komponente mitschwingt. Wenn die Software irgendwelche Sachbearbeitung macht und das hängt an einer Vertragsnummer oder Rechnungsnummer oder so, ist ein Konflikt relativ unwahrscheinlich.
Geht es um Produkt oder Bestelldaten, und es wird bspw. die items on stock property häufig geändert, dürften viel mehr solche Fälle auftreten.
Ein filigranerer Sperrmechnismus würde auch helfen. Vielleicht geht es ja bei neueren Versionen.

[mjustin]

Zitat von jobo:

Damit hättest Du ja alles über Bord geworfen, das den Sinn des Constraint ausmacht.

Ja, eine Retry-Schleife ist dem sicher vorzuziehen. Leider sind davon mehrere Anwendungen betroffen, zum Teil laufen die auf Hintergrund-Servern ohne Oberfläche.