VB / C# SQL Server Record Locking

Question

I have searched for this over and over and cannot find an answer. I want to read and lock a record in sqlserver, process the data in some way, then write the record back to the database and release the lock.

This is an example of the type of code I'm trying to use:

Imports System.Data.SqlClient

Public Module TestDB

Private Sub DoThis()

    ProcessData(1, 1)

End Sub

Private Sub ProcessData(ID As Integer, Quantity As Integer)

    Dim DBMS As New DB
    Dim MyRow As DataRow = Nothing

    DBMS.OpenDatabase()

    MyRow = DBMS.GetRecord(ID)          'lock this record so nobody else can read it, but only the record, not the table
    If MyRow IsNot Nothing Then
        '----------
        'do some processing
        MyRow("Quantity") = MyRow("Quantity") + Quantity
        '----------
        DBMS.UpdateRecord(MyRow)        'unlock this record people can read it again
    End If

    DBMS.CloseDatabase()

End Sub

End Module


Public Class DB

Public m_oConnection As SqlConnection
Public m_oTransaction As SqlTransaction
Public m_oCommand As SqlCommand
Public m_oDataAdapter As SqlDataAdapter
Public m_oDataTable As DataTable

Public Shared m_sConnectionString As String = "Server=SQL01; Database=MyDB; uid=me; pwd=secret;"

Public Sub OpenDatabase()

    m_oConnection = New SqlConnection
    m_oConnection.ConnectionString = m_sConnectionString
    m_oConnection.Open()

End Sub

Public Sub CloseDatabase()

    m_oConnection.Close()

End Sub

Public Function GetRecord(RecordID As Integer) As DataRow

    Dim Result As DataRow = Nothing

    Dim SQL As String = ""
    SQL &= "SELECT * FROM TempStock WHERE StockID = " & RecordID

    m_oDataAdapter = New SqlDataAdapter
    m_oDataTable = New DataTable

    m_oCommand = New SqlCommand(SQL, m_oConnection)

    m_oDataAdapter.SelectCommand = m_oCommand
    m_oDataAdapter.Fill(m_oDataTable)

    Dim iRows As Integer = m_oDataTable.Rows.Count
    If iRows > 0 Then
        Result = m_oDataTable.Rows(0)
    End If

    Return Result

End Function

Public Function UpdateRecord(Row As DataRow) As Integer

    Dim Result As Integer = 0

    Dim SQL As String = ""
    SQL &= "UPDATE TempStock "
    SQL &= "SET Quantity = " & Row("Quantity")
    SQL &= "WHERE StockID = " & Row("StockID")

    m_oCommand = New SqlCommand(SQL, m_oConnection)
    Dim iRows As Integer = m_oCommand.ExecuteNonQuery()

    Return Result

End Function

End Class

Very simple idea to my mind, and a pretty standard thing to write in a multiuser application - I don't want 2 users to read the same record and try to update it. I don't want to get into timestamps etc, and so far, using transactions I just cannot get it to work - unless I'm totally misunderstanding them.

