Datensatzsperren ADO

[Dieter Mang]

Hallo,

ich muß in einer Anwendung Datensätze über einen längeren Zeitraum
sperren. Ich mache das jetzt so:

' Versuchen den Plan zur Bearbeitung zu sperren
Set cnLock = New ADODB.Connection
cnLock.Open CN.ConnectionString ' CursorLocation = aduseserver
cnLock.CommandTimeout = 1
Set rsLock = New ADODB.Recordset
rsLock.Open "select PID,aendat from zPlan where PID = " +
Str(rsPlan!pid), cnLock, adOpenDynamic, adLockPessimistic
On Error GoTo LockErr
If rsLock!pid = rsPlan!pid Then
rsPlanLocked = True
End If
Exit Sub
LockErr:
MsgBox "Plan ist gesperrt, Bearbeitung nicht möglich"
rsPlanLocked = False

Also währen ich die Bearbeitung immer mit Clientbasierten Cursorn machen
verwende ich zum sperren ein Serverbasiertes Recordset mit
adLockpessimistik.

Im Prinzip funktioniert mal alles auf den ersten Blick. Hat jemand mit
dieser Art von Sperren Erfahrungen? Was kann passieren? Wo sind Problem
zu erwarten. Was gibt es für Alternativen die folgenden Aufgabe lösen.

Während des Bearbeitungszeitraums muß der ganze Vorgang für die
Anwendung gesperrt sein. Ich mache das dadurch das ich den Kopfsatz
sperre und damit in der Anwendung den Zugriff auf die Kindsätze
unterbinde.

[Elmar Boye]

Hallo Dieter,

Post by Dieter Mang
ich muß in einer Anwendung Datensätze über einen längeren Zeitraum
[Code gesnippt...]
Also währen ich die Bearbeitung immer mit Clientbasierten Cursorn
machen verwende ich zum sperren ein Serverbasiertes Recordset mit
adLockpessimistik.
Im Prinzip funktioniert mal alles auf den ersten Blick.

Funktionieren tut das prinzipiell schon...

Post by Dieter Mang
Hat jemand mit dieser Art von Sperren Erfahrungen? Was kann passieren?

Am Ende sperrst Du dich schnell selbst aus. Den Zugriff auf die eine
Zeile hast Du nur über die eine Verbindung mit dem Recordset. Jede
weitere Connection Deiner Anwendung wird ebenso ausgesperrt, da jede
Sitzung (= ADODB.Connection) getrennt verwaltet wird. Summa summarum
wird das schnell ein ziemlich trickreicher Verhau, wenn Du noch
andere Verbindungen mit adUseClient einsetzen willst.

Post by Dieter Mang
Was gibt es für Alternativen die folgenden Aufgabe lösen.
Während des Bearbeitungszeitraums muß der ganze Vorgang für die
Anwendung gesperrt sein. Ich mache das dadurch das ich den Kopfsatz
sperre und damit in der Anwendung den Zugriff auf die Kindsätze
unterbinde.

Eine bessere Alternative, die ohne die pessimistischen Sperren auskommt,
wäre der Einsatz der SytemProzedur sp_getapplock. Dort kannst Du eine
Sperre in Verbindung mit Deinem Primary Key setzen. Diese Sperre behindert
keinen, solange sie nicht abgefragt wird.
Vorteil gegenüber selbst realisierten Sperren z. B. über zusätzliche Spalten
ist, dass sie beim "Abschuss" der Anwendung/Sitzung automatisch aufgelöst
wird.

Gruss
Elmar

[Dieter Mang]

Hallo Elmar,

Post by Elmar Boye
Eine bessere Alternative, die ohne die pessimistischen Sperren
auskommt, wäre der Einsatz der SytemProzedur sp_getapplock. Dort
kannst Du eine Sperre in Verbindung mit Deinem Primary Key setzen.
Diese Sperre behindert keinen, solange sie nicht abgefragt wird.
Vorteil gegenüber selbst realisierten Sperren z. B. über zusätzliche
Spalten ist, dass sie beim "Abschuss" der Anwendung/Sitzung
automatisch aufgelöst wird.

Laut OH ist das ein sehr intressanter Ansatz, aber ich kriegs nicht hin
das für VB aufzuarbeiten.

Ich habe mir also eine SP erstellt

CREATE PROCEDURE PlanLock
@Res VarChar(255),
@Result int output
AS
BEGIN TRAN
EXEC @result = sp_getapplock @Resource = @Res,
@LockMode = 'Exclusive',@LockTimeout =0
COMMIT TRAN
GO

und spreche diese aus VB an:

