Transaktionen Concurrency Management in MS SQL Server

Transaktionen
Concurrency Management
in MS SQL Server
Transaktionen in SQL Server
• SQL Server bietet die Möglichkeit, eine Reihe von
Datenbankoperationen (Änderungen) in einem logischen Vorgang zu
gruppieren → Transaktionen
• Transaktionen stellen sicher, dass die ACID Eigenschaften
eingehalten warden
• Für eine gute Effizienz → sollte parallele Transaktionen unterstützen
• Die Operationen unterschiedlichen Benutzers werden als
verschiedene Transaktionen bearbeitet
Transaktionen in SQL Server
• Kontrollieren von Transaktionen:
• Transaktionen werden von Anwendung gesteuert, indem angegeben wird
wann eine Transaktion startet und anwendet
• Default wird jede Operation als separate Transaktion betrachtet
• Möglichkeiten:
• SQL-Anweisungen innerhalb Visual Studio
• Funktionen der ADO.NET oder anderen Datenbank APIs
• Im folgenden werden wir über SQL-Anweisungen reden
Kontrollieren von Transaktionen
• Starten von Transaktionen:
• BEGIN TRANSACTION / BEGIN TRAN
• ROLLBACK TRANSACTION / ROLLBACK TRAN
• COMMIT TRANSACTION / COMMIT TRAN
• SET IMPLICIT_TRANSACTIONS ON – steuert den impliziten Start einer Transaktionen
bei Ausführung einer Anweisung
• Nachdem sich eine Verbindung im impliziten Transaktionmodus befindet, müssen
sie darauf achten, eine Transaktion auch abzuschließen
• SET XACT_ABORT ON – falls ein SQL Error aufgetreten ist, wird die ganze Transaktion
zurückgesetzt (bei OFF wird nur die Anweisung, wo der Fehler aufgetreten ist,
zurückgesetzt)
Transaktionen in SQL Server
• SQL Server kann lokale oder verteilte Transaktionen benutzen:
• Lokale Transaktionen
• SAVE TRANSACTION – man definiert einen Sicherungspunkt → die Position, zu der
eine Transaktion zurückkehren kann
• Eine ROLLBACK TRANSACTION-Anweisung, die den Sicherungspunktnamen angibt,
führt ein Rollback der Transaktion bis zur letzten SAVE TRANSACTION-Anweisung
• SAVE TRANSACTION wird nicht in verteilten Transaktionen unterstützt
• Mit einer ROLLBACK TRANSACTION-Anweisung, in der savepoint_name angegeben
ist, werden alle Sperren freigegeben, die außerhalb des Sicherungspunkts aktiviert
werden
Transaktionen in SQL Server
• Verteilte Transaktionen:
• BEGIN DISTRIBUTED TRANSACTION
• Die Instanz von SQL Server-Datenbankmodul, die die BEGIN DISTRIBUTED
TRANSACTION-Anweisung ausführt, wird als Transaktionsurheber bezeichnet
und steuert den Abschluss der Transaktion
• COMMIT TRANSACTION startet einen von MS DTC (Microsoft Distributed
Transaction Coordinator) koordinierten Commit
Beispiel 1
SET XACT_ABORT ON;
BEGIN TRAN T1
INSERT INTO Studenten VALUES (9,'Ana','Ana');
INSERT INTO Studienvertrage VALUES (10,1);
COMMIT TRAN T1
Beispiel 2
BEGIN TRAN T1
BEGIN TRY
INSERT INTO Studenten VALUES (9,'Ana','Ana');
INSERT INTO Studienvertrage VALUES (10,1);
COMMIT TRAN T1
END TRY
BEGIN CATCH
ROLLBACK TRAN T1
END CATCH
Nebenläufigkeitskontrolle
• SQL Server Trnasaktionen Isolationsstufen lösen wichtige
Nebenläufigkeitsprobleme:
• Verlorengegangene Änderungen (lost updates): tauchen auf, wenn
zwei Transaktionen denselben Objekt ändern
• Dirty Read: taucht auf, wenn eine Transaktion uncommitted Daten liest
• Inkonsistente Analyse (unrepeatable reads): taucht auf wenn ein
existierendes Tupel während der Transaktion vom außen geändert
wird
• Phantom-Problem: taucht auf, wenn neue Daten eingefügt werden
und während einer Transaktionen neu erscheinen
Nebenläufigkeitskontrolle
• SQL Server sichert die Isolationsstufen mithilfe der Sperren:
• Schreibsperren sind exklusiv, Lesesperren sind shared
• Eine gut geschriebene Transaktion fordert die korrekten Sperren
bevor sie diese braucht
• Eine zwei-Phasen Transaktion behaltet die Sperren bis sie alle
nötige Sperren hat
• Isolationsstufen bestimmen wie lange Sperren behalten werden
Sperren in SQL Server
• Die Sperren werden normalerweise dynamisch von dem Sperrmanager verwaltet,
nicht von der Anwendung
• Granularität von Sperren:
• Auf Zeilenebene:
• zeilen-id (rid) – jeweils nur eine Zeile gesperrt
• schlüssel (key) – zeilenebene in einem Index, bezieht sich auf einen Schlüsselwert
•
•
•
•
Auf Seitenebene: seite (pag)
Auf eine Gruppe von 8 zusammenhängenden Date- oder Indexseiten: block (ext)
Auf Tabellenebene: tabelle (tab)
Auf Datenbankebene: datenbank (db)
• Sperren können mehrere Granularitäten haben
• Wie lange wir die Sperre behalten? → bestimmt von der Isolationsstufe
• Bis die Anweisung ausgeführt wird
• Bis die Transaktion beendet wird
Sperren in SQL Server
S
U
X
S
Yes
Yes
No
U
Yes
No
No
X
No
No
No
Sperrmodi:
• Gemeinsam / Shared (S) – nur-Lese-Sperre
• Exklusiv (X) – wenn man Daten modifizieren muss. Nicht kompatibel mit anderen
Sperren (außer bei NOLOCK mit der READ UNCOMMITTED Isolationsstufe)
• Aktualisierung / Update (U) – S Sperre die später eine X Sperre wird
• Beabsichtigt / Intent (I) – zeigt an, dass SQL Server eine Sperre weiter plant
(intention to lock)
• Schema (Sch-M, Sch-S) – Schemamodifikation, Schemastabilität → beziehen sich auf
Änderungen des Datenbankschemas (Blockiert nur DDL, nicht DML Anweisungen)
• Massenaktualisierung / Bulk Update (BU) – verhindert den Zugriff auf die Tabellen
wo ein Bulk-Einfügen ausgeführt wird
• Schlüsselbereich / Key Range – sperrt Zeilen basierend auf eine Bedingung
Sperren in SQL Server
• Bevor SQL Server 7.0 konnte man nur
Seiten und Tabellen sperren um
Phantoms zu vermeiden
• Sperrinformationen anzeigen:
sp_lock
go
•
•
•
•
•
•
•
spid – Systemprozess-Id
dbid – Id der Datenbank, die die Sperre anfordert
ObjId – Id des Objektes, das die Sperre anfordert
IndId – Id des Index an
Type – gesperrte Ressourcentyp
Mode – Sperrmodus
Status – gibt an, ob die Sperre momentan erteilt ist (grant), auf einen anderen
Vorgang warten muss (wait) oder zu einer anderen Sperre konvertiert wird (cnvt)
Isolationsstufen
• READ UNCOMMITTED: Sperren werden nur für UPDATE-Befehle ausgelöst
(keine Sperre beim Lesen)
• Bietet die beste Parallelität, erlaubt aber Dirty-Reads, Phantomwerte und nicht
wiederholtes Lesen (unrepeatable reads)
• READ COMMITED: Behaltet die Sperre nur für die Ausführung dieser Anweisung
(Standardeinstellung von SQL Server)
• Vor jedem Lesevorgang muss ein Commit ausgeführt werden → erlaubt keine Dirty-Reads
mehr
• REPEATABLE READ: sperrt die Daten bei Lesevorgängen und behaltet diese bis
am Ende der Transaktion (erlaubt keine unrepeatable Reads mehr)
• SERIALIZABLE: behaltet die Sperren und die Schlüsselbereichsperren während
der ganzen Transaktion
• Transaktionen sind vollständig voneinander isoliert und damit serialisierbar (keine
Anomalien mehr)
• SNAPSHOT: man arbeitet auf Daten Snapshots
Isolationsstufen
• Je geringer die Isolation der Transaktionen, desto besser ist die
Parallelität
→ aber dann können Inkonsistenzen in den Daten vorkommen
• SQL Syntax:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED | READ
UNCOMMITTED | ...
Isolationsstufen
Read
Uncommitted
Read
Committed
Repeatable
Read
Serializable
Lost Updates?
Nein
Nein
Nein
Nein
Dirty Reads?
Ja
Nein
Nein
Nein
Unrepeatable
Reads?
Phantoms?
Ja
Ja
Nein
Nein
Ja
Ja
Ja
Nein
Deadlocks
• MS SQL Server entdeckt Deadlocks
• Default: die jüngste Transaktion wird abgebrochen
• Error 1205 – sollte erfasst werden und richtig behandelt werden
• SET LOCK TIMEOUT – läßt die maximale Zeitspanne ermitteln, für
welche eine Transaktion warten darf, bevor sie abgebrochen wird
• SET DEADLOCK PRIORITY
• Mögliche Werte: Low, Medium, High, numerisch {-10, -9, ..., 0, ..., 9, 10}
• Die Transaktion mit dem kleineren Priorität wird abgebrochen
Deadlock Situationen vermeiden
• Halten sie Transaktionen so klein wie möglich
• Lese die Eingaben von den Users und überpüfe diese vor dem Beginn
der Transaktion (nicht innerhalb einer Transaktion)
• Behalte (wenn möglich) dieselbe Reihenfolge bei dem Zugriff auf die
Daten zwischen den Transaktionen
• Benutze eine kleinere Isolationsstufe
• Zugriff auf so wenig wie möglich Daten in einer Transaktion