Can anyone help? (my example is in VB, but C# answers will be just as helpful).

Answer 1

Ok, for those that might be interested, I've finally managed to work this out - my test code is listed below.

There's a module that contains various 'database' routines, and a form which calls these routines (note the form code is just code, not the actual form, but it should be sufficient to demonstrate how it works).

By using transaction and the lock hints shown in the code, I can read a record and lock it so other users can't access it. I can apply an update and once the commit (or abort) transaction are applied, the record is unlocked for other users.

A second user/instance that tried to access a locked record will be 'paused' until the transaction by the first instance is completed (although this will timeout after 30 seconds, but this is handled by the code).

The second instance can access a different record and update it as required.

Module Code:

Option Explicit On

Imports System
Imports System.Data.SqlClient

Public Module DataAccess

Public m_sConnectionString As String = "Server=sql01; Database=test; uid=myuser; pwd=mypwd;"
Private m_oConnection As SqlConnection = New SqlConnection
Private m_oTransaction As SqlTransaction

Public Function OpenDB() As Boolean

    Try
        m_oConnection = New SqlConnection
        m_oConnection.ConnectionString = m_sConnectionString
        m_oConnection.Open()

        Return True

    Catch sqlex As SqlException
        Console.WriteLine(sqlex.Message)
        Return False

    Catch ex As Exception
        Console.WriteLine(ex.Message)
        Return False

    End Try

End Function

Public Sub CloseDB()

    Try
        m_oTransaction = Nothing
        m_oConnection.Close()
        m_oConnection = Nothing

    Catch sqlex As SqlException
        Console.WriteLine(sqlex.Message)
        Stop

    Catch ex As Exception
        Console.WriteLine(ex.Message)
        Stop

    Finally

    End Try

End Sub

Public Sub CommitTransaction()

    Try
        If m_oTransaction IsNot Nothing Then m_oTransaction.Commit()

    Catch sqlex As SqlException
        Console.WriteLine(sqlex.Message)
        Stop

    Catch ex As Exception
        Console.WriteLine(ex.Message)
        Stop

    Finally
        m_oTransaction = Nothing

    End Try

End Sub

Public Sub AbortTransaction()

    Try
        If m_oTransaction IsNot Nothing Then m_oTransaction.Rollback()

    Catch sqlex As SqlException
        Console.WriteLine(sqlex.Message)
        Stop

    Catch ex As Exception
        Console.WriteLine(ex.Message)
        Stop

    Finally
        m_oTransaction = Nothing

    End Try

End Sub

Public Function ReadRecordByID(ID As Integer, LockRecord As Boolean) As Tuple(Of Boolean, DataRow)

    Dim Result As Tuple(Of Boolean, DataRow) = New Tuple(Of Boolean, DataRow)(False, Nothing)

    If ID = 0 Then Return Result

    Dim sSQL As String = ""
    Dim oCommand As SqlCommand = Nothing
    Dim LockCommand As String = ""

    Dim MyDA As SqlDataAdapter
    Dim MyTable As DataTable
    Dim MyRow As DataRow

    Try
        m_oTransaction = Nothing

        oCommand = m_oConnection.CreateCommand
        If LockRecord Then
            m_oTransaction = m_oConnection.BeginTransaction(IsolationLevel.Serializable)
            LockCommand = "WITH (HOLDLOCK, UPDLOCK) "
        End If

        sSQL = ""
        sSQL &= "SELECT * FROM TempStock " & LockCommand & "WHERE StockID = " & ID

        oCommand.CommandText = sSQL
        oCommand.Connection = m_oConnection

        MyDA = New SqlDataAdapter
        MyTable = New DataTable

        If LockRecord Then oCommand.Transaction = m_oTransaction

        MyDA.SelectCommand = oCommand
        MyDA.Fill(MyTable)
        MyRow = MyTable.Rows(0)

        Result = New Tuple(Of Boolean, DataRow)(True, MyRow)

        MyTable = Nothing
        MyDA = Nothing

    Catch sqlex As SqlException
        Console.WriteLine(sqlex.Message)
        If m_oTransaction IsNot Nothing Then AbortTransaction()

    Catch ex As Exception
        Console.WriteLine(ex.Message)
        If m_oTransaction IsNot Nothing Then AbortTransaction()

    Finally

    End Try

    Return Result

End Function

Public Function UpdateRecord(Row As DataRow) As Boolean

    Dim Result As Boolean = False

    Dim sSQL As String = ""
    Dim oCommand As SqlCommand = Nothing

    Try
        oCommand = m_oConnection.CreateCommand

        sSQL = ""
        sSQL &= "UPDATE TempStock " & vbCrLf
        sSQL &= "SET Quantity = " & Row("Quantity") & ", Allocated = " & Row("Allocated") & ", LastUpdated = GETDATE() WHERE StockID = " & Row("StockID")

        oCommand.CommandText = sSQL
        oCommand.Connection = m_oConnection
        oCommand.Transaction = m_oTransaction
        oCommand.ExecuteNonQuery()

        Result = True

    Catch sqlex As SqlException
        Console.WriteLine(sqlex.Message)
        If m_oTransaction IsNot Nothing Then AbortTransaction()
        Result = False

    Catch ex As Exception
        Console.WriteLine(ex.Message)
        If m_oTransaction IsNot Nothing Then AbortTransaction()
        Result = False

    End Try

    Return Result

End Function

End Module

Form Code:

Option Explicit On

Imports System

Public Class Form1

Private MyDataRow As DataRow

Private Sub btnOpen_Click(sender As Object, e As EventArgs) Handles btnOpen.Click

    Dim ID As Integer = Val(txtID.Text)
    If ID < 1 Then Exit Sub

    btnOpen.Enabled = False
    btnUpdate.Enabled = False
    btnCommit.Enabled = False
    btnAbort.Enabled = False

    If OpenDB() Then
TryAgain:
        Dim ReadRow As Tuple(Of Boolean, DataRow) = ReadRecordByID(ID, chkTransaction.Checked)
        btnUpdate.Enabled = True
        If ReadRow.Item1 Then
            MyDataRow = ReadRow.Item2
            lblQty.Text = MyDataRow("Quantity")
            lblAlloc.Text = MyDataRow("Allocated")
            txtQty.Text = ""
            txtAlloc.Text = ""
            If Not chkTransaction.Checked Then btnAbort.Enabled = True
        Else
            Select Case MessageBox.Show("Transaction Time Out - Unable to lock record", "Database", MessageBoxButtons.RetryCancel, MessageBoxIcon.Question)
                Case DialogResult.Retry
                    GoTo TryAgain
                Case Else
            End Select
            btnOpen.Enabled = True
            btnUpdate.Enabled = False
            btnCommit.Enabled = False
            btnAbort.Enabled = False
            txtID.Select()
        End If
    Else
        btnOpen.Enabled = True
        btnUpdate.Enabled = False
        btnCommit.Enabled = False
        btnAbort.Enabled = False
    End If

End Sub

Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click

    MyDataRow("Quantity") += Val(txtQty.Text)
    MyDataRow("Allocated") += Val(txtAlloc.Text)

    If UpdateRecord(MyDataRow) Then

        If chkTransaction.Checked Then
            btnCommit.Enabled = True
            btnAbort.Enabled = True
            btnUpdate.Enabled = False
        Else
            btnOpen.Enabled = True
            btnCommit.Enabled = False
            btnAbort.Enabled = False
            btnUpdate.Enabled = False

            lblQty.Text = ""
            lblAlloc.Text = ""
            txtQty.Text = ""
            txtAlloc.Text = ""
        End If

    Else
        btnOpen.Enabled = True
        btnUpdate.Enabled = False
        btnCommit.Enabled = False
        btnAbort.Enabled = False

        lblQty.Text = ""
        lblAlloc.Text = ""
        txtQty.Text = ""
        txtAlloc.Text = ""
    End If

End Sub

Private Sub btnCommit_Click(sender As Object, e As EventArgs) Handles btnCommit.Click

    CommitTransaction()

    lblQty.Text = ""
    lblAlloc.Text = ""
    txtQty.Text = ""
    txtAlloc.Text = ""

    btnOpen.Enabled = True
    btnCommit.Enabled = False
    btnAbort.Enabled = False

    CloseDB()

End Sub

Private Sub btnAbort_Click(sender As Object, e As EventArgs) Handles btnAbort.Click

    AbortTransaction()

    lblQty.Text = ""
    lblAlloc.Text = ""
    txtQty.Text = ""
    txtAlloc.Text = ""

    btnOpen.Enabled = True
    btnUpdate.Enabled = False
    btnCommit.Enabled = False
    btnAbort.Enabled = False

    CloseDB()

End Sub

End Class

There's been a lot of trial and error getting this to work, as I've just not been able to find any examples, just documentation that covers the theory, in a vague way.

I did get it working and then it stopped - I changed the database table - you must have a primary key on the table, and have 'allow row locks = true'.

I hope this helps anyone in a similar situation.

Answer 2

[This is a comment really, but too long]

ADO.Net is very heavily biased toward optimistic concurrency ie no actual locking, but using timestamps (or checking current row values) to detect changes, which you then have to deal with. MS will tell you that most of the time this is a better strategy, eg because users can go to lunch leaving locks in place which prevent other users from doing anything, etc.
Transactions don't do locking, they just guarantee all-or-nothing execution of a batch. It's a common, intuitive misconception though - see for instance Is a single SQL Server statement atomic and consistent? which hopefully also contains enough information down-thread about how to make locking occur, to at least get you started.

EDIT in response to OPs comment/answer:

You need to research more on locking - ROWLOCK, UPDLOCK, transaction isolation level etc, particularly if you intend to go for pessimistic locking. As I mentioned there is some good material in the replies in the link to get you started. Also David Sceppa's ADO.Net 2.0 book - more than 10 years old, true - is still relevant and briefly covers this issue on p507 and 650. Don't give up hope on optimistic locking either - you might find you can get a surprisingly long way with judicious use of transactions etc. Your putative scenario of negative stock etc is definitely avoidable even with optimistic locking; but one user or another is going to perceive that they can go ahead, but then hit an error message that their update failed.

Answer 3

For most occasions, I agree, optimistic locking works, and if one allows for timestamps, then it helps for those moments when someone might have changed your record.

The problem I have here is a legacy system, and time to completely rewrite from scratch and design better isn't going to happen.

The legacy code is using a random access binary file and at some point, MS have very kindly change the libraries that allow access to text/random/binary files in .net and stopped the multiuser access working. The documentation says it all works, but quite simply, it doesn't - you can open a file with shared access and lock a record, but it locks the file, not just the record.

To get round this, I need to migrate the data to a database and replace a number of sub/functions in a class so they get the data from the database and not the binary file. All well and good, and minimal code change... except when it comes to a particular section that uses a mechanism as I describe above.

The silly thing is, this to my mind is pretty basic business data processing... read a stock record and see if there is sufficient stock to fulfil an order, then reserve or deduct the stock. If another user tried to do the same at the same time, they will see the same quantity is available and try to use it, and if the quantities are such, then the stock will go negative and/or someone will not be able to have their order, as there was insufficient stock. Obviously, I don't want to keep reading the record and refreshing the screen so the user can see the stock changing, every second. Added to this, is a service/background task automatically doing the stock processing - this only runs on one server, so isn't going to have issues competing with itself, but users have the facility to manually process the stock, for orders that fail the automatic process for some reason.

The best I've managed so far, is using transactions (which potentially, is good, as I want to process multiple items for an order on an 'all or nothing' basis), but so far, the only thing I can find is using:

open table, open transaction, select, (process), update, commit or terminate, close table

using TABLOCKX, but this locks the whole table.

But without this, until the update command is issued, the select statement can be repeated by other users as much as they like and they see the original figure, not the updated figure. That doesn't appear until the commit command it issued.