Recipe 6.13 Implementing Pessimistic Concurrency Without Using Database Locks

Problem

You need the safety of pessimistic locking without the overhead of database locks.

Solution

Use extra columns and stored procedures as shown in the following examples.

The schema of table TBL0613 used in this solution is shown in Table 6-19.

Table 6-19. TBL0613 schema

Column name	Data type	Length	Allow nulls?
Id	int	4	No
Field1	nvarchar	50	Yes
Field2	nvarchar	50	Yes
LockId	uniqueidentifier	16	Yes
LockDateTime	datetime	8	Yes

The sample uses seven stored procedures, which are shown in Example 6-31 through Example 6-37:

SP0613_AcquireLock

Used to lock a record specified by an Id parameter in the table TBL0613 in the database. The lock is effected by setting the LockId field of an unlocked record, where the value of the LockId field is null, to a GUID specified by an input parameter.

SP0613_ReleaseLock

Used to clear the lock on a record in the table TBL0613 by setting both the LockId and LockDateTime columns to null. The record is identified by an Id parameter. A LockId parameter—obtained by executing the SP0613_AcquireLock stored procedure—must be supplied to clear the lock on a record.

SP0613_Delete

Used to delete a record specified by an Id parameter from the table TBL0613 in the database. A LockId parameter—obtained by executing the SP0613_AcquireLock stored procedure—must be supplied to delete the record.

SP0613_Get

Used to retrieve a record specified by an Id parameter or all records from the table TBL0613 in the database. An expression column, called IsLocked, is also returned indicating whether the row is currently locked by any user.

SP0613_Insert

Used to insert a new record into the table TBL0613 in the database.

SP0613_Update

Used to update a record in the table TBL0613 in the database. A LockId parameter—obtained by executing the SP0613_AcquireLock stored procedure—must be supplied to update the record.

SP0613_PurgeExpired

Used to remove locks older than a specified number of seconds by setting the LockId and LockDateTime values for those records to null.

Example 6-31. Stored procedure: SP0613_AcquireLock

CREATE PROCEDURE SP0613_AcquireLock
    @Id int,
    @LockId uniqueidentifier
AS

    update TBL0613
    set
        LockID=@LockID,
        LockDateTime=GetDate( ) 
    where
        Id=@Id and
        LockId IS NULL
        
    return @@rowcount

Example 6-32. Stored procedure: SP0613_ReleaseLock

CREATE PROCEDURE SP0613_ReleaseLock
    @Id int,
    @LockID uniqueidentifier
AS
    update
        TBL0613
    set
        LockId=NULL,
        LockDateTime=NULL
    where
        Id=@Id and
        LockID=@LockID
        
    return @@rowcount

Example 6-33. Stored procedure: SP0613_Delete

CREATE PROCEDURE SP0613_Delete
    @Id int,
    @LockID uniqueidentifier
AS
    SET NOCOUNT ON
    
    delete
    from
        TBL0613
    where
        Id=@Id and
        LockId=@LockId
        
    return @@ROWCOUNT

Example 6-34. Stored procedure: SP0613_Get

CREATE PROCEDURE SP0613_Get
    @Id int=null
AS
    SET NOCOUNT ON
    
    if @Id is not null
    begin
        select
            Id,
            Field1,
            Field2,
            IsLocked =
            case
                when LockId is null then 0
                else 1
            end
        from
            TBL0613
        where
            Id=@Id
            
        return 0
    end
    
    select
        Id,
        Field1,
        Field2,
        IsLocked =
        case
            when LockId is null then 0
            else 1
        end
    from
        TBL0613
        
    return 0

Example 6-35. Stored procedure: SP0613_Insert

CREATE PROCEDURE SP0613_Insert
    @Id int,
    @Field1 nvarchar(50),
    @Field2 nvarchar(50)
AS
    SET NOCOUNT ON
    
    insert TBL0613(
        Id,
        Field1,
        Field2)
    values (
        @Id,
        @Field1,
        @Field2)
    
    if @@rowcount=0
        return 1
            
    return 0

Example 6-36. Stored procedure: SP0613_Update

CREATE PROCEDURE SP0613_Update
    @Id int,
    @Field1 nvarchar(50)=null,
    @Field2 nvarchar(50)=null,
    @LockID uniqueidentifier
AS
    update
        TBL0613
    set
        Field1=@Field1,
        Field2=@Field2
    where
        Id=@Id and
        LockId=@LockId
    
    return @@ROWCOUNT

Example 6-37. Stored procedure: SP0613_PurgeExpired

CREATE PROCEDURE SP0613_PurgeExpired
    @timeoutSec int
