ADO.NET Cookbook

6.13. Implementing Pessimistic Concurrency Without Using Database Locks

Problem

You need the safety of pessimistic locking without the overhead of database locks.

Solution

Use extra columns and stored procedures as shown in the following examples.

The schema of table TBL0613 used in this solution is shown in Table 6-19.

Table 6-19. TBL0613 schema

Column name	Data type	Length	Allow nulls?
Id	int	4	No
Field1	nvarchar	50	Yes
Field2	nvarchar	50	Yes
LockId	uniqueidentifier	16	Yes
LockDateTime	datetime	8	Yes

The sample uses seven stored procedures, which are shown in Example 6-31 through Example 6-37:

SP0613_AcquireLock

Used to lock a record specified by an Id parameter in the table TBL0613 in the database. The lock is effected by setting the LockId field of an unlocked record, where the value of the LockId field is null, to a GUID specified by an input parameter.

SP0613_ReleaseLock

Used to clear the lock on a record in the table TBL0613 by setting both the LockId and LockDateTime columns to null. The record is identified by an Id parameter. A LockId parameter—obtained by executing the SP0613_AcquireLock stored procedure—must be supplied to clear the lock on a record.

SP0613_Delete

Used to delete a record specified by an Id parameter from the table TBL0613 in the database. A LockId parameter—obtained by executing the SP0613_AcquireLock stored procedure—must be supplied to delete the record.

SP0613_Get

Used to retrieve a record specified by an Id parameter or all records ...