BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

Design Patterns

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.

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".

EXEC LockCustomer 1, '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:

EXEC LockCustomer 1, 'Bob'

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

EXEC ReadCustomer 1, 'Jim'

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

EXEC ReadCustomer 1, 'Bob'

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

EXEC SaveCustomer 1, 'ABC Ltd', 'enquiries@abc.co', 'Jim'

Bob is prevented from saving details for the customer:

EXEC SaveCustomer 1, 'ABC Limited', 'admin@abc.co', 'Bob'

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

EXEC UnlockCustomer 1, 'Bob'

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

EXEC UnlockCustomer 1, 'Jim'

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

EXEC ReadCustomer 1, 'Bob'
15 March 2010