AS
    SET NOCOUNT ON
    
    UPDATE TBL0613
    SET
        LockId = null,
        LockDateTime = null
    WHERE
        DATEADD(s, @timeoutSec, LockDateTime) < GETDATE( );
    
    RETURN

The sample code contains seven event handlers:

Form.Load

Sets up the sample by creating a DataTable representing the table TBL0613 in the database. A DataAdapter is created and the select, delete, insert, and update commands are built using the stored procedures for the solution. A RowUpdated event handler is attached to the DataAdapter. The schema and data for table TBL0613 is loaded into the DataTable. The ReadOnly property of the IsLocked expression field is set to false so that it can be modified programmatically as required. A LockId column is added to the DataTable to hold acquired locks because LockId values cannot be returned from TBL0613 since doing so would allow other users to retrieve locks other than their own. Finally, the default view of the table is bound to the data grid on the form.

Lock Button.Click

Used to acquire locks on all rows in the DataTable. The stored procedure SP0613_AcquireLock is executed for each row. A GUID is generated programmatically and passed as the LockId parameter into the stored procedure along with the Id for the row. A message is displayed indicating the success or failure of the locking attempt for each row.

Release Button.Click

Used to release locks on all rows in the DataTable. The stored procedure SP0613_ReleaseLock is executed for each row. The currently held LockId is passed into the stored procedure along with the Id for the row. A message is displayed indicating the success or failure of the lock release attempt for each row.

Force Release Button.Click

Executes a SQL statement that removes all locks on all rows in TBL0613 in the database. This is accomplished by setting the values of the LockId and LockDateTime fields to null.

Update Button.Click

Uses a DataAdapter to reconcile changes made to the DataTable with the table TBL0613 in the database.

Refresh Button.Click

Clears the DataTable and uses the DataAdapter to fill the table with all data from the table TBL0613 in the database.

DataAdapter.RowUpdated

Checks if a row was affected when an attempt was made to update or delete it. In both cases, the return value parameter from the stored procedure used to perform the action is 1 for success and 0 for failure. A failure results in the RowError property of the row being set.

The C# code is shown in Example 6-38.

Example 6-38. File: PessimisticUpdatesForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Text;
using System.Data;
using System.Data.SqlClient;

private DataTable dt;
private SqlDataAdapter da;

private const String TABLENAME="TBL0613";

// Table column name constants 
private const String ID_FIELD            = "Id";
private const String FIELD1_FIELD        = "Field1";
private const String FIELD2_FIELD        = "Field2";
private const String LOCKID_FIELD        = "LockId";
private const String LOCKDATETIME_FIELD  = "LockDateTime";
// Expression in table
private const String ISLOCKED_FIELD      = "IsLocked";

// Stored procedure name constants
private const String DELETE_SP           = "SP0613_Delete";
private const String GET_SP              = "SP0613_Get";
private const String INSERT_SP           = "SP0613_Insert";
private const String UPDATE_SP           = "SP0613_Update";

private const String ACQUIRELOCK_SP      = "SP0613_AcquireLock";
private const String RELEASELOCK_SP      = "SP0613_ReleaseLock";

// Stored procedure parameter name constants for table
private const String ID_PARM             = "@Id";
private const String FIELD1_PARM         = "@Field1";
private const String FIELD2_PARM         = "@Field2";
private const String LOCKID_PARM         = "@LockId";

private const String RETVAL_PARM         = "@RetVal";

//  . . . 

