MS-SQL Datensatz sperren

  • VB.NET
  • .NET (FX) 4.5–4.8

Es gibt 9 Antworten in diesem Thema. Der letzte Beitrag () ist von Zenker.

    MS-SQL Datensatz sperren

    Hallo,

    ich möchte in einer MS-SQL Datenbank einen Datensatz versperren. Es handelt sich dabei um einen Nummernkreiszähler, auf den auch andere zeitgleich zugreifen. Nach vielen Experimenten kam schlussendlich das hier heraus:

    VB.NET-Quellcode

    1. Imports System.Data.SqlClient
    2. Imports System.Data.Odbc
    3. ...
    4. Public sTransaction As SqlTransaction
    5. Public command As SqlCommand
    6. Public conn as SQLConnections
    7. Transaction = conn.BeginTransaction("TestTransaction")
    8. command.Transaction = sTransaction
    9. Try
    10. command.CommandText = "Update xyz Set Id = 1"
    11. command.ExecuteNonQuery()
    12. Catch ex As Exceptions
    13. Transaction.Rollback()
    14. conn.close
    15. return false
    16. End Try
    17. sTransaction.Commit()
    18. conn.close
    19. return true


    Es wirkt, allerdings passiert das Aufheben der Sperre an einem zweiten Arbeitsplatz erst nach dem Beenden des Programmes. Ich finde da keinen Fehler. In meinem Fall hat er das Commit durchlaufen und die dann die Verbindung geschlossen, das habe ich im Debugger nachvollziehen können. Hat jemand eine Idee, was da falsch laufen könnte?
    Hallo,
    so ganz komplett sieht der Code nicht aus. Die SqlConnection z.B. sollte mit einem Using-Block verwendet werden. Auch das Erstellen des Command-Objekts ansich zeigst du nicht. Du könntest mit dem SQL-Server Management Studio folgendes SQL ausführen, um zu schauen, ob deine Transaktion wirklich noch läuft:

    SQL-Abfrage

    1. SELECT * FROM sys.sysprocesses WHERE open_tran = 1

    Gibt es denn am zweiten Arbeitsplatz eine Fehlermeldung?

    Schau auch mal in die Doku, die haben hier ein komplettes Beispiel bzgl. Transaktionen.

    Edit:
    Auch wenn folgendes jetzt eher "advanced" ist, aber für einen Nummernkreiszähler musst du anders vorgehen. Die Transaktion sollte bleiben, aber du musst vorher den aktuellen Wert aus der Datenbank holen. Man kann den SQL-Server anweisen, dass er bereits beim Lesen einen Lock auf den Datensatz setzen soll. Wenn eine zweite Instanz deines Programms eine neue Nummer ziehen will, wird so lange gewartet, bis ein Lock gesetzt werden kann, oder aber ein Timeout passiert.
    Spoiler anzeigen

    C#-Quellcode

    1. var transaction = connection.BeginTransaction();
    2. var command = connection.CreateCommand();
    3. command.Connection = connection;
    4. command.Transaction = transaction;
    5. try
    6. {
    7. command.CommandText = "SELECT TOP 1 Id, NextNumber FROM NumberSeries WITH(UPDLOCK)";
    8. using var reader = command.ExecuteReader(CommandBehavior.SingleResult);
    9. reader.Read();
    10. var nextNo = (int)reader["NextNumber"];
    11. reader.Close();
    12. command.CommandText = "UPDATE NumberSeries SET NextNumber = @newNumber WHERE Id = 1";
    13. command.Parameters.AddWithValue("newNumber", ++nextNo);
    14. command.ExecuteNonQuery();
    15. // Dinge mit der neuen Nummer machen
    16. transaction.Commit();
    17. }
    18. catch (Exception ex)
    19. {
    20. Console.WriteLine("Message {0}", ex.Message);
    21. try
    22. {
    23. transaction.Rollback();
    24. }
    25. catch (Exception rex)
    26. {
    27. Console.WriteLine("Rollback exception: {0}", rex.Message);
    28. }
    29. }


    Wichtig ist der Table Hint WITH(UPDLOCK) im SELECT-Statement. Wie oben bereits erwähnt, wartet ein zweiter Prozess, der ebenfalls eine neue Nummer ermitteln möchte, bei [tt]ExecuteReader{/tt], bis der LOCK gesetzt werden kann, oder ein Timeout auftritt. So verhinderst du, dass zwei Mal die selbe Nr. zurückgeschrieben wird.

    Dieser Beitrag wurde bereits 2 mal editiert, zuletzt von „ISliceUrPanties“ ()

    Moinsen!

    Der "Erfinder des Rades" hat Dir schon zur Transaktion das Wesentliche gesagt.

    Von mir dazu noch eine weitere Anmerkung im Klugscheißermodus (ich weiß - erster Beitrag und dann gleich so... ;) :(

    Eine Transaktion ist kein geeignetes Mittel, um Benutzereingabe an Datensätzen zu sperren. Eine Transaktion dient dazu, eine oder i.d.R. mehrere Zugriffe auf eine Datenbank sauber durchzuführen und bei Fehlern wieder vollständig wieder zurücknehmen zu können, ohne das die Datenbank verändert wurde. Nicht mehr - nicht weniger. Da Transaktionen u.U. nicht nur den benötigten Datensatz sondern auch ganze Seiten sperren, kann dies, wenn die Transaktion nicht zeitnah abgeschlossen wird, zu Problemen führen.

    Wenn Du einen Datensatz sauber für eine zeitgleiche Bearbeitung durch andere Benutzer sperren möchtest, geht das nur über eine entsprechende Hilfstabelle und im besten Falle über entsprechende SPs in der Datenbank. Wenn Du jedoch fortlaufende Nummern zwingend benötigst, mußt Du bei Neuanlagen sogar die ganze Tabelle sperren.

    Die Hilfstabelle einhält in den einzelnen die PKs der Datensätze, die aktuell gesperrt sind. Wichtig ist dabei ein Ablaufzeitpunkt der Sperre für den Fall, wenn ein Client während der Bearbeitung in der Sperre abschmiert. Das Sahnehäubchen ist dann, wenn die Tabelle auch noch die Kontaktdaten des sperrenden Benutzers enthält, z.B. die Durchwahl.

    Der Ablauf ist dann folgender:
    1) Sperrtabelle abfragen
    2.1) Ist der Datensatz oder die Tabelle gesperrt: Meldung an den Client
    2.2) Datensatz ist nicht gesperrt:
    2.2.1) Sperre setzen
    2.2.2) ggf. Transaktion starten
    2.2.3) Datensatz / Datensätze bearbeiten oder neu anlegen
    2.2.4) Transaktion abschließen
    2.2.5) Sperre aufheben.

    Viele Grüße

    Gerrit
    Da stimme ich @Gerrit zu, ich hab mir dafür ne kleine Klasse gebaut, ggf. kannst du die für deine Zwecke verunstalten:

    Spoiler anzeigen

    VB.NET-Quellcode

    1. Public Class RecordLock
    2. Private Shared UserRow As BenutzerRow
    3. Private Shared MachineName As String
    4. Private Shared TableName As String
    5. Private Shared RecordIDs As Integer()
    6. Private Shared Locked As List(Of RecordLockRow)
    7. ''' <summary>RecordLock initialisieren und Properties setzen</summary>
    8. Public Sub New(table As DataTable, ParamArray IDs As Integer())
    9. If IDs.Length = 0 Then Throw New Exception("Ohne Record-ID's kein Locking möglich!")
    10. UserRow = User.UserRow
    11. MachineName = Environment.MachineName
    12. TableName = table.TableName
    13. RecordIDs = IDs
    14. Locked = New List(Of RecordLockRow)
    15. End Sub
    16. ''' <summary>wenn nicht schon ein Lock eines anderen Users für die Datensätze besteht, dann Lock setzen</summary>
    17. Public Function LockRecord() As Boolean
    18. If App.PersistanceModeXml Then Return True 'Im XML-Betrieb ist kein RecordLock nötig, da nur Single-User
    19. Dts.TableFill(Dts.RecordLock)
    20. If IsAlreadyLocked() Then Return False
    21. RecordIDs.ForEach(Sub(id) Locked.Add(Dts.RecordLock.AddRecordLockRow(UserRow, MachineName, TableName, id, Date.Now)))
    22. Dts.SaveDts() 'lock persistieren
    23. Return True
    24. End Function
    25. ''' <summary>den eigenen RecordLock rausnehmen</summary>
    26. Public Sub UnlockRecord()
    27. If App.PersistanceModeXml Then Return 'Im XML-Betrieb ist kein RecordLock nötig, da nur Single-User
    28. Dts.TableFill(Dts.RecordLock)
    29. Locked.ForEach(Sub(rw) rw.Delete())
    30. Dts.SaveDts() 'unlock persistieren
    31. End Sub
    32. ''' <summary>Refreshen der RecordLock-Tabelle und prüfen, ob bereits ein Lock eines anderen Users besteht</summary>
    33. Private Function IsAlreadyLocked() As Boolean
    34. If App.PersistanceModeXml Then Return False 'Im XML-Betrieb ist kein RecordLock nötig, da nur Single-User
    35. Dts.RecordLock.CustomFill("WHERE Tabelle = ?", TableName)
    36. Dim ToDelete As New List(Of DataRow)
    37. For Each recID In RecordIDs
    38. Dim tryLock = Dts.RecordLock.FirstOrDefault(Function(x) x.Tabelle = TableName AndAlso x.DatensatzID = recID)
    39. If tryLock IsNot Nothing Then 'es gibt schon einen Lock
    40. Dim test = tryLock.Timestamp
    41. If tryLock.Timestamp.AddHours(1) < Date.Now Then 'Lock älter als 1 Stunde = verfallen -> löschen
    42. ToDelete.Add(tryLock) 'den abgelaufenen Lock löschen
    43. Else
    44. If tryLock.BenutzerRow Is User.UserRow Then
    45. ToDelete.Add(tryLock) 'wenn der Lock vom eigenen User ist, dann auch löschen
    46. Else
    47. msgError($"Der Datensatz ist seit {tryLock.Timestamp} durch {tryLock.BenutzerRow.Benutzername} | {tryLock.Rechner} gesperrt.")
    48. Return True
    49. End If
    50. End If
    51. End If
    52. Next
    53. ToDelete.ForEach(Sub(rw) rw.Delete())
    54. Return False
    55. End Function
    56. End Class

    "Na, wie ist das Wetter bei dir?"
    "Caps Lock."
    "Hä?"
    "Shift ohne Ende!" :thumbsup:
    @Zenker: Solange sich die User hier anständig verhalten und auch die Antwortenden eingehen, wird auch niemand in der Luft zerrissen ;)
    Solltest du meinen Code oben bei dir benutzen und anpassen, würde ich mich über eine Rückmeldung freuen, ob das auch bei dir funzt :thumbup:
    "Na, wie ist das Wetter bei dir?"
    "Caps Lock."
    "Hä?"
    "Shift ohne Ende!" :thumbsup:
    Hallo,

    nach längerer Krankheit habe ich nun ausprobiert. Da färbt sich noch einiges rot. Offenbar soll es eine Klasse BenutzerRow geben. Im Grunde reicht dort auch die UserRow, oder?

    dts - ist das das Dataset oder ist das etwas, das als Verweis noch eingebunden werden muss?

    Zenker schrieb:

    Offenbar soll es eine Klasse BenutzerRow geben

    Bei mir gibt's eine Klasse User - davon bekommt ein Benutzer des Programms beim Anmelden eine neue Instanz zugewiesen, mit Eigenschaften die benötigt werden.

    z.B. WindowsUserName, Die BenutzerRow unter dem der User im Programm angelegt wurde etc.

    Du kannst das auch ohne Benutzerinfo's locken, dazu musst du die Lock-Klasse eben anpassen. Aber da deine Anwendung ja Multi-User-fähig sein soll, würde es ja auch Sinn machen diese User im Programm "anzulegen"
    und eine entspr. Rechtesteuerung zu programmieren. Dafür hilft die User-Klasse dann eben auch schon.


    Zenker schrieb:

    dts - ist das das Dataset

    Ja, ist das DataSet
    "Na, wie ist das Wetter bei dir?"
    "Caps Lock."
    "Hä?"
    "Shift ohne Ende!" :thumbsup: