Pessimistic Locking (2)

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.

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