private void PessimisticUpdatesForm_Load(object sender, System.EventArgs e)
{
    // Build the table.
    dt = new DataTable(TABLENAME);

    // Create the DataAdapter.
    da = new SqlDataAdapter( );
    // Add a handler for the RowUpdated event.
    da.RowUpdated += new SqlRowUpdatedEventHandler(da_RowUpdated);

    // Create a connection.
    SqlConnection conn = new SqlConnection(
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);

    // Build the select command.
    SqlCommand selectCommand = new SqlCommand(GET_SP, conn);
    selectCommand.CommandType = CommandType.StoredProcedure;
    da.SelectCommand = selectCommand;

    // Build the delete command.
    SqlCommand deleteCommand = new SqlCommand(DELETE_SP, conn);
    deleteCommand.CommandType = CommandType.StoredProcedure;
    deleteCommand.Parameters.Add(ID_PARM, SqlDbType.Int, 0, ID_FIELD);
    deleteCommand.Parameters.Add(LOCKID_PARM, SqlDbType.UniqueIdentifier,
        0, LOCKID_FIELD);
    deleteCommand.Parameters.Add(RETVAL_PARM, SqlDbType.Int).Direction =
        ParameterDirection.ReturnValue;
    da.DeleteCommand = deleteCommand;

    // Build the insert command.
    SqlCommand insertCommand = new SqlCommand(INSERT_SP, conn);
    insertCommand.CommandType = CommandType.StoredProcedure;
    insertCommand.Parameters.Add(ID_PARM, SqlDbType.Int, 0, ID_FIELD);
    insertCommand.Parameters.Add(FIELD1_PARM, SqlDbType.NVarChar, 50,
        FIELD1_FIELD);
    insertCommand.Parameters.Add(FIELD2_PARM, SqlDbType.NVarChar, 50,
        FIELD2_FIELD);
    da.InsertCommand = insertCommand;

    // Build the update command.
    SqlCommand updateCommand = new SqlCommand(UPDATE_SP, conn);
    updateCommand.CommandType = CommandType.StoredProcedure;
    updateCommand.Parameters.Add(ID_PARM, SqlDbType.Int, 0, ID_FIELD);
    updateCommand.Parameters.Add(FIELD1_PARM, SqlDbType.NVarChar, 50,
        FIELD1_FIELD);
    updateCommand.Parameters.Add(FIELD2_PARM, SqlDbType.NVarChar, 50,
        FIELD2_FIELD);
    updateCommand.Parameters.Add(LOCKID_PARM, SqlDbType.UniqueIdentifier,
        0, LOCKID_FIELD);
    updateCommand.Parameters.Add(RETVAL_PARM, SqlDbType.Int).Direction =
        ParameterDirection.ReturnValue;
    da.UpdateCommand = updateCommand;

    // Fill the table.
    da.FillSchema(dt, SchemaType.Source);
    da.Fill(dt);

    // Unlock the IsLocked expression column.
    dt.Columns[ISLOCKED_FIELD].ReadOnly = false;

    // Add a column to the table to control the locking.
    dt.Columns.Add("LockId", typeof(Guid));

    // Bind the default view of the table to the grid.
    dataGrid.DataSource = dt.DefaultView;
}

