Pessimistic Locking

by Richard Carr, published at http://www.blackwasp.co.uk/PessimisticLocking.aspx

Pessimistic locking concurrency control systems use locking to prevent collisions caused when multiple users attempt to access the same shared data. In this article, the final part of three, a simple pessimistic locking system is created using SQL Server.

Implementing Pessimistic Locking

This is the third and final instalment in a series of articles describing concurrency control systems. In the first article I described the use of concurrency control in multi-user systems to avoid data integrity issues. This included descriptions of optimistic locking and pessimistic locking. In the second article we created a simple optimistic locking system using SQL Server stored procedures. In this final part we will create a similar pessimistic locking system.

The pessimistic locking system will be created using a SQL Server database containing a single table of customers. As in the previous article, each customer includes a unique identifier, the name of the customer and their email address. The locking system will add an extra column to the table that contains the name of the user that currently holds a lock against the row. For more complex systems you could extend this mechanism to all lockable tables or create a centralised table that holds lock data for all information types.

To keep the examples simple, stored procedures will be created to read or update a customer and to request and release locks. In a real system, further procedures may be included for creating new customers, deleting existing customers and performing bulk updates. These will not be included in this article.

Creating the Customers Table

The Customers table includes four columns. Three of these hold the customer data and one holds the lock information. The lock will be defined by storing the name of a user in the column. When unlocked, the column's value will be NULL. To create the Customers table, create a new database and execute the following script.

CREATE TABLE Customers
(
    CustomerId BIGINT NOT NULL,
    CustomerName VARCHAR(50) NOT NULL,
    EmailAddress VARCHAR(100) NOT NULL,
    LockedBy VARCHAR(20),
    CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (CustomerId)
)

To create a sample customer that is not locked, execute the following:

INSERT INTO Customers
VALUES (1, 'ABC Limited', 'enquiries@abc.co', null)

Locking a Customer

In the example pessimistic locking system, the process for editing a customer will involve four stages. These are:

• Lock a customer for editing.
• Read the customer details from the database.
• Save updated information to the database.
• Release the lock.

To lock a customer, the user's name will be recorded against the required row in the table using the LockCustomer stored procedure. To eliminate the risk of two people attempting to create a lock on the same row at the same time, the stored procedure will attempt to lock the row where the customer ID matches that provided in an argument and where the current LockedBy value is NULL. If another user has obtained a lock on the row, no rows will be updated. This is detected and an error raised.

To create the stored procedure, execute the following.

CREATE PROCEDURE LockCustomer
(
    @CustomerId BIGINT,
    @LockingUser VARCHAR(20)
)
AS
BEGIN
    SET NOCOUNT ON

    UPDATE
        Customers
    SET
        LockedBy = @LockingUser
    WHERE
        CustomerId = @CustomerId
    AND
        LockedBy IS NULL

    IF @@ROWCOUNT = 0
    BEGIN
        RAISERROR('Customer already locked!',16,1)
    END
END

Reading a Customer

Reading a customer is achieved with a second stored procedure. This procedure requires two arguments to be provided containing the customer ID and the name of the user that wishes to read the information. The details of the customer and the user who currently holds a lock will be returned. To prevent dirty reads, where the data is being edited by another user and may be out of date, the customer details will only be returned if the customer is not locked, or if it is locked by the user calling the procedure.

To create the ReadCustomer procedure, execute the script below:

CREATE PROCEDURE ReadCustomer
(
    @CustomerId BIGINT,
    @ReadingUser VARCHAR(20)
)
AS
BEGIN
    SET NOCOUNT ON

    SELECT
        CustomerId,
        CustomerName,
        EmailAddress,
        LockedBy
    FROM
        Customers
    WHERE
        CustomerId = @CustomerId
    AND
        isnull(LockedBy, @ReadingUser) = @ReadingUser
END