[ Team LiB ]Recipe 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

return @@rowcount
Example 6-32. Stored procedure: SP0613_ReleaseLock
CREATE PROCEDURE SP0613_ReleaseLock
@Id int,
@LockID uniqueidentifier
AS
update
TBL0613
set
LockId=NULL,
LockDateTime=NULL
where
Id=@Id and
LockID=@LockID

return @@rowcount
Example 6-33. Stored procedure: SP0613_Delete
CREATE PROCEDURE SP0613_Delete
@Id int,
@LockID uniqueidentifier
AS
SET NOCOUNT ON

delete
from
TBL0613
where
Id=@Id and
LockId=@LockId


IsLocked =
case
when LockId is null then 0
else 1
end
from
TBL0613

return 0
Example 6-35. Stored procedure: SP0613_Insert
CREATE PROCEDURE SP0613_Insert
@Id int,
@Field1 nvarchar(50),
@Field2 nvarchar(50)
AS
SET NOCOUNT ON

insert TBL0613(
Id,
Field1,
Field2)
values (
@Id,
@Field1,
@Field2)

if @@rowcount=0
return 1

return 0