private void lockButton_Click(object sender, System.EventArgs e)
{
    StringBuilder sb = new StringBuilder( );

    // Lock all of the rows in the table.
    SqlConnection conn = new SqlConnection(
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);

    // Create a command for the lock stored procedure.
    SqlCommand cmd = new SqlCommand( );
    cmd.Connection = conn;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = ACQUIRELOCK_SP;
    cmd.Parameters.Add(ID_PARM, SqlDbType.Int);
    cmd.Parameters.Add(LOCKID_PARM, SqlDbType.UniqueIdentifier);
    cmd.Parameters.Add(RETVAL_PARM, SqlDbType.Int).Direction =
        ParameterDirection.ReturnValue;

    conn.Open( );
    // Iterate over the row collection for the table.
    foreach(DataRow row in dt.Rows)
    {
        // Generate a lock ID.
        Guid lockId = Guid.NewGuid( );

        // Execute the lock command to acquire a lock on the row.
        cmd.Parameters[ID_PARM].Value = row[ID_FIELD];
        cmd.Parameters[LOCKID_PARM].Value = lockId;
        cmd.ExecuteNonQuery( );
        if((int)cmd.Parameters[RETVAL_PARM].Value == 0)
        {
            // Row lock could not be acquired
            sb.Append("Could not aquire lock on row [ID = " +
                row[ID_FIELD] + "]." + Environment.NewLine);
            row[LOCKID_FIELD] = DBNull.Value;
        }
        else
        {
            // Row lock acquired
            row[LOCKID_FIELD] = lockId;
            row[ISLOCKED_FIELD] = 1;
        }
    conn.Close( );

    // Display an error message for locks that could not be acquired.
    if(sb.Length > 0)
        MessageBox.Show(sb.ToString( ), "Simulate Pessimistic Locking",
        MessageBoxButtons.OK, MessageBoxIcon.Error);
}

private void releaseButton_Click(object sender, System.EventArgs e)
{
    StringBuilder sb = new StringBuilder( );

    // Release lock on all of the rows in the table.
    SqlConnection conn = new SqlConnection(
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);

    // Create a command for the release stored procedure.
    SqlCommand cmd = new SqlCommand( );
    cmd.Connection = conn;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = RELEASELOCK_SP;
    cmd.Parameters.Add(ID_PARM, SqlDbType.Int);
    cmd.Parameters.Add(LOCKID_PARM, SqlDbType.UniqueIdentifier);
    cmd.Parameters.Add(RETVAL_PARM, SqlDbType.Int).Direction =
        ParameterDirection.ReturnValue;

    conn.Open( );
    // Iterate over the collection of rows in the table.
    foreach(DataRow row in dt.Rows)
    {
        // Execute the command to release the lock on the row.
        cmd.Parameters[ID_PARM].Value = row[ID_FIELD];
        cmd.Parameters[LOCKID_PARM].Value = row[LOCKID_FIELD];
        cmd.ExecuteNonQuery( );
        if((int)cmd.Parameters[RETVAL_PARM].Value == 0)
            // Row lock could not be released
            sb.Append("Could not release lock on row [ID = " +
                row[ID_FIELD] + "]." + Environment.NewLine);
        else
        {
            // Row lock released
            row[LOCKID_FIELD] = DBNull.Value;
            row[ISLOCKED_FIELD] = 0;
        }
    }
    conn.Close( );

    // Display an error message for locks which could not be released.
    if(sb.Length > 0)
        MessageBox.Show(sb.ToString( ), "Simulate Pessimistic Locking",
        MessageBoxButtons.OK, MessageBoxIcon.Error);
}

private void forceReleaseButton_Click(object sender, System.EventArgs e)
{
    // Normally, security would be used to block this statement
    // from being executed.
    // Clear all of the locks that exist on the table.

    // Create the connection.
    SqlConnection conn = new SqlConnection(
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    String sqlText = "UPDATE " + TABLENAME + " SET " + LOCKID_FIELD + " =
        NULL, " + LOCKDATETIME_FIELD + " = NULL";
    // Create and execute the command to force release on all rows.
    SqlCommand cmd = new SqlCommand(sqlText, conn);
    conn.Open( );
    cmd.ExecuteNonQuery( );
    conn.Close( );

    // Update the lock ID.
    foreach(DataRow row in dt.Rows)
    {
        row[LOCKID_FIELD] = DBNull.Value;
        row[ISLOCKED_FIELD] = 0;
    }

    MessageBox.Show("All row locks on table released.",
        "Simulate Pessimistic Locking", MessageBoxButtons.OK,
        MessageBoxIcon.Information);
}

private void updateButton_Click(object sender, System.EventArgs e)
{
    try
    {
        // Use the DataAdapter to update the table.
        da.Update(dt);
    }
    catch(Exception ex)
    {
        // Display error message if the row is not locked for update.
        MessageBox.Show("ERROR: " + ex.Message,
            "Simulate Pessimistic Locking", MessageBoxButtons.OK,
            MessageBoxIcon.Error);
    }
}

private void refreshButton_Click(object sender, System.EventArgs e)
{
    // Refresh the data from the source.
    dt.Clear( );
    da.Fill(dt);        
}

private void da_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
{
    if(e.StatementType == StatementType.Update &&
        (int)e.Command.Parameters[RETVAL_PARM].Value == 0)
    {
        // Row error if row could not be updated without lock
        e.Row.RowError = "Lock required to update this row.";
        // Continue processing the update for the other rows.
        e.Status = UpdateStatus.Continue;
    }

    if(e.StatementType == StatementType.Delete &&
        (int)e.Command.Parameters[RETVAL_PARM].Value == 0)
    {
        // Row error if row could not be deleted without lock
        e.Row.RowError = "Lock required to delete this row.";
        // Continue processing the update for the other rows.
        e.Status = UpdateStatus.Continue;
    }
}

Discussion

Pessimistic concurrency prevents other users from modifying data that a user is reading by locking rows of data at the data source. Other users cannot perform actions that affect the locked row until the current lock holder releases the lock. Pessimistic concurrency protects data integrity without requiring transaction rollbacks but with reduced data availability and increased resources needed to maintain server locks. A persistent connection to the database server is required, limiting scalability.

Optimistic concurrency does not lock data while it is being read. Instead, if the user wants to make a change to the data, the application determines whether another user has changed the data since it was last read. If the data has been modified, a violation is considered to have occurred. Often, the user is notified of the changes made by other users and given an opportunity to resubmit changes. Optimistic concurrency protects data integrity with transaction rollbacks and provides higher data availability without needing additional server resources for the locks. Connections to the database do not need to be persistent, making the solution more scalable. Optimistic concurrency requires a more complex programming model because violations and transactions need to be handled.

The solution simulates pessimistic locking by adding two columns to each table in which locking is simulated as shown in Table 6-20.

Table 6-20. Pessimistic locking columns

Field	Data type	Length	Allow nulls?
LockId	uniqueidentifier	16	Yes
LockDateTime	datetime	8	Yes

The LockId column is a GUID generated by the user representing a lock on a row. An unlocked row has a null value for the LockId. The LockId cannot be accessed directly by any user because doing so would allow a user to retrieve the lock issued to another user rendering the technique useless. The solution returns a Boolean IsLocked value indicating whether the field is locked by another user.

The LockDateTime field contains the date and time that a lock was issued to a user. It is used by a SQL Server Agent job that clears locks held for longer than a duration specified in seconds by periodically executing the stored procedure SP0613_PurgeExpired.