Implementing Pessimistic Concurrency Without Using Database Locks

[ 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.
Nội dung trích xuất từ tài liệu:
Implementing Pessimistic Concurrency Without Using Database Locks[ Team LiB ]Recipe 6.13 Implementing Pessimistic Concurrency Without Using Database LocksProblemYou need the safety of pessimistic locking without the overhead of database locks.SolutionUse 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 NoField1 nvarchar 50 YesField2 nvarchar 50 YesLockId uniqueidentifier 16 YesLockDateTime datetime 8 YesThe sample uses seven stored procedures, which are shown in Example 6-31 throughExample 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 from the table TBL0613 in the database. An expression column, called IsLocked, is also returned indicating whether the row is currently locked by any user.SP0613_Insert Used to insert a new record into the table TBL0613 in the database.SP0613_Update Used to update a record in the table TBL0613 in the database. A LockId parameter—obtained by executing the SP0613_AcquireLock stored procedure— must be supplied to update the record.SP0613_PurgeExpired Used to remove locks older than a specified number of seconds by setting the LockId and LockDateTime values for those records to null.Example 6-31. Stored procedure: SP0613_AcquireLockCREATE PROCEDURE SP0613_AcquireLock @Id int, @LockId uniqueidentifierAS update TBL0613 set LockID=@LockID, LockDateTime=GetDate( ) where Id=@Id and LockId IS NULL return @@rowcountExample 6-32. Stored procedure: SP0613_ReleaseLockCREATE PROCEDURE SP0613_ReleaseLock @Id int, @LockID uniqueidentifierAS update TBL0613 set LockId=NULL, LockDateTime=NULL where Id=@Id and LockID=@LockID return @@rowcountExample 6-33. Stored procedure: SP0613_DeleteCREATE PROCEDURE SP0613_Delete @Id int, @LockID uniqueidentifierAS SET NOCOUNT ON delete from TBL0613 where Id=@Id and LockId=@LockId return @@ROWCOUNTExample 6-34. Stored procedure: SP0613_GetCREATE PROCEDURE SP0613_Get @Id int=nullAS SET NOCOUNT ON if @Id is not null begin select Id, Field1, Field2, IsLocked = case when LockId is null then 0 else 1 end from TBL0613 where Id=@Id return 0 end select Id, Field1, Field2, IsLocked = case when LockId is null then 0 else 1 end from TBL0613 return 0Example 6-35. Stored procedure: SP0613_InsertCREATE 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 0Example 6-36. Stored procedure: SP0613_UpdateCREATE PROCEDURE SP0613_Update @Id int, @Field1 nvarchar(50)=null, @Field2 nvarchar(50)=null, @LockID uniqueidentifierAS update TBL0613 set Field1=@Field1, Field2=@Field2 where Id=@Id and LockId=@LockId return @@ROWCOUNTExample 6-37. Stored procedure: SP0613_PurgeExpiredCREATE PROCEDURE SP0613_PurgeExpired @timeoutSec intAS SET NOCOUNT ON UPDATE TBL0613 SET LockId = null, LockDateTime = null WHERE DATEADD(s, @timeoutSec, LockDateTime) < GETDATE( ); RETURNThe sample code contains seven event handlers:Form.Load Sets up the sample by creating a DataTable representing the table TBL0613 in the database. A DataAdapter is created and the select, delete, in ...