InnoDB / Transactions / Rollback aber kein LOCK

[Forumsgast]

Hallo zusammen

Habe eine relativ kritische Operation (sie kann Fehler verursachen), welche einige Daten in meine Datenbank schreibt. Dies dauert auch so seine Zeit (~5 Sekunden). Falls etwas schief läuft, benötige ich einen Rollback von allen Queries.
Ich nutze also InnoDB über Doctrine / Symfony und benutze "beginTransaction" was ein "START TRANSACTION" Query auslöst. Nun ist die Datenbank blockiert für alle weiteren Anfragen während dieser Transaction wie ich leider feststellen musste.

Ich habe bereits nach dem "START TRANSACTION" ein manuelles "UNLOCK TABLES" hinzugefügt, was aber keine Veränderung gebracht hat.

Mein Ziel: Ich will einen Rollback von allen Queries in der Transaction erlauben, muss aber keinen Lock haben auf die Datenbank. WRITE LOCK würde auch reichen, wenns denn mit lock sein muss. Aber aktuell kann ich während der Transaction nicht mal lesen (SELECT).

Nun die Frage: Mache ich was mit der Transaction falsch? Gibt es andere Möglichkeiten um das Feature eines Rollbacks zu bekommen? Kann ich den LOCK irgendwie beeinflussen? (READ COMMIT / UNCOMMITED, völlig wurst, es muss einfach weitergehen für die Applikation/Frontend während diese Operation ausgeführt wird). Hab ich evtl. mit Doctrine ein Problem, weil da halt dutzende von Queries (Hauptsächlich Selects) in der Transaction noch gemacht werden, weil Daten aus Entities nachgeladen werden und somit praktisch alles gelockt wird?

Meine letzte Möglichkeit die ich sehe, wäre manuell den Rollback zu machen, was aber zu Problemen führen kann, wenn der Rollback dann auch noch Errors bringen könnte.

Jemand eine Idee? Aktuell kann ich während der Operation meine Seite nicht mehr aufrufen.

Ubuntu, Mysql version = 5.5.49-0ubuntu0.14.04.1, InnoDB, PHP 5.6.23, Symfony 2.8, Doctrine 2.4.

[Forumsgast]

http://docs.doctrine-project.org/pro...nsactions.html

http://dev.mysql.com/doc/refman/5.7/...on-levels.html

[Forumsgast]

Nun ist die Datenbank blockiert für alle weiteren Anfragen während dieser Transaction wie ich leider feststellen musste.

Ich habe bereits nach dem "START TRANSACTION" ein manuelles "UNLOCK TABLES" hinzugefügt, was aber keine Veränderung gebracht hat.

Mein Ziel: Ich will einen Rollback von allen Queries in der Transaction erlauben, muss aber keinen Lock haben auf die Datenbank. WRITE LOCK würde auch reichen, wenns denn mit lock sein muss. Aber aktuell kann ich während der Transaction nicht mal lesen (SELECT).

.

Vorab: Hab mit Transaktionen bisher noch keine großen Erfahrungen gesammelt und bitte um Nachsicht, wenn ich mich hier reinhänge.

Ist es wirklich so, das mit "beginnTransaktion" die gesamte Datenbank gesperrt ist?
Hab als Test mal eine meiner Testroutinen welche Transaktionen nutzen per sleep künstlich verlängert.
Während die Transaktion läuft, kann ich problemlos über eine zweits PHP-Skript eine nicht an der Transaktion beteiligte Tabelle per Select abfragen.
Nutze jedoch nur PHP + PDO.

[Forumsgast]

Ist es wirklich so, das mit "beginnTransaktion" die gesamte Datenbank gesperrt ist?

