Optimistic Locking (2)

Optimistic locking concurrency control systems provide high levels of scalability by checking for data collisions only when information is stored. In this article, the second part of three, a simple optimistic locking system is created using SQL Server.

Reading a Customer

Reading of customers will be controlled by a stored procedure. This procedure will accept the unique ID for a customer and return that customer's details and version number. The TIMESTAMP will be converted to a big integer as this is easier for clients to consume and will make testing the stored procedures simpler.

To create the ReadCustomer stored procedure, execute the following:

CREATE PROCEDURE ReadCustomer
(
    @CustomerId BIGINT
)
AS
BEGIN
    SET NOCOUNT ON

    SELECT
        CustomerId,
        CustomerName,
        EmailAddress,
        CONVERT(BIGINT, LockId) AS LockId
    FROM
        Customers
    WHERE
        CustomerId = @CustomerId
END

Saving a Customer

When a customer has been updated and is saved, the SaveCustomer stored procedure is called. This accepts the new details for the customer and the version number that was originally read. To prevent collisions, the stored procedure runs an UPDATE statement that updates the row where both the customer ID and lock ID match those provided as arguments. If the customer has been updated by another user, the LockId value will be different and the update will not modify any data rows. This is detected by checking the @@ROWCOUNT variable following the update. If the row count is zero, an error is raised. If a row was updated, the procedure returns normally.

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

    UPDATE
        Customers
    SET
        CustomerName = @CustomerName,
        EmailAddress = @EmailAddress
    WHERE
        CustomerId = @CustomerId
    AND
        LockId = @LockID

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