Pessimistic Locking
Pessimistic locking concurrency control systems use locking to prevent collisions caused when multiple users attempt to access the same shared data. In this article, the final part of three, a simple pessimistic locking system is created using SQL Server.
Saving a Customer
Once a customer has been updated, the SaveCustomer stored procedure will be used to store the new details. Again, the name of the user who owns the lock must be provided to one of the procedure's parameters. This name will be used during the update to prevent users that do not own a lock from modifying the customer data.
CREATE PROCEDURE SaveCustomer
(
@CustomerId BIGINT,
@CustomerName VARCHAR(50),
@EmailAddress VARCHAR(100),
@LockingUser VARCHAR(20)
)
AS
BEGIN
SET NOCOUNT ON
UPDATE
Customers
SET
CustomerName = @CustomerName,
EmailAddress = @EmailAddress
WHERE
CustomerId = @CustomerId
AND
LockedBy = @LockingUser
IF @@ROWCOUNT = 0
BEGIN
RAISERROR('Update failed!',16,1)
END
END
Unlocking a Customer
The last stored procedure is used to release a customer lock once a user has completed their changes. This stored procedure changes the value in the LockedBy column to NULL to indicate that no lock is present. The name of the user that currently owns the lock is passed to the procedure and used to ensure that the lock cannot be released by an incorrect user.
CREATE PROCEDURE UnlockCustomer
(
@CustomerId BIGINT,
@LockingUser VARCHAR(20)
)
AS
BEGIN
SET NOCOUNT ON
UPDATE
Customers
SET
LockedBy = NULL
WHERE
CustomerId = @CustomerId
AND
LockedBy = @LockingUser
IF @@ROWCOUNT = 0
BEGIN
RAISERROR('Unlock failed!',16,1)
END
END
Testing the Pessimistic Locking
We can test the locking mechanism by calling the four stored procedures in the order that they would be used in a real application. Let's start by locking the sample customer row, allocating the lock to a user named, "Jim".
EXEC LockCustomer 1, 'Jim'
If another user attempts to obtain a lock for the same customer, the action will fail. The following should cause an error to be raised:
EXEC LockCustomer 1, 'Bob'
Jim can now read the customer details in preparation for editing:
EXEC ReadCustomer 1, 'Jim'
However, if Bob tries to read the customer, no rows are returned:
EXEC ReadCustomer 1, 'Bob'
Jim can edit the customer details and save the details back to the centralised database:
EXEC SaveCustomer 1, 'ABC Ltd', 'enquiries@abc.co', 'Jim'
Bob is prevented from saving details for the customer:
EXEC SaveCustomer 1, 'ABC Limited', 'admin@abc.co', 'Bob'
Bob is also prevented from trying to unlock the customer so that he can obtain his own lock:
EXEC UnlockCustomer 1, 'Bob'
Finally, Jim can unlock the customer so that it is once again available to other users:
EXEC UnlockCustomer 1, 'Jim'
Once unlocked, the customer can be read by any user without the risk of a dirty read.
EXEC ReadCustomer 1, 'Bob'
15 March 2010