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

[jobo]

Was ich da irritierend finde ein "Lock" conflict bei insert.
1. Das kann vorkommen, wenn die DB kein Row Level Locking kann.
Weiß nicht, wie das da bei IB und genau dieser Version aussieht.

2. Die zugehörige Fehlermeldung (seitens Firebird) spricht in der Erläuterung von Updates oder Deletes, nicht von Inserts.

Daraus ergibt sich die Frage, fliegen hier nebenläufig noch andere Anweisungen rum?
Ist das "dynamisch" generierte Statement tatsächlich korrekt? (Werden die ID verwendet (Master), die auch gewünscht sind?
Oder bestehen beim Master oder Detail Tabelle andere Abhängigkeiten in Form von Triggern oder cascading constraints, die zu diesem Problem führen (bspw. Selbstreferenz, ...)

(Quelle http://www.firebirdfaq.org/faq109/ -hab mir nicht die Mühe gemacht, nach original IB Doku zu suchen, ist also vielleicht irreführend, was da steht)

P.S.: Der Isolation level sollte m.E. ok sein.

[mjustin]

Zitat von jobo:

Daraus ergibt sich die Frage, fliegen hier nebenläufig noch andere Anweisungen rum?
Ist das "dynamisch" generierte Statement tatsächlich korrekt? (Werden die ID verwendet (Master), die auch gewünscht sind?
Oder bestehen beim Master oder Detail Tabelle andere Abhängigkeiten in Form von Triggern oder cascading constraints, die zu diesem Problem führen (bspw. Selbstreferenz, ...)

(Quelle http://www.firebirdfaq.org/faq109/ -hab mir nicht die Mühe gemacht, nach original IB Doku zu suchen, ist also vielleicht irreführend, was da steht)

P.S.: Der Isolation level sollte m.E. ok sein.

Da multi-user, fliegen einige andere Anweisungen rum. Die sind in anderen Prozessen, im aktuelle Prozess laufen keine Threads o.ä. mit Datenbankzugriff. Als weitere Absicherung prüft das Programm vor dem INSERT, ob der als FK einzutragende Wert tatsächlich in der MASTER Tabelle "sichtbar" ist (existiert).
In 99,9 Prozent aller Fälle funktioniert das INSERT. Einen Fehler im Statement kann ich daher beherzt ausschliessen.

Es gibt nur einen Trigger (BEFORE INSERT) in der Detail-Tabelle. Er sorgt für einen vorhandene Primary Key in der Detail-Tabelle, und füllt ein Feld mit dem aktuellen Usernamen der Connection.

if (new.detail_pk is null) then
new.detail_pk = gen_id(gen_detail, 1);

if (new.DB_USER_NAME is null) then
new.DB_USER_NAME = user;

Da das Problem nur selten auftritt, halte ich es für wahrscheinlich dass Aktionen die in einer anderen Transaktion durchgeführt werden - z.B. der MASTER Datensatz wurde in einer anderen Transaktion geändert, aber noch nicht comitted - verantwortlich sind. Ich schaue mal ob ich etwas reproduzierbares finde.

[jobo]

Zitat von mjustin:

...snip
Als weitere Absicherung prüft das Programm vor dem INSERT, ob der als FK einzutragende Wert tatsächlich in der MASTER Tabelle "sichtbar" ist (existiert).
...snip

Wie wird denn geprüft, ist das ggF. zu "grob", also entsteht erst dadurch das read lock?
Oder hast Du das bei den Querchecks von vorhin erst gar nicht "nachgeahmt"?

Die Prüfung ist ja doppeltgemoppelt, der FK Constraint soll das ja abfackeln. Besser wäre hier wahrscheinlich, es knallen zu lassen bzw. die database exception zu fangen und zu behandeln (wie auch immer)

[mjustin]

Zitat von jobo:

Zitat von mjustin:

...snip
Als weitere Absicherung prüft das Programm vor dem INSERT, ob der als FK einzutragende Wert tatsächlich in der MASTER Tabelle "sichtbar" ist (existiert).
...snip

Wie wird denn geprüft, ist das ggF. zu "grob", also entsteht erst dadurch das read lock?
Oder hast Du das bei den Querchecks von vorhin erst gar nicht "nachgeahmt"?

Die Prüfung ist ja doppeltgemoppelt, der FK Constraint soll das ja abfackeln. Besser wäre hier wahrscheinlich, es knallen zu lassen bzw. die database exception zu fangen und zu behandeln (wie auch immer)

Ja, die Prüfung kann wieder heraus, sie stammte noch aus der Zeit einige Minuten vor der Aufklärung des Phänomens.

Vielen Dank für die Anregungen und Tips!

[jobo]

Clientseitig ist mir eingefallen, habe ich mal etwas in einem ähnlichen Zusammenhang implementiert, um ähnliche Probleme zu verhindern. Eine Monstermaske mit Master und mehreren Detaildatasets.
Man konnte anfangs einen neuen Mastersatz ungeposted stehen lassen und Details eintragen, bis zum post natürlich, der ging nicht, weil der Masterkey nicht da sein konnte.
Ich habe dann schließlich so umgebaut, dass bei Verlassen (GUI) des Mastersatzes nachgefragt wurde, ob man nicht posten möchte. "Verlassen" kann dabei unterschiedlich komplex sein.

Hilft natürlich nichts, wenn Dein Problem auf unterschiedlichen System basiert. Aber wenn es so ist wie es ist, spricht m.E. auch nichts dagegen, beim Posten (Fehlschlag) eine entsprechende Meldung auszugeben:
"Der .. hat es seit 2h noch nicht geschafft, auf "Commit" zu drücken und blockiert alles, sie finden ihn in Büro xy 2.Etage.. ."

Naja so ähnlich

[hoika]

Hallo,

Zitat:

Vielleicht gibt es zu wenige InterBase Datenbanken, in denen mit Foreign Key Constraint gearbeitet wird.

Zitat:

Oder neuere Versionen arbeiten etwas schmerzfreier.

Hallo, ich denke, zweiteres ist richtig.

Das obige Verhalten hatte ich seit FB1.5 nicht mehr beobachtet.

Allerdings sind bei uns die Mastertabellen immer weit früher gefüllt als die Detailtabellen,
und alles schick mit expliziten Transaktionen gekapselt.

Ein Detail-Datensatz kann per Design bei uns nicht angelegt werden, bis der Master seine Transaktion abgeschlossen hat.
Danach wird auf jeden Fall eine neue (Detail-)Transaktion gestartet.

[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.