SQL Server Paralleles Arbeiten und Transaktionen

Paralleles Arbeiten

Wie funktioniert in SQL Server, das parallele abrufen und ändern von Daten? SQL Server bedient sich hierbei bestimmter Techniken, die dem Anwender das parallele Arbeiten ermöglichen. Dies funktioniert sehr gut, jedoch gibt es bestimmte Zustände die auftreten können und mit den parallelen Zugriffen ist es ganz schnell vorbei bzw. Befehle werden blockiert oder dauern überdurchschnittlich lange.

In diesem Artikel wird Ihnen gezeigt, wie SQL Server grundsätzlich das parallele Arbeiten ermöglicht welche Techniken und Begriffe es hierbei gibt und worauf Sie achten sollten um SQL Server optimal und performant arbeiten kann.

Mal ganz einfach gesagt:

Nehmen wir einmal an ich persönlich wäre SQL Server. Ich hätte eine Datenbank mit vielen Daten zu verwalten und ich wüsste genau, dass mehrere Anwender auf die Daten zugreifen wollen, da diese sich ja soeben bei mir angemeldet haben. Es ist also davon auszugehen, dass jeder angemeldete User mir (also SQL Server) SQL Befehle schickt, damit ich diese Befehle ausführe.

Als SQL Server muss ich natürlich dafür sorgen, dass wenn Sie ich gerade eine Änderung für einen User an den Daten durchführe, ich genau aufpassen muss, damit nicht gleichzeitig Befehle von einem anderen User ausgeführt werden können die eventuell die Änderungen überschreiben.

Was würden Sie tun um z.B. einen Benutzer daran zu hindern, die Daten zu ändern die gerade ein anderer Benutzer ändert?

Also wenn ich SQL Server wäre und Sie mir die SQL Kommandos zurufen würden, täte ich es so machen.

Wenn ich Datensätze hinzufügen müsste, würde ich sie in die zugehörige Tabelle schreiben und bis ich fertig bin, würde ich diese Daten mit einer Notiz (Sperre) belegen, damit niemand anderes auf diese Daten zugreift bis ich fertig bin.

Wenn ich Datensätze ändern müsste, würde ich diese zuerst suchen und diese dann anfangen der Reihe nach zu ändern. In diesem Fall, würde ich auf diese Daten auch eine Sperre setzen bis ich fertig bin.

Wenn ich Daten lösche, so suche ich die Daten zuerst. Dann setze ich eine Sperre darauf und beginne mit dem löschen.

Wenn ich Daten nur finden muss, da mir eine Select Abfrage zugeschickt wurde, werde ich die Daten suchen und die gefundenen Daten markieren (mit einer kleinen Sperre), damit Sie mir die Daten nicht ändern können die ich gerade passend zu ihrer Select Abfrage ausliefere.

Natürlich werden alle Sperren sofort nachdem ich fertig bin wieder gelöscht und können durch die Befehle eines andren Users wieder gesetzt werden wenn denn die gleichen Vorgänge durchgeführt werden sollen.

Nach dem oben gerade beschriebenen Prinzip durch Setzen von Sperren, arbeitet SQL Server auch tatsächlich in der Standardeinstellung.

Um die Zusammenarbeit in einem Multiuser Datenbanksystem zu gewährleisten, gibt es allerdings erst einmal zwei grundlegende Ansätze.

Diese Ansätze unterscheiden sich dahingehend wie Sperren gesetzt werden und zu welchem Zeitpunkt überhaupt änderungen etc. zugelassen werden.

Es handelt sich um Pessimistic Locking (pessimistisches Sperren) und Optimistic Locking Model.

Pessimistic Locking

Beim pessimistischen Model werden, bevor mit den Daten gearbeitet wird, diese vorsorglich gesperrt egal ob ein weiterer Anwender darauf zugreift oder nicht. Andere Anwender können für den Moment wo die Sperren aktiv sind nicht darauf zugreifen.

Optimistic Locking

