SQL Daten lesen während SQL Transaction

Magic1416

Lieutenant
Registriert
Dez. 2003
Beiträge
513
Hi,

Ich entwickle gerade ein Programm in C# welches häufig auf eine SQL Datenbank zugreift.
Überwiegend erfolgt der Zugriff lesend.
Es kann jedoch vorkommen, dass bestimmte Objekte in der Datenbank aktualisiert werden müssen. Ein Objekt besteht aus mehreren Datensätzen in verschiedenen Tabellen mit 1:n und m:n Beziehungen.

Die Aufgabe ist folgende:
Ein User kann ein Objekt aktualisieren. Gleichzeitige aktualisierungen des selben Objekts durch mehrere User soll unter allen Umständen nicht möglich sein.

Mein Ansatz:
In C# wird eine Transaktion erzeugt. Die Änderung werden vorgenommen. Dieser Vorgang kann durchaus je nach größe bis zu 60 sek. dauern. Mit Commit wird der Vorgang abgeschlossen. Soweit funktioniert das auch.

Es kann aber vorkommen, dass ein User das Objekt lesen will, während es aktualisiert wird. Für den Lesevorgang verwende ich bisher keine Transaktion.

Ich hätte in diesem moment zwei Scenarien erwartet, mit denen ich, egal welche von beiden auftritt, zufrieden wäre.

1. Der Lesevorgang beginnt und gibt das Ergebnis zurück, welches vor Beginn der Transaktion aktuell war.

2. Der Lesevorgang beginnt und wartet, bis die Transaktion mit Commit oder Rollback abgeschlossen wird, und gibt dann das Ergebnis zurück.

Keines von beiden Fällen trifft aber zu.
Der Lesevorgang beginnt und wartet, bis die Transaktion abgeschlossen ist. Allerdings gibt es dann kein Ergebnis sondern eine Fehlermeldung vom SQL Server das der Thread xy einen DeadLock aufweist und der Vorgang abgebrochen werden musste. Man solle doch diesen nochmal wiederholen.


Weiß jemand, wie oder was ich machen muß damit bei dem Select auf gelockte Datensätze keine Fehlermeldung erscheint ? Sowas wie Fall 1 oder noch lieber Fall 2 wäre mir ganz recht.


Gruss Magic
 
Du musst die DB Für Lese - und Schreibezugriff sperren (außer den einen User der gerade ein Aktion ausführt). Hat er die aktion ausgeführt können andere Aktionen der anderen User fortgesetzt werden.

Wie genau das geht hängt von der DB engine ab. Kann leider nicht mehr sagen... würde aber gerne die Lösung auch wissen, weil es mich selbst interessiert!
 
Unabhängig von den möglichen 2 Lösungen, hätte ich noch eine 3. Variante, die da ab und zu über den Weg läuft. Also SQLServer kennt einen Typ namens TIMESTAMP. Dieser wird jedesmal aktualisiert, wenn man ein UPDATE macht, beim INSERT wird er das erste Mal gefüllt. (Jeweils automatisch von SQLServer erledigt)

Nun zur Theorie:
Man macht in jede der Tabellen eine Spalte mit diesem Typ rein.
Beim Update wird diese Spalte in der WHERE Bedingung ergänzt

Jetzt gibt es 2 Möglichkeiten:
1. der TIMESTAMP ist unverändert, somit kann das Update ausgeführt werden
2. der TIMESTAMP hat sich durch ein UPDATE von einem anderen User geändert
-> .Net löst eine DBConcurrency Exception aus, deine Applikation könnte nun genau diese Exception abfangen und dem User sagen "Hallo der Datensatz kann nicht gespeichert werden, weil er veraltet ist, bitte refreshe und versuchs nochmal..."

Somit hast du es nicht zwingend nötig, über Transaktionen, Datensätze zu locken und ein anderer User kann weiter die Daten abfragen.

Das würde ich jedenfalls so machen, wenn für jede Zeile ein UPDATE ausgeführt wird. Wenn du jedoch mit einem UPDATE gleich mehrere Datensätze änderst, dann wirst du mit den Transaction arbeiten müssen. Schau einfach in die SQLServer Doku...
 
Zuletzt bearbeitet:
Hi,

bin die letzen zwei Tage nicht untätig gewesen und habe experimentiert und kann glaub ich, einen Erfolg verbuchen.
Als Backend wird SQL Server 2005 Enterprise verwendet. Der Infrastrukturcode ist hier komplett selbst entwickelt. Das EntityFramework oder Linq to SQL bietet sich in meinem Fall nicht an.

Meine Lösung arbeitet jetzt ausschließlich mit Transaktionen , auch dann, wenn nur Daten gelesen werden.

Für den Client, der gerade ein Objekt schreibt und die Daten sperrt:

Code:
SQLCommand c = _dbconnection.CreateCommand(); //Command Object aus dem ConnectionObject erzuegen
SQLTransaction t = _dbconnection.BeginTransaction(IsolationLevel.Serializeable); //Transaction Object erzeugen
c.Transaction = t;  //TransactionsObject dem oder den Commands zuweisen
c.CommandText = "Hier steht ein Query";
c.ExecuteNonQuery();