Nein. Es wird nur das gesperrt, was relevant für die jeweilige Transaktion ist. Es wäre ungünstig wenn zwei Transaktionen den selben Datensatz aktuallisieren und ähnliches. Das Isolationslevel bestimmt dabei in welcher Form gesperrt wird. Bei repeatable read (default level) verwendet Mysql unter anderem Gap-Locks (Hauptursache für Locking-Probleme), damit werden Lücken zwischen indizierten Werten blockiert. Z.B. sowas in der Art von DELETE/UPDATE ... WHERE id/datum/preis/usw > 10 führt zu solchen Locks und damit müssen alle andere Transaktionen warten die in irgendeiner Form schreibend auf > 10 Zugreifen wollen (auch INSERTS). Diese gap locks werden auch bei solchen Dingen wie DELETE ... WHERE id = '11' gesetzt. Wenn das der "letzte" Datensatz ist, geht der Gap-Lock von 11 bis unendlich und blockiert damit exakt wie das vorherige Beispiel. Ist das nicht der "letzte" Datensatz, blockiert er nur bis zur nächsten id. Diese Gap-Locks können mit read commited umgangen werden, dabei sollte aber klar sein, dass diese nicht aus spaß gesetzt werden! Vorher also genau überlegen, ob es eventuell zu Problemen führen kann. Leider ist das zum Teil gar nicht überschaubar -> daher Transaktionen immer so kurz wie möglich halten!

*edit* Das wichtigste vergessen: Schreibzugriffe ohne Index oder zu unselektiven Index bei repeatable read => kommt quasi einem write lock der Tabelle gleich.

[Forumsgast]

es bietet sich hier an, die SCHREIB-Operation in eine zusätzliche Tabelle zu machen (gleiches Spaltenlayout wie die eigentliche Tabelle)... die kann ja problemlos gelockt werden - hinterher, wenn die Transaktion erfolgreich war, überträgst du nur noch die Daten von der Zusatztabelle in die Tabelle, um die es eigentlich geht - das sollte schnell gehen, so dass es für deine Anwendung kein Problem darstellt.
insert select

dann truncatest du noch die Zusatztabelle und du wärest bereit für die nächste Schreib-Transaktion

[Forumsgast]

erc : Danke für die ausführliche Antwort.

mYkon : Ein Select sollte vom Grundsatz während einer gleichzeitig laufenden Transaktion immer möglich sein.
Habe bei meinen Tests jedoch festgestellt, das die gleichen SELECTs wenn eine Transaktion läuft zum Teil das 100 fache an Zeit benötigen.

Als Ergänzung zu den obigen Links: Wiki zu Transaktionen, Problemen und Isolationsebenen

[Forumsgast]

es bietet sich hier an, die SCHREIB-Operation in eine zusätzliche Tabelle zu machen .. hinterher, wenn die Transaktion erfolgreich war, überträgst du nur noch die Daten von der Zusatztabelle in die Tabelle, um die es eigentlich geht - ..dann truncatest du noch die Zusatztabelle

Sorry und bitte nicht persönlich nehmen, aber das klingt ziemlich finster. Ich rette mich aus einer blockierenden Transaktion, indem ich eine 2. Tabelle aufmache .. und dann später nach der Transaktion Daten daraus zurückkopiere ..?!
Das verletzt gleich mehrere Prinzipien für die ACID Datenbanken geschaffen wurden und bricht am Ende den Sinn der Transaktion.

Ich wäre fast versucht zu sagen, dann lieber von Anfang an kein Begin Transaction statt sowas, aber das ist natürlich auch keine Lösung.
Ich würde mich noch drastischer ausdrücken, aber es gibt ja nicht umsonst Forenregeln. Das Wichtigste: Ich hoffe, dass kein unbedarfter Leser versucht, auf diesem Wege seine Datenverarbeitung "abzusichern".

[Forumsgast]

Es fehlen wohl einfach noch wichtige Details zur Problembeschreibung, um konkrete Hinweise geben zu können.

Habe eine relativ kritische Operation (sie kann Fehler verursachen), welche einige Daten in meine Datenbank schreibt. Dies dauert auch so seine Zeit (~5 Sekunden). Falls etwas schief läuft, benötige ich einen Rollback von allen Queries.
Ich nutze also InnoDB über Doctrine / Symfony und benutze "beginTransaction" was ein "START TRANSACTION" Query auslöst. Nun ist die Datenbank blockiert für alle weiteren Anfragen während dieser Transaction wie ich leider feststellen musste.

Wie sehen die Anfragen aus, wo ein Blockieren der Datenbank vorliegen soll? Sind dies reine Selects ? Laufen diese Anfragen auch innerhalb von Transaktionen?
Wann wird diese "kritische Operation" ausgeführt? Mit jeder Anfrage? In einem Cronjob? Welche Tabellen sind jeweils beteiligt ?