Deadlock bei gleichzeitigem Zugriff

Question

Hallo,

in einer Windows-Forms-Anwendung habe ich unten angefügten Code. Konkret geht es um das Verhalten des SQL-Servers beim Locking, also beim gleichzeitigen schreibenden Zugriff mehrerer Applikationen auf denselben Datensatz. Laut Doku SQL-Server sollte der SQL-Server hier ein Locking durchführen, d. h. eine Anwendung wartet bis die andere fertig ist.

Wenn ich die unten aufgeführte Schleife in zwei Anwendungen starte, funktioniert das auch. Wenn eine Dritte dazukommt, gibt es irgendwann Deadlocks.

Unter Oracle 11 funktioniert alles einwandfrei, d. h. alle drei Anwendungen arbeiten ihre Schleife ab und eine wartet, wenn die Zeile blockiert ist.

Wo liegt beim SL-Server das Problem?

Grüße

Stefan

    void button1_Click(object sender, EventArgs e)
    {
      SqlConnection conn = new SqlConnection("Data Source=VMW2003200811YS;Initial Catalog=pubs;User ID=tester;Password=tester");
      conn.Open();
      try
      {
        for (uint i = 1; i <= 5000; i++)
        {
          SqlTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
          SqlCommand cmd = new SqlCommand();
          cmd.Connection = conn;
          cmd.Transaction = trans;
          cmd.CommandText = "update TEST_ID set NEXT_ID=NEXT_ID+1";
          cmd.ExecuteNonQuery();
          cmd.CommandText = "select NEXT_ID-1 from TEST_ID";
          int nextid = (int)cmd.ExecuteScalar();
          trans.Commit();
        }
      }
      finally
      {
        conn.Close();
      }

 

Answer 1

Hallo Keksforscher (ein Realname wäre schöner),

um das Locking von SQL Server zu verstehen, solltest Du ev. mal diesen Artikel lesen:
http://www.sqlteam.com/article/introduction-to-locking-in-sql-server

Ebenfalls eine sehr gute Quelle (incl. Beispiele) findest Du hier:
http://blog.sqlauthority.com/2007/04/27/sql-server-2005-locking-hints-and-examples/

---

Uwe Ricken
Microsoft Certified Database Administrator SQL Server 2005
db Berater GmbH
http://www.db-berater.de

Answer 2

Hallo Uwe,

vielen Dank für die Links. Meine Problem ist damit aber nicht gelöst.

Daß bei einem Update-Zugriff der SQL-Server ein Update-Lock anfordert und diesen dann später in einen Exklusive-Lock konvertieren mußte, wußte ich auch schon vorher.

Desweiteren geht es hier ja darum, warum bei zwei gleichzeitigen Zugriffen das System läuft, aber nicht bei z. B. drei oder mehr.

Weiterhin ist die Meldung eines Deadlocks Quatsch, weil ein Deadlock definiert ist als:

"Deadlock occurs when two users have locks on separate objects and each user wants a lock on the other's object. For example, User1 has a lock on object "A" and wants a lock on object "B" and User2 has a lock on object "B" and wants a lock on object "A". In this case, SQL Server ends a deadlock by choosing the user, who will be a deadlock victim. After that, SQL Server rolls back the breaking user's transaction, sends message number 1205 to notify the user's application about breaking, and then allows the nonbreaking user's process to continue."

Wir haben hier aber keine zwei Anwendungen, die sich gegenseitig die Ressourcen blockieren sondern nur Anwendungen, die gleichzeitig den gleichen Datensatz in der DB updaten wollen. Aufgabe der DB ist es, dies zu synchronisieren.

Unter Oracle funktioniert das wunderbar mit bis zu 50 Clienten (getestet), bei SQL-Server macht das Programm bei drei Clients die Grätsche.

Warum?

Grüße

Stefan

Answer 3

Hallo Stefan,

wir hatten in unserer ERP Datenbank eine SP, die nach der gleichen Logik vorging um eine Identity (Oracle: Sequenz) abzubilden (keine gute Idee). Ohne explizite Transaktion kamen in Stress-Situationen doppelte Werte zustande, mit Transaktion gab es wie bei Dir Deadlocks.

Um es zu lösen (und zu optimieren), gäbe es mehrere Varianten. Wenn immer nur ein Wert betroffen sein kann, weil Next_ID der PK ist, könntest Du Dir den Wert, den Du mit den zusätzlichen SELECT ermittelst, über einen Parameter zurück liefern lassen (wobei das -1 natürlich auch in C# erfolgen kann, statt in der zusätzlichen SET Anweisung).
Andere Alterntive wäre, egal ob 1 oder mehrer Werte sich ergeben, die OUTPUT-Klausel zu verwenden, die es ab SQL Server 2005 gibt. Ergeben sich mehrere Werte, dann verwende einen DataReader, bei nur einem Wert nimmst Du ExecuteScalar.

Beides würde Dir ein zusätzliches SELECT ersparen und Deadlocks vermeiden.

-- Nur für meinen Test

CREATE TABLE #TEST_ID (next_id int);

INSERT INTO #TEST_ID VALUES (1);

DECLARE @nextID int;

 

 

-- Variante 1, wenn nur 1 Wert betroffen

UPDATE #TEST_ID

SET @nextID = NEXT_ID = NEXT_ID + 1;

SET @nextID = @nextID -1;

 

-- Variante 2, wenn mehrere Werte betroffen

UPDATE #TEST_ID

SET NEXT_ID = NEXT_ID + 1

OUTPUT inserted.NEXT_ID;

 

 

GO

DROP TABLE #TEST_ID;

 

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 4

Hallo,

die Definition der Tabelle sieht so aus:

CREATE TABLE NextID(
    NextDocID         int    NOT NULL,
)
;

Keine Indexes usw.

Die Tabelle enthält einen Wert, der als Zähler dient und durch meine Routine um 1 erhöht wird um die nächste eindeutige ID zu bekommen.

Und: ja ich muß das so machen und kann keine Eigenheiten des SQL-Servers dafür benutzen weil unser Programm mehrere Datenbanken unterstützen muß (Oracle, SQL-Server, MySQL, Postgres, Firebird) und wir nicht für jede Datenbank Besonderheiten programmieren können.

Grüße

Stefan

Answer 5

Hallo Yury,

ich glaube, daß Du die Ausgangssituation falsch verstanden hast. Die Tabelle enthält nur einen Wert der immer um 1 inkrementiert werden soll (siehe mein Posting unten).

Grüße

Stefan

Answer 6

Und: ja ich muß das so machen und kann keine Eigenheiten des SQL-Servers dafür benutzen weil unser Programm mehrere Datenbanken unterstützen muß (Oracle, SQL-Server, MySQL, Postgres, Firebird) und wir nicht für jede Datenbank Besonderheiten programmieren können.

Tja,

schade nur, dass alle diese Datenbanken ihre Besonderheiten haben, die wenn man diese nicht, kennt zu diversen Fehlersituationen führen können.

Wobei hier in diesem Fall der Unterschied zwischen multi-version concurrency control (mvcc) und locking entscheidend ist (einfach mal nach googeln). SQL Server beherrscht beides, ersteres muss man aber aktivieren.

Zu beachten sind hier aber die möglichen Phänomene, die sich bei mvcc oder locking ergeben können, insbesondere wenn man versucht eine Sequence auf die Art und Weise zu implementieren (nicht umsonst stellen z.B. Oracle und Firebird spezielle Befehle zum Erstellen von Sequenzen zur Verfügung, warum SQL Server z.B. nicht würde mich auch mal interessieren (wie man in SQL Server eine Sequence nachimplementieren kann, ist aber auch leicht zu ergooglen)).

Reiz dein Beispiel bei Oracle einfach mal mehr aus (mehr parallele Zugriffe) und schau mal, ob der erzeugte Wert wirklich eindeutig bleibt...

Viele Grüße

Christoph