usw. usw. mit Parametern, Prozeduren, Readern und Rekursionen

Dies sind die .Net Objeke die ich zum Aktualisieren der Daten benötige. Den Isolationlevel habe ich auf Serializable gesetzt, damit kein zweiter Client die selben Datensätze verändern kann, noch bevor die erste Transaktion überhaupt Daten verändert hat. Dies könnte dann der Fall sein, wenn der Client die Daten ermittelt, die sich überhaupt ändern könnten. Daher darf kein zweiter Client denselben Versuch starten.

Während des Lese, Analyse und Update Vorgangs erhalten alle weiteren Command Objekte, die in diesem Zusammenhang stehen, das selbe Transaction Object wie oben erzeugt. Auf keinen Fall darf ein neues Objekt erzeugt werden.

Beendet wird die Transaktion mit:

Code:
t.Commit();

Damit sind die Datensätze wieder aktuell und freigegeben.

Nun zum interessanten Teil:
Das Lesen des Objektes, welches gerade aktualisiert wird.
Hierzu ist es wichtig, dass wie oben, eine Transaktion erzeugt wird, obwohl gar keine Daten verändert werden. Macht man das nicht, hab ich mein Deadlock Problem.

Den IsolationLevel dieser Transaktion setzte ich auf "ReadUncommited".
Das bedeutet, dass selbst die gelockten Daten gelesen werden, obwohl diese durch die andere Transaktion gesperrt sind. Dies geschieht sofort. Damit gehe ich allerdings das Risiko ein, dass mein Objekt falsche oder unvollständige Daten anzeigt. In meinem Fall kann ich das Risiko eingehen.

Alternativ kann man für dies Transaktion den IsolationLevel auf "ReadCommited" setzen. Dies bedeutet, dass der Reader wartet, wenn die andere Transaktion bereits eine Änderung vorgenommen hat. Nachdem die schreibende Transaktion committed wurde, setzt die Lesende Ihren Vorgang fort ohne abzustürzen.

Will man es ganz restriktiv, dann auch hier "Serializable" einstellen. Dann muss er auf alle Fälle warten bis die Datensätze freigegeben sind, egal ob diese verändert wurden, oder nicht.

Gruss Magic

@Rossibaer: Dein Lösungsansatz könnte vielleicht klappen. Ist aber in dem Entwicklungsstadium in dem sich die SW befindet nicht mehr so einfach einzubauen.
 
Zuletzt bearbeitet:
Danke das du so ausführlich beschrieben hast, wie du das ganze nun aufziehst. Werde bei Gelegenheit das bei meinen "Projekten" etwas näher betrachten...

Grüße
 
Hmm ich weiß nicht. Serializable, so schön es auch ist vollständiges ACID zu haben, ist der Killer jeder Nebenläufigkeit.
In der Regel lassen sich wesentlich bessere Lösungen bauen, die nicht alle anderen User ausschließen. Ich kenn mich mit MSSQL jetzt nicht so bombig aus - aber gibt es eine Möglichkeit vor dem Beginn der Transaktion zu sagen, auf welche Tabellen die Transaktion in welchem Modus (lesend/schreibend) zugreifen soll und die DB übernimmt dann das Lock-Management?

Nebenbei dann aber mit anderen Transaktionen im Dirty Read (Read Uncommited) zu arbeiten, zerstört die gewonnene Sicherheit durch ein Serializable sofort wieder. Merkt man erst, wenn man sporadisch "komische" Ergebnisse hat. Ich möchte Dir, Magic1416, nicht zu nahetreten, aber das klingt nach einem riesengroßen Designfehler/Hack, der die Locksymptome behebt und das Problem nicht angeht. Das Problem wird später mit inkonsistenten Datensätzen viel viel schlimmer zurückschlagen. Die Deadlocks waren schon das richtige Symptom für ein Problem, aber die Lösung ist falsch.
 
7H3 N4C3R schrieb:
Hmm ich weiß nicht. Serializable, so schön es auch ist vollständiges ACID zu haben, ist der Killer jeder Nebenläufigkeit.
In der Regel lassen sich wesentlich bessere Lösungen bauen, die nicht alle anderen User ausschließen. Ich kenn mich mit MSSQL jetzt nicht so bombig aus - aber gibt es eine Möglichkeit vor dem Beginn der Transaktion zu sagen, auf welche Tabellen die Transaktion in welchem Modus (lesend/schreibend) zugreifen soll und die DB übernimmt dann das Lock-Management?

Ja sowas gibt es.... steht im beitrag von @bernig

Hier nochmal für 2008 auf deutsch! hier
 
@roker002: Vielleicht bin ich ja blind, aber ich finde dort nur, wie ich den Isolation-Level setzen kann.
 
@7H3 N4C3R

