Heute war schon alleine deswegen ein guter Tag, weil mir jemand eine kluge Frage zu SQL stellte. Manche Dinge vergisst man ja wieder, aber ich weiß noch genau, wie wenig ich zu Beginn meiner Beschäftigung mit Datenbanken verstand, warum bei verschachtelten Transaktionen zwar genau so viele COMMITs wie BEGIN-TRANSACTIONs nötig sind (leuchtet ein), aber ein ROLLBACK immer bis zum Beginn der äußeren Transaktion zurück rollt… 🙂
Als ich heute einem Kollegen auf diese Frage antwortete, fand ich in "meinen" TSQL-Richtlinien neben einer etwas langweiligen Erklärung den Hinweis (sinngemäß):
Verschachtelte Transaktionen sind im günstigsten Fall wirkungslos und sollten daher nicht eingesetzt werden.
Dabei fällt mir noch etwas ganz anderes ein: Hat eigentlich schon jemand mal einen sinnvollen Einsatz für Savepoints gefunden? Unsere Anwendungen haben zwar ein paar längere Transaktionen, aber wir kämpfen dann meist mit den Nebenwirkungen, insbesondere der Lock-Escalation. Aber ich bevorzuge viele kleinere Transaktionen hintereinander… Geschmackssache?
Hallo Thomas,
man muss nur den Gedanken über Bord werfen, das es geschachtelte Transaktionen gibt. Denn, es gibt sie nicht. Bei einer Transaktion wird sequentiell protokolliert. Da kann man sequentiell vor- und zurückspulen, aber nichts aus der Mitte herausschneiden, was bei einer Schachtelung ja möglich sein müsste. Daher reicht es auch, wärend einer Transaktion einfach nur alle begin trans zu zählen (@@trancount), anstatt die einzelnen commits dem entsprechenden begin tran zuzuordnen. Ein commit macht daher nichst anderes, als den Transaktionszähler um 1 zu erniedrigen. Ein rollback sagt nichts anders als bis zum Anfang, oder – wenn angegeben – bis zu einem gesetzten savepoint zurückzuspulen. Da ist auch nichts geschachtelt, es ist also unbedeutend ob nach dem Setzen des savepoints weitere begin trans erfolgt sind. Um die Transkation erfolgreich abzuschliessen, muss der Transaktionszähler einfach bei 0 sein. Das geschieht durch rollback bis zum Anfang (nicht durch Rollback bis zum Savepoint) oder entsprechend viele commits.
Ein Klassiker zum Beispiel bei Einrichtungsskripten ist, im Fehlerfall einfach kein commit zu machen. Wenn am Ende des Skriptes der Transaktionszähler nicht auf 0 steht, dann rollback (für Feinde der Fehlerbehandlung 😉 )
Zum Einsatz eines savepoints als Beispiel eine stored procedure die beim Aufruf prüft, ob eine Transaktion aktiv ist, wenn nein, eine Transaktion öffnet, wenn ja einen savepoint setzt, um im Fehlerfall nur die Datenänderungen der stored procedure rückgängig zu machen.
Ob viele kurze Transaktionen hintereinander oder eine lange Transaktion ist keine Frage des Geschmacks, sondern der Konsistenz. Also die kürzestmögliche Transaktion, die die Datenbank im Fehlerfall in einem konsistentem Zustand hält.
Hier noch ein kleines Skript hierzu:
create table dbo.trantest(id int primary key clustered not NULL)
begin tran
insert into dbo.trantest select 1
begin tran
insert into dbo.trantest select 2
save tran Merker1
begin tran
insert into dbo.trantest select 3
begin tran
save tran Merker2
insert into dbo.trantest select 4
begin tran
insert into dbo.trantest select 5
select * from dbo.trantest
select @@trancount
rollback tran Merker2
select * from dbo.trantest
select @@trancount
rollback tran Merker1
select * from dbo.trantest
select @@trancount
commit
commit
commit
commit
commit
select * from dbo.trantest
select @@trancount
drop table dbo.trantest
Viele Grüße
Christoph
Hallo Christoph,
eine Antwort hat mich beeindruckt. Danke.
Das Problem mit dem Rollback hatten wir seinerzeit als wir (Sybase SQL Server System 10 einsetzend) den frisch von Sybase aufgekauften Watcom SQL Server (später Sybase SQL Anywhere) testeten. Hier war es so, dass nur der ANSI-Transaktionsmodus verfügbar war: jeder Befehl öffnete eine Transaktion. Am Ende fehlte deswegen wenigsten ein COMMIT und nach dem Ende unserer Anwendung wurde alles zurück gesetzt. Das war ein schöner Aha-Effekt. Aber fürs Testen war das schon klasse… 😉
Dein Beispiel mit Savepoint leuchtet mir ein. Danke. Dem kann ich zustimmen und jetzt sehe ich den Vorteil. Ich tendiere allerdings dazu in so einem Fall in der Unter-Prozedur den Trancount zu prüfen und nur dann eine Transaktion zu öffnen, wenn noch keine offen ist. Damit ich mit dem Commit das gleiche tun kann, muss ich mir blöderweise den alten Trancount merken.
Deine Einschätzung bzgl. der Länge von Transaktionen kann ich nicht hundertprozentig zustimmen. Leider erlebte ich beispielsweise mehrfach, dass komplette Datentrafos als eine Transaktion durchgeführt wurden, um im Fehlerfall alles wieder zurücksetzen zu können. Oder Datenimporte wurden als eine Transaktion durchgeführt. Hier war es in der Regel erheblich schneller die Konsistenz selber durch andere Mechanismen sicherzustellen, insbesondere wenn das im laufenden Betrieb durchgeführt werden sollte. Zumal meistens ja gar kein komplettes Rücksetzen nötig ist, sondern in diesen Fällen "nur" an der Stelle des Abbruchs wieder aufgesetzt werden muss. Wenn man das anders sicher stellen kann als durch eine große Transaktion, dann hat das schon seine Vorteile. Vielleicht gilt das nicht, wenn man Admin ist und nur eine einzige Datenbank betreut. Wir entwickeln Standardsoftware, die bei sehr, sehr vielen Kunden im Einsatz ist.
Viele Grüße
Thomas
Hallo Thomas,
konsistent heißt ja quasi in einem definiertem Zustand. Wenn man mit vielen kurzen Transaktionen arbeitet, um bei Abbruch wieder aufsetzen zu können, widerspricht dem das ja nicht, denn bei Abbruch ist die Datenbank in einem definierten Zustand, nämlich ab da wieder aufsetzen. In einem solchen Falle fängt ja keiner an, mit der Datenbank parallel zu arbeiten, sondern der Prozess wird wieder aufgesetzt (Batch).
Wenn aber bei einem schreibenden Zugriff ein Fehler auftritt, der nicht zu einem Zustand führt bei dem wiederholt werden kann, muss rückgängig gemacht werden (schreibender Zugriff durch Benutzer bei mehreren Benutzern).
Wenn im zweiten Fall die Transaktionen zu lange dauern und es damit zu Blocking Problemen kommt muss man auf der Anwendungseite und/oder ans Datenbankdesign ran. Read committed snapshot ist auch nicht der Weisheit letzter Schluß, hier mal ein Link für eines der möglichen Probleme
http://blogs.msdn.com/mssqlisv/archive/2009/02/02/zeroing-in-on-blocking-on-seemingly-unrelated-tables.aspx
Man muss ordentlich Gehirnschmalz einsetzen, wenn man zukunftsicher die Datenbanken und Anwendungen designen möchte – viel mehr als so manche Frameworks einen glauben machen möchten…
Viele Grüße
Christoph
Hallo Christoph,
den Artikel und auch das darin beschriebene Problem kannte ich noch nicht. Danke!
Gestern erfuhr ich, dass ein weiteres Produktteam aktuell mit Snapshot-Isolation experimentiert. Da kommt mir das sehr gelegen. der Job meines Teams ist quasi internes Consulting zu solchen Fragen…
Viele Grüße
Thomas
Hallo Thomas,
Craig Freedman ist dir ja ein Begriff, aber kennst du auch den Artikel?
http://blogs.msdn.com/craigfr/archive/2007/05/16/serializable-vs-snapshot-isolation-level.aspx
Hier wird einfach dargestellt, was beim Anwendungsdesign im Snapshot Isolation Level bedacht werden muss.
Bestimmt für den einen oder anderen ein interessantes AHA Erlebnis 😀
Bezüglich der verschiedenen Transaction Isolation Levels und deren komplexen Auswirkungen werden sich die wenigsten Gedanken gemacht.
Daraus entstehen die Fehlersituationen, die unter die Rubrik "unerklärbar, ist halt so" fallen – oder einfach gar nicht bemerkt werden…
Eigentlich seltsam, Anwendungsentwickler machen sich hier seltenst Gedanken drüber, wärend sie sich bei Multi Threading in den Anwendungen den Kopf zerbrechen…
Viele Grüße
Christoph