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 ...

Get ADO.NET Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.