TRY ... CATCH und Transaktionen

Question

Hallo,

ich habe gerade ein Problem mit dem Verhalten eines TRY ... CATCH Blocks das beim Auftreten einer Exception die laufende Transaktion in einen nicht mehr commitbaren Zustand fällt. Ich würde gern wissen ob man dieses Verhalten ändern kann (dahingehend das sich der Transaktionszustand nicht durch eine Exception ändert) oder ob es eine Ansatz gibt das Problem zumindest zu umgehen.

Ok. Was habe ich im Moment. Ich habe eine kleine Struktur aus 2 Tabellen die ich im Bulk updaten will(über die SqlBulkCopy Klasse aus dem .Net Framework). Dazu habe ich eine Stellvertreter-Tabelle mit einem Trigger erstellt auf die der Bulk update läuft. Der Trigger (Instead of Insert) geht dann hin und verteilt die Daten auf meine beiden Tabellen. Wobei eine der beiden Tabellen bereits entsprechende Daten enthalten könnte. Dann bräuchte ich für die 2.te Tabelle nur den PK des Datensatzes. Der entscheidende Teil des aktuellen Triggers als Pseudocode

FETCH NEXT FROM inserted_cursor INTO @Id, @Feld1, @Feld2, ...
WHILE @@FETCH_STATUS = 0
BEGIN
  IF (@Id IS NULL)
  BEGIN            
     BEGIN TRY
        INSERT INTO Tabelle1(Feld1, Feld1) VALUES(@Feld1, @Feld1)   
        SELECT @Id = @@IDENTITY
     END TRY      
     BEGIN CATCH            
        SELECT @Id = Id FROM Tabelle1 WHERE Feld1 = @Feld1 AND Feld2 = @Feld2
     END CATCH                        
  END           
  INSERT INTO Tabelle2(Id, ...) VALUES (@Id, .....)
  FETCH NEXT FROM inserted_cursor INTO @Id, @Feld1, @Feld2, ...
END

Der inserted_cursor wird so zusammengebaut das dieser schon prüft ob in Tabelle1 ein entsprechender Satz gefunden werden kann. Wenn der fehlt wird versucht das nachzuholen. Aus Konkurrenzproblemen kann sich das aber mittlerweile geändert haben, unwahrscheinlich aber möglich. In dem Fall bräuchte ich die ID des dann doch vorhandenen Satzes und würde meine Operationen einfach fortsetzen. Dieser Trigger ist performancekritisch insofern würde ich gern auf großes Locking auf Tabelle1 verzichten.

Deshalb der Ansatz TRY ... CATCH zu verwenden. Seltenes Ereignis und eigentliche Operation(INSERT) im Normalfall sehr schnell. Leider wird halt wenn es zur Key Violation auf Tabelle1 kommt auch die Transaktion beendet.

Was für Möglichkeiten habe ich?

Ralf

Answer 1

Hallo Ralf,

an der Transaktionssteuerung lässt sich nichts drehen... und das ist auch besser so.

Wenn Du bei Deinem Vorgehen bleiben willst[1], so solltest Du mit
INSERT INTO Tabelle (...) SELECT ... WHERE NOT EXISTS (...)
arbeiten, wodurch das Problem vermieden werden kann.

Effizienter (ab SQL Server 2008) wäre aber anstatt des Cusors (langsam) MERGE zu verwenden,
siehe dazu auch Optimieren der Leistung bei MERGE-Anweisungen

Damit erhälst Du setbasierte Einfügeoperationen, die besser sind als einzelne INSERTS
und via [NOT] MATCHED kannst Du dem Problem der Überschneidungen begegnen.

Gruß Elmar

[1] Optimal wird es nicht, da Du voll protokollierst,
siehe Voraussetzungen für die minimale Protokollierung beim Massenimport

Answer 2

Danke Elmar,

Ziel ist auch den 2005er zu unterstützen. Und bisher wollte ich auf versionsspezifische Optimierungen möglichst verzichten(Ist schon schwierig genug verschiedene Datenbanksysteme unter einen Hut zu bekommen) daher fällt MERGE leider aus. Halte ich aber im Hinterkopf.

Werde die Performance eines

INSERT INTO Tabelle (...) SELECT ... WHERE NOT EXISTS (...)

mal prüfen und dann gegebenfallse auf den try..catch verzichten.

Im Moment habe ich den Trigger um ein 'SET XACT_ABORT OFF' ergänzt. Scheint Vordergründig für mein gewünschtes Verhalten zu sorgen. Bin mir bei den Nebenwirkungen aber noch nicht sicher.

Das Vorgehen ist das Ergebnis einiger Performanceexperimente und nicht zwingen. Bin für Alternativen offen. Das zu lösende Problem sind die konkurrierenden Massenupdates die leider nur fast überschneidungsfrei sind. Und das ganze halt mit möglicht optimaler Performance. Alle anderen Aspekte wie Lesbarkeit, Wartbarkeit etc. stehen in diesem speziellen Fall deutlich hinter dem Performanceaspekt zurück.

Gruß

Ralf

Answer 3

Hallo Ralf,

Ob mit oder ohne XACT_ABORT ist auch Geschmackssache - da nicht ganz ohne Seiteneffekte:

http://weblogs.sqlteam.com/dang/archive/2007/10/20/Use-Caution-with-Explicit-Transactions-in-Stored-Procedures.aspx
wenn auch hier der Verursacher - u. a. weil sich TRY CATCH und XACT_ABORT "beissen".

IMHO sollte man durchgängig mit dem einen oder anderen arbeiten,
sonst leidet die Wartbarkeit deutlich, da sich das Verhalten ändert.

Hier kommt hinzu, dass man sich im Trigger bereits innerhalb einer Transaktion befindet.

Wegen der Performance ein relativ frischer Beitrag von Alexander Kuznetsov:
Using XACT_ABORT ON may be faster than using TRY...CATCH

Das Ende des Lebenszyklus für SQL Server 2005 ist mit der Veröffentlichung von SQL Server 2012 absehbar,
da solltest Du Dich weniger nach richten, wenn ihr nicht gerade mit viel Altbestand "beglückt" seid.

Selbst wenn Du Merge nicht einsetzen willst, so wäre der Einsatz der OUTPUT Klausel
günstiger als eine Cursor/Identity Abfrage - vorausgesetzt Du hast keine geschachtelten Trigger.

Gruß Elmar

Answer 4

Hallo Ralf Jansen,

Ich gehe davon aus, dass die Antworten Dir weitergeholfen haben.
Solltest Du noch "Rückfragen" dazu haben, so gib uns bitte Bescheid.

Grüße,
Robert

---

Robert Breitenhofer, MICROSOFT 
Bitte haben Sie Verständnis dafür, dass im Rahmen dieses Forums, welches auf dem Community-Prinzip „Entwickler helfen Entwickler“ beruht, kein technischer Support geleistet werden kann oder sonst welche garantierten Maßnahmen seitens Microsoft zugesichert werden können.