Optimistic Locking

by Richard Carr, published at http://www.blackwasp.co.uk/OptimisticLocking.aspx

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.

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.

EXEC ReadCustomer 1

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.

EXEC SaveCustomer 1, 'ABC Limited', 'admin@abc.co', 1234

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.

EXEC SaveCustomer 1, 'ABC Ltd', 'enquiries@abc.co', 1234

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.