Pessimistic Locking

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

Saving a Customer

Once a customer has been updated, the SaveCustomer stored procedure will be used to store the new details. Again, the name of the user who owns the lock must be provided to one of the procedure's parameters. This name will be used during the update to prevent users that do not own a lock from modifying the customer data.

CREATE PROCEDURE SaveCustomer
(
    @CustomerId BIGINT,
    @CustomerName VARCHAR(50),
    @EmailAddress VARCHAR(100),
    @LockingUser VARCHAR(20)
)
AS
BEGIN
    SET NOCOUNT ON

    UPDATE
        Customers
    SET
        CustomerName = @CustomerName,
        EmailAddress = @EmailAddress
    WHERE
        CustomerId = @CustomerId
    AND
        LockedBy = @LockingUser

    IF @@ROWCOUNT = 0
    BEGIN
        RAISERROR('Update failed!',16,1)
    END
END

Unlocking a Customer

The last stored procedure is used to release a customer lock once a user has completed their changes. This stored procedure changes the value in the LockedBy column to NULL to indicate that no lock is present. The name of the user that currently owns the lock is passed to the procedure and used to ensure that the lock cannot be released by an incorrect user.

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

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

    IF @@ROWCOUNT = 0
    BEGIN
        RAISERROR('Unlock failed!',16,1)
    END
END

Testing the Pessimistic Locking

We can test the locking mechanism by calling the four stored procedures in the order that they would be used in a real application. Let's start by locking the sample customer row, allocating the lock to a user named, "Jim".

If another user attempts to obtain a lock for the same customer, the action will fail. The following should cause an error to be raised:

Jim can now read the customer details in preparation for editing:

However, if Bob tries to read the customer, no rows are returned:

Jim can edit the customer details and save the details back to the centralised database:

Bob is prevented from saving details for the customer:

Bob is also prevented from trying to unlock the customer so that he can obtain his own lock:

Finally, Jim can unlock the customer so that it is once again available to other users:

Once unlocked, the customer can be read by any user without the risk of a dirty read.