Optimistic Locking

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.

Implementing Optimistic Locking

This is the second instalment in a series of three 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 will create a simple optimistic locking system using SQL Server stored procedures. You will be able to use the concepts described here to extend this to your own projects that use a SQL Server database as a central storage point, or modify the code for alternative database management systems (DBMS).

The optimistic locking system will be implemented against a database containing a single table. This table stores the details of customers and includes a customer identification number, the customer name and an email address. The locking system will use a version number for each customer that is updated each time the customer's details are changed. This version number will be read when a user opens a customer row for editing and will be required when writing back to the database.

To keep the examples as simple as possible, stored procedures will be created to read or update a customer. 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 the example.

Creating the Customers Table

The Customers table includes four columns. Three of these hold the customer data and one holds a version number. The version number column, named LockId, is of the TIMESTAMP data type. This data type assigns a value to the column when a new customer is created and automatically updates the value every time a row is modified. It is perfect for our requirements and means that no additional code to process version numbers is required.

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,
    LockId TIMESTAMP NOT NULL,
    CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (CustomerId)
)

To create a sample customer, execute the following:

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

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

Testing the Optimistic Locking

To test the stored procedures we can execute them against the test customer. To begin, we'll read the customer by executing the following. Make a note of the LockId version number that is returned.

To simulate a user editing the customer's email address and saving the data, execute the following. Change the '1234' value to the LockId value returned from ReadCustomer.

If you examine the data in the table you will find that the email address has been updated. A new version number should also have been allocated to the customer row.

To test the process when a collision occurs, we can assume that two users were editing the customer at the same time. The first user has already correctly updated the email address. If the second were to try to save the customer with a new customer name, they should be prevented from doing so. To test this, execute the following script. Again, substitute '1234' with the original LockId from ReadCustomer.

This time an error is displayed and the update fails. If you view the data in the table you will see that the customer name and the version number remain unchanged.