Bedeutet, SQL Server geht davon aus, dass die Daten die Sie gerade bearbeiten, erst einmal nicht zur gleichen Zeit von einem anderen User verändert werden. Es wird bei diesem Konzept erst einmal ganz Optimistisch an die Sache herangegangen. Bedeutet, es wird auf die ursprünglichen Daten keine Sperre gesetzt. Die zu verändernden Daten werden aus der Tabelle kopiert und mit der Kopie dieser Daten wird gearbeitet. Erst wenn die zuvor kopierten geänderten Daten wirklich in die Datenbank übernommen werden sollen, wird beim zurückschreiben überprüft, ob die Ursprungsdaten (also die in der Datenbanktabelle) zwischenzeitlich von einem anderen Anwender geändert wurden.

Was sind Transaktionen?

Bevor wir uns detaillierter den unterschiedlichsten Begrifflichkeiten und Mechanismen zuwenden soll hier noch geklärt werden was eine Transaktion eigentlich ist.

Man stolpert immer wieder über den Begriff der Transaktion.

Quelle Wikipedia:
Als Transaktion (von lateinisch trans „(hin-)über“, agere „treiben, handeln, führen“: also wörtlich: Überführung; dt. hier besser: Durchführung) bezeichnet man in der Informatik eine Folge von Programmschritten, die als eine logische Einheit betrachtet werden, weil sie den Datenbestand nach fehlerfreier und vollständiger Ausführung in einem konsistenten Zustand hinterlassen. Daher wird für eine Transaktion insbesondere gefordert, dass sie entweder vollständig und fehlerfrei oder gar nicht ausgeführt wird.

Eine Transaktion ist ein einzelner oder eine Sammlung von SQL DML Befehlen. Entweder werden alle zur Transaktion zugehörigen Befehle erfolgreich ausgeführt oder aber sollte ein Befehl misslingen, keiner. Die Daten werden bei einem Fehler in den Zustand vor Start der Transaktion zurückversetzt.

Ein Beispiel welches bereits in früheren Fachbüchern hierzu aufgegriffen wurde:

In einer Bank muss von einem Konto ein Betrag von 100,– auf ein anderes Konto überwiesen werden. Hierzu sind in der Regel zwei Vorgänge (SQL Befehle) notwendig (ja klar man könnte dies auch mit einem Updatebefehl umsetzen aber es geht hier um das Beispiel).

Der erste Befehl zieht die 100,– EUR ab und der Betrag wird sich gemerkt. Der zweite Befehl nimmt sich diesen Betrag und bucht ihn auf das neue Konto.

Wenn der erste Befehl erfolgreich ausgeführt wird, dann aber der Server abstürzt, kann das Geld nicht mehr auf das zweite Konto gebucht werden. Der Betrag wurde zwar abgezogen und nicht wieder aufsummiert und der gemerkte Betrag wurde auch nirgendwo notiert.

Hier kommen Transaktionen ins Spiel.

Wenn mehrere Befehle im Rahmen einer Transaktion ausgeführt werden kann im Fehlerfall der Datenbestand wieder zurückgesetzt werden. Man nennt diese Aktion auch ROLLBACK . Die Daten haben wieder den gleichen Stand wie vor dem starten der Transaktion.

Wenn aber beide Befehle erfolgreich abgearbeitet werden konnten, wird SQL Server mitgeteilt das die Transaction mit einem sogenannten COMMIT beendet wird. Die Änderungen werden festgeschrieben und sind Bestandteil der Datenbank.

Start Demo Rollback Transaktion:

In dieser Demo wird Ihnen gezeigt, wie eine Transaktion gestartet wird und nachdem die Änderungen an den Daten durchgeführt wurden und Daten hinzugefügt wurden, diese Änderungen wieder rückgängig gemacht werden.

1. Öffnen Sie SSMS und führen Sie auf Ihrer Testdatenbank (demo) folgende Befehle aus, um die Tabelle testransaktion mit zwei Datensätzen anzulegen.

create table testransaction (id int identity(1,1), nachname varchar(100), vorname varchar(100));

insert into testransaction values (‚Franke‘,’Mike‘);

