MSSQL 2005: Wert exclusiv sperren beim Lesen?

24/03/2009 - 18:12 von Hans Peter | Report spam
Hallo zusammen,

ich habe eigentlich eine einfache Aufgabenstellung. Ich möchte:

Schritt 1. Einen Spalten-Wert (Hier: "Count") aus einer Tabelle lesen
Schritt 2. Den Wert neu berechnen
Schritt 3. Den neuen Wert in der Spalte "Count" wieder speichern


Das Problem ist, das die Schritte 1-3 zur gleichen Zeit mehrmals
aufgerufen werden können. Deswegen habe ich alles in einer Transaktion
gepackt.

Bei der Aufgabenstellung ist es allerdings auch extrem wichtig ist das
der Wert aus 1 nicht erneut gelesen werden darf (da er sich durch die
Transaktion ja àndert) bis die Transaktion abgeschlossen ist. Das
bedeutet, dass ich an Schritt 1 eine exclusiv Sperre benötige.

Dazu habe ich mit mit T-SQL folgendes Skript erstellt, was zweimal zur
gleichen Zeit ausgeführt wird:

BEGIN TRANSACTION

DECLARE @intCount AS INT

SET @intCount = (SELECT Count FROM tblState WHERE ID = 1)

SET @intCount = @intCount + 1

SELECT @intCount

WAITFOR DELAY '00:00:05'

UPDATE tblState SET Count = @intCount

COMMIT TRANSACTION

Wenn ich das Skript zur gleichen Zeit zwei mal ausführe, dann erhalte
ich aus beiden Skripten die gleichen Werte. Verwende ich als Transaktion
Repeatable Read, dann wird eine Transaktion als Deadlock gevictimed.

Verwende ich Serializable dann werden auch beide Transaktion als
Deadlock gevictimed. Das kann ich mir auch nicht erklàren, da bei
Serializable doch die Transaktion explizit nacheinander verarbeitet
werden sollten -> Somit dürft doch gar kein Deadlock entstehen, oder?

Eine Lösung meinerseits die ich besonders uncool finde wàre wie folgt:

BEGIN TRANSACTION

DECLARE @intCount AS INT

UPDATE tblState SET Count = Count WHERE ID = 1

SET @intCount = (SELECT Count FROM tblState WHERE ID = 1)

SET @intCount = @intCount + 1

SELECT @intCount

WAITFOR DELAY '00:00:05'

UPDATE tblState SET Count = @intCount

COMMIT TRANSACTION

Das hat bei mir bis jetzt bei allen Test (mit ReadCommitted)
funktioniert. Ich möchte es aber ungern verwenden!


Meine Fragen dazu:

1. Wie kann ich ein exclusiv Lock beim Lesen (=SELECT) erreichen?
2. Wieso bekomme ich beim ISolationslevel RepeatableRead oder
Serializable bei dem Skript überhaupt einen Deadlock?

Gruß Hans
 

Lesen sie die antworten

#1 Elmar Boye
24/03/2009 - 18:47 | Warnen spam
Hallo Hans Peter,

Hans Peter schrieb:
ich habe eigentlich eine einfache Aufgabenstellung. Ich möchte:

Schritt 1. Einen Spalten-Wert (Hier: "Count") aus einer Tabelle lesen
Schritt 2. Den Wert neu berechnen
Schritt 3. Den neuen Wert in der Spalte "Count" wieder speichern


Das Problem ist, das die Schritte 1-3 zur gleichen Zeit mehrmals
aufgerufen werden können. Deswegen habe ich alles in einer Transaktion
gepackt.

Bei der Aufgabenstellung ist es allerdings auch extrem wichtig ist das
der Wert aus 1 nicht erneut gelesen werden darf (da er sich durch die
Transaktion ja àndert) bis die Transaktion abgeschlossen ist. Das
bedeutet, dass ich an Schritt 1 eine exclusiv Sperre benötige.

Dazu habe ich mit mit T-SQL folgendes Skript erstellt, was zweimal zur
gleichen Zeit ausgeführt wird:
BEGIN TRANSACTION

DECLARE @intCount AS INT

SET @intCount = (SELECT Count FROM tblState



hier einfügen:
WITH (HOLDLOCK, UPDLOCK)

HOLDLOCK entspricht SERIALIZABLE (aber einfacher zu tippen ;-)
UPDLOCK erstellt eine Änderungssperre.

WHERE ID = 1)

...

UPDATE tblState SET Count = @intCount



und hier bitte auch einfügen:
WHERE ID = 1;

COMMIT TRANSACTION



Mehr dazu:
<URL:http://msdn.microsoft.com/de-de/lib...3.aspx>

und auch von Interesse:
<URL:http://weblogs.sqlteam.com/dang/arc...n.aspx>

Gruß Elmar

Ähnliche fragen