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

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
13 March 2010