insert into testransaction values (‚Fredericks‘,’Hanna‘);

2. Überprüfen Sie, durch nachfolgendes Select ob die Datensätze in der Tabelle erfolgreich angelegt wurden
select * from testransaction

3. Führen Sie die nachfolgenden Befehle aus um den Datenbestand zu ändern. Nach dem Öffnen einer Transaktion wird Mike Richter zu Michael Richter geändert. Es wird ein Datensatz Anne Richter hinzugefügt.

Begin Transaction;

update testransaction set vorname = ‚Michael‘ where id = 1;

insert into testransaction values (‚Richter‘,’Anne‘);

4. Der Selectbefehl zeigt Ihnen jetzt drei Datensätze in der Tabelle testransaktion an. Führen Sie Ihn aus um das Ergebnis zu überprüfen.

select * from testransaction

5. Führen Sie jetzt den Befehl Rollback aus und betrachten Sie anschließend mit dem Selectbefehl erneut Ihre Daten.

Rollback transaction

select * from testransaction

Sie werden feststellen, dass Ihre Daten wieder auf den Stand zurückgesetzt wurden, wie Sie vor Ihren Änderungen ausgesehen haben.

-Demo Ende-

Start Demo Commit Transaktion

1. Öffnen Sie SSMS und führen Sie auf Ihrer Testdatenbank (demo) folgende Befehle aus, um die Tabelle testransaktion mit zwei Datensätzen anzulegen.

create table testransaction (id int identity(1,1), nachname varchar(100), vorname varchar(100));

insert into testransaction values (‚Franke‘,’Mike‘);

insert into testransaction values (‚Fredericks‘,’Hanna‘);

2. Überprüfen Sie, durch nachfolgendes Select ob die Datensätze in der Tabelle erfolgreich angelegt wurden

select * from testransaction

Begin Transaction;

update testransaction set vorname = ‚Michael‘ where id = 1;

insert into testransaction values (‚Richter‘,’Anne‘);

4. Der Selectbefehl zeigt Ihnen jetzt drei Datensätze in der Tabelle testransaktion an. Führen Sie Ihn aus um das Ergebnis zu überprüfen.

select * from testransaction

5. Führen Sie jetzt den Befehl Commit Transaction aus und betrachten Sie anschließend mit dem Selectbefehl erneut Ihre Daten.

Commit Transaction

Jetzt sehen Sie, dass sämtliche Änderungen die Sie gemacht haben immer noch da sind, da Sie festgeschrieben (committed) wurden. Auch ein Rollback ist jetzt nicht mehr möglich, da Sie SQL Server mitgeteilt haben, das Sie die Transaktion erfolgreich abschließen möchten.

-Demo Ende-

Eine Transaktion startet man mit dem Befehl

BEGIN TRANSACTION

Und beendet Sie entweder mit COMMIT TRANSACTION oder ROLLBACK TRANSACTION.

Der Autocommit Modus

Wenn Sie einen Befehl eingeben ohne zuvor BEGIN TRANSACTION zu schreiben arbeitet SQL Server im sogenannten Auto Commit Modus arbeitet.

Dies bedeutet das SQL Server einfach von selbst, unsichtbar vor den SQL Befehl die Begin Transaction Anweisung setzt. Wenn der SQL Befehl Fehler erzeugt wird automatisch ein Rollback Transaction ausgeführt.

SQL Server führt sämtliche Befehle immer im Rahmen von Transaktionen aus. Mit Begin Transaction sagen Sie SQL Server lediglich, das Sie jetzt explizit festlegen wann eine Transaktion gestartet wird und wann Sie aufhört. Deshalb heißt dieser Modus auch Expliziter Transaktionsmode. Andernfalls wie zuvor beschrieben arbeitet SQL Server im Autocommit Modus. Auf Implizite Transaktionen wird in diesem Buch nicht eingegangen, da sie dem Expliziten Modus ähneln und eher selten in der Praxis angewendet werden.

Brauchen Sie noch einen Xpertenrat?

Das könnte Dich auch interessieren