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)