Dim cmdPlanLock As ADODB.Command
Set cmdPlanLock = New ADODB.Command
cmdPlanLock.ActiveConnection = CN
cmdPlanLock.CommandType = adCmdStoredProc
cmdPlanLock.CommandText = "PlanLock"
cmdPlanLock.Parameters.Refresh
cmdPlanLock("@Res") = Str(rsPlan!pid)
cmdPlanLock.Execute
If cmdPlanLock("@Result") >= 0 Then


Leider liefert @Result immer 0 zurück, das heißt es werden keine Sperren
gesetzt.
Mein CN ist adUseClient.

Siehst Du den Fehler?

Danke für die Hilfe
Gruss Dieter

[Elmar Boye]

Hallo Dieter,

Post by Dieter Mang

Post by Elmar Boye
Eine bessere Alternative, die ohne die pessimistischen Sperren
auskommt, wäre der Einsatz der SytemProzedur sp_getapplock. Dort
kannst Du eine Sperre in Verbindung mit Deinem Primary Key setzen.
Diese Sperre behindert keinen, solange sie nicht abgefragt wird.
Vorteil gegenüber selbst realisierten Sperren z. B. über zusätzliche
Spalten ist, dass sie beim "Abschuss" der Anwendung/Sitzung
automatisch aufgelöst wird.

Laut OH ist das ein sehr intressanter Ansatz, aber ich kriegs nicht
hin das für VB aufzuarbeiten.
Ich habe mir also eine SP erstellt
CREATE PROCEDURE PlanLock
@Res VarChar(255),
@Result int output
AS
BEGIN TRAN
@LockMode = 'Exclusive',@LockTimeout =0
COMMIT TRAN
GO

Der Standardmodus für LockOwner ist 'Transaction'. Und in dem
Modus wird eine Applikationssperre wie jede andere Sperre einer
Sitzung durch ein COMMIT / ROLLBACK freigegeben.
Da Du das oben in BEGIN TRAN/COMMIT verpackt hast, wird die
Sperre gleich wieder aufgelöst.

Sinnvoller für Deinen Zweck ist da eine Sitzungssperre:

DROP PROC PlanLock
GO

CREATE PROCEDURE PlanLock
@Res nvarchar(255),
@Result int output
AS
EXEC @result = sp_getapplock
@Resource = @Res,
@LockOwner = N'Session',
@LockMode = N'Exclusive',
@LockTimeout = 1000
GO


Wenn Du den folgenden Testcode in einer Sitzung im QA
ausführst, siehst Du eine "APP" Sperre:

DECLARE @id nvarchar(255)
DECLARE @rc int

SET @id = N'4711'
EXEC PlanLock @id, @rc out
SELECT @@SPID, @rc
EXEC sp_lock

Gleicher Code in einer weiteren Sitzung, liefert Dir -1 zurück,
Wie der Parameterwert 'Session' für LockOwner schon andeutet,
gilt die Sperre jetzt für die Dauer der Sitzung. Wenn Du also
Deine Verbindung schliesst, wird die Sperre entfernt. Der
Transaktionskontext hat hingegen keine Auswirkungen auf die
Sperre.
Gleiches läuft äquivalent mit ADO, wobei Du sinnvollerweise
eine globale Connection-Variable für die Sperrenvergabe nutzt.
Für die Freigabe das äquivalent Gegenstück mit
EXEC sp_releaseapplock @Resource, N'Session'
nicht vergessen.

Gruss
Elmar

[Dieter Mang]

Hallo Elmar,

Post by Dieter Mang
CREATE PROCEDURE PlanLock
@Res nvarchar(255),
@Result int output
AS
@Resource = @Res,
@LockOwner = N'Session',
@LockMode = N'Exclusive',
@LockTimeout = 1000

^^^^

Das funktioniert natürlich perfekt, danke.

Letzte Frage, der LockTimeout von 1000 - ist das notwendig oder würden
nach Deiner Erfahrung auch 100 reichen? Wenn mal die Sperre gesetzt ist
dauert das eh Minuten und Stunden, so dass 1 Sekunde warten auch keinen
Vorteil bringt.

[Elmar Boye]

Hallo Dieter,

Post by Dieter Mang

Post by Dieter Mang
CREATE PROCEDURE PlanLock
[...]
@LockTimeout = 1000

^^^^
Letzte Frage, der LockTimeout von 1000 - ist das notwendig oder würden
nach Deiner Erfahrung auch 100 reichen? Wenn mal die Sperre gesetzt
ist dauert das eh Minuten und Stunden, so dass 1 Sekunde warten auch
keinen Vorteil bringt.

Im Prinzip kannst Du es da halten wie die Dachdecker...
denn umgekehrt ist eine Sekunde warten auch nicht so dramatisch.

Gruss
Elmar