tailieunhanh - Implementing Pessimistic Concurrency Without Using Database Locks

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