SSIS TransactionOption - lesende Connections?

Question

Guten Morgen,

ich beschäftige mich gerade mit der TransactionOption in SSIS und stehe vor einem Problem/einer Frage.

Sagen wir ich habe zwei Server und meinen Entwicklungsrechner. Einer der Server beheimatet das BI-Warehouse, der andere eine Quellendatenbank. Im Grunde völlig egal ob das jetzt eine MSSQL oder z.B. PostgresDB, ein Windows oder Linuxserver ist.

Ich möchte Daten von der Quellendatenbank in das Warehouse transferieren und hierbei eben die TransactionOption = Required auf einem Sequencecontainer nutzen. Im Datenbankverständnis bündeln Transaktionen, einfach gesagt, schreibende (!) Vorgänge die bei einem Fehler so einfach rückgängig gemacht werden können. Soweit so gut. In diesem Sinne habe ich MSDTC zwischen meinem Entwicklungsrechner und dem BI-Warehouse-Server auch korrekt konfiguriert und es funktioniert. Nun zu dem Teil den ich nicht in meinen Kopf bekomme. Wenn der Server auf dem die Quellendatenbank liegt NICHT für MSDTC konfiguiert ist läuft das ganze nicht weil eben keine MSDTC-Verbindung zustande kommt. Aber warum sollte das überhaupt nötig sein?? Ich lese doch nur von der Quelle? Was sollte hier eine Transaktion überhaupt bringen? Es ist doch regelrecht unsinnig MSDTC auf allen Servern zu konfigurieren obwohl die BI hiervon nur liest. Wichtig ist Transaktionalitä doch nur für die Systeme in die man schreibt!? Vor allem geht das (afaik) bei einem Server der auf Linux basiert auch gar nicht. Aber welchen Sinn hat diese TransactionOption dann noch wenn man sich nur innerhalb der Zieldatenbank bewegen aber keine Quellen abfragen kann?

Verstehe ich da irgendwas falsch? Gibt es eine Möglichkeit für eine Connection oder eben OLEDB-Source in einem SequenceContainer mit Transaction zu konfigurieren das hier keine Transaction nötig ist?

Klar kann ich im Zweifelsfall natürlich selber das ganze Nachbasteln mit Begin/Commit/Rollback auf SQL-Ebene. Aber wenn es diese Funktion schon gibt sollte die doch sinnvoll nutzbar sein?

VG

Marco

Answer 1

Auch Lesevorgänge unterliegen der Transaktion da damit die Sichtbarkeit von Daten gewährleistet wird.

Die Transaktion "Read Commited" stellt dir nur Daten zur Verfügung, die vor dem letzten Start deiner Lesetransaktion durch andere Sitzungen commited sind.
Je nach Datenbankimplementation werden
- nicht commitete Daten überlesen
- oder ggf. wird auf den Commit gewartet, ggf. mit Timeout und Abbruch des Lesevorgangs
- oder es wird die vorherige Version der Daten bereitgestellt (Satzversioning)
als Aktion durchgeführt.

Beim SQL-Server wird z.B. der Lesevorgang u.U. geparkt, wenn noch offene Commits anderer Sitzungen für die Tabelle vorliegen

https://docs.microsoft.com/de-de/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver15.

Gerade bei BI-ETL-Prozessen ist dies zu beachten, ob man denn tatsächlich alle aktuellen Daten geladen hat.
Dies ist ins besonders bei Aktualisierung notwendig. Bei Komplettladungen ist dies letztlich egal.

Hinzu kommt die Standardeinstellung bei Read-Commited bzgl. SNAPSHOT!
Da ETL-Vorgänge ja durchaus länger dauernde Prozesse sind kann es zum Stillstand der gesamten Anwendung kommen, da bei Snapshot = Off die Tabelle gegen sämtliche Änderungen gesperrt wird, bis dein ETL fertig ist.
Wenn du allerdings Snapshot einfach umstellst, kann die Anwendung zu unkonsistenten Daten oder seltsamen Verhalten führen. Lesevorgäge erhalten alte Daten und werden nicht mehr angehalten.
Updatevorgänge laufen ggf. auf Fehler da die Daten bereits durch andere Sitzungen geändert sein könnten.
Auf Satzversioning muss eine Anwendung reagieren können, was die meisten i.d.R. nicht können.

Answer 2

Hi,

erstmal vielen Dank für die schnelle Antwort. Das ganze klingt schlüssig, Danke für die Erleuchtung ;)

Bis zum letzten Absatz klingt das alles in Richtung "aktiviere MSDTC auch für die Quellen, das ist eine super wichtige Sache". Mit dem letzten Absatz bekommt das ganze aber wieder eine andere Färbung. So wie ich dich verstehe könnte somit die BI, wenn sie sich mehrmals täglich aktualisiert, über die Transaktionalität im schlimmsten Fall das ganze CRM lahmlegen wenn z.B. die CRM-Datenbank abgefragt wird (SNAPSHOT!)? Aber ohne Transaktionalität wäre das kein Problem? Und ohne Snapshot bin ich wieder genauso weit wie ohne Transaktion?

Tatsächlich ist Read-Commited Snapshot auf der CRM-Datenbank aus. Es ist auch ehrlich gesagt keine Option das anzuschalten.

Wenn ich mit der Transaktionalität für lesende Operationen oben erwähntes Risiko einkaufe das die ganze CRM-Applikation "hängt" bin ich da gerade nicht mehr so begeistert, sofern ich dich da richtig verstanden habe. Ehrlich gesagt wäre es in unserem Szenario auch nicht sonderlich dramatisch wenn die Daten noch 100%ig aktuell sind da ich mit Change-Tracking basierend auf einer DateTime-Spalte zwischen Quelle und BI arbeite. Sprich die Sachen die nicht aktuell sofort mitkommen kämen dann eben später und würden nicht verloren gehen.

Answer 3

Du kannst die Daten ggf. per Stored procedure bereitstellen und in dieser u.U. auf READ UNCOMMITED umstellen.

https://stackoverflow.com/questions/10984731/how-the-transaction-isolation-levels-in-ssis-works

Die einzige Gefahr besteht darin, Daten gelesen weerden können die mit Rollback wieder gelöscht wurden.
Erst mit der nächsten Aktualisierung bekommst du dann wieder einen Stand, der allerdings auch Schmutzdaten enthalten kann.

Es ist halt ein generelles Problem bei SQL-Server, dass eine READ COMMITED-Transaktion die komplette Tabelle gegen Update/Insert/Delete sperrt. Dies gilt sogar, wenn aus der Tabelle ganz andere Bereiche gelesen werden als von anderen Transaktionen gerade bearbeitet werden.
Diese Problematik lässt sich nur mit SNAPSHOT = ON und Rowversions beheben. Dies erfordet jedoch einigen Umdenken bei der App-Programmierung. Die Locks reduzieren sich z.T. erheblich, der Durchsatz erhöht sich, allerdings müssen Updates/Deletes besser überwacht werden.

https://docs.microsoft.com/de-de/sql/odbc/reference/develop-app/optimistic-concurrency?view=sql-server-ver15