Das mit dem Serializable ist sicher so ne Sache. Wenn es für meine Aufgabenstellung eine bessere Lösung gibt, ändere ich sofort das Design.

Die Aufgabenstellung ist folgende:
Die Company in der ich arbeite ist weltweit vertreten. Demensprechend Groß ist deren Active Directory.
Zu Auswertungszwecken in Bezug auf deren Softwareverteilsystem, benötige ich die Teile aus dem AD, welche mit der Softwareverteilung in Zusammenhang stehen. Ich habe eine Routine geschrieben, die das AD an den entsprechenden Stellen "abgrasst" und die daraus benötigten Informationen in die DB mit einer circle-Relation importiert. Die Infos aus dem SW-Verteilsystem fliessen später ein. Sind die Daten alle drin, kann ich natürlich sehr schnell Auswertungen fahren und Fehler in diesem riesigen Konstrukt finden.

Mein Tool beginnt die Suche im AD an einer definierten Stelle und durchforstet jedes Objekt hierarchisch nach unten. Dabei werden auch die Members als auch die MemberOfs betrachtet. Durch die Circle Relation ergibt sich das Konstrukt aus Member und MemberOf quasi von allein, indem ich lediglich die Members eines Objektes importiere. Die Organisation der Objekte im AD ergibt sich aus der Guid des Objektes und dessen ParentGuid.

Die Objekte werden also nach und nach in die DB gelesen. Das dauert natürlich dementsprechend lange.

Es gibt aber auch schon hier das erste Problem, wenn bei allen folgenden Imports Objekte aus dem AD entfernt wurden oder lediglich das Membership gelöst wurde.
Während des Imports weiss man ja das nicht, weil man lediglich den Ist-Zustand hat.

Daher habe ich eine Art Flag in der Datenbank.
Diese werden vor dem Import eines Objektes auf 0 zurück gesetzt. Dann erfolgt der Import welches für jedes importierte Objekt den Flag auf 1 setzt.
Alles was nach dem Import übrig bleibt, dessen Flag auf 0 geblieben ist, kann aus der Datenbank raus.
Dies kann aber erst erledigt werden, wenn das AD Objekt fertig gelesen wurde.

Ich muss daher unbedingt (und daher serializable) verhindern, dass jemand das selbe AD Objekt zum selben Zeitpunkt aktualisieren will. Falls jemand eine bessere Lösung hat, als mit Flags zu arbeiten um Datensätze zu löschen, bitte her damit.

Und genau aus diesem Grund kann ich eigentlich auch den Dirty Read zulassen. Im schlimmsten Fall, hat derjenige, der eine Auswertung auf dieses Objekt mit dem Softwareverteilsystem machen will entwender zu viele oder zu wenige Objekte in der DB. Auf das Feedback muss ich allerdings noch warten, ob die, die es nachher verwenden müssen, damit leben können, oder dem ganzen Ergebnis nicht mehr trauen.

Gruss Magic
 
Zuletzt bearbeitet:
Ich würde den Import neuer Daten in eine temporäre Tabelle machen und dann über "Drop table" und anschließendem Umbenennen einen harten Switch in einer ganz kurzen Transaktion vollziehen. Falls bei einem Update nicht alle Objekte kommen sondern nur ein Teil (also wenn du bislang nur ausgewählte Sätze mit 0 markierst) muss man eben statt droppen den Insert schnell machen:
delete from bla where <bisheriges Kriterium für 0>
insert into bla Select * from tmp;

Man könnte auch über eine Historisierung nachdenken (Gültigkeitszeiträume mitspeichern) aber scheinbar wird das eh nicht gebraucht so wie ich dich verstanden habe...
 
Ich würde mich BerniG anschließen. Und zwar, wenn das Auslesen so lange dauert, dann die Daten vorerst in eine temporäre Tabelle einlesen. Erst nach Abschluß dieser Aktion, die Daten in der Zieltabelle aktualisieren.

Abweichend würde ich aber nicht die Tabelle durch die Temp-Tabelle ersetzen, weil u.a. die GRANTS verloren gehen genauso wie Trigger, Indizes und dann wieder neu erzeugt werden müssen. Nehmen wir zum Beispiel mal an, dass du eine 2. Tabelle "SOURCE" mit der gleichen Struktur hast, dann mache doch eine Transaktion ala:

BEGIN TRANSACTION
DELETE FROM TARGET
INSERT INTO TARGET() SELECT ... FROM SOURCE
IF @@ERROR = 0 COMMIT TRANSACTION
ELSE ROLLBACK TRANSACTION

TRUNCATE SOURCE -- nicht zu vergessen, die brachiale Methode bei SQLServer um alle Daten einer Tabelle zu entfernen. Es wird dabei nichts in den entsprechenden Transaktionlogs gespeichert(!), sollte aber nicht in der Transaktion auf der TARGET Tabelle angewendet werden. Unmittelbar vor dem Import aus AD in die SOURCE Tabelle wäre sicher eine geeignete Position für diesen Befehl.
 
Zuletzt bearbeitet:
Zurück
Oben