SQL Server Optimistic Locking and pessimistic locking

In the actual production environment where multiple users access data concurrently, we often try to maintain data consistency as much as possible. The most typical example is to read data from the table, check and verify the data, and then write it back to the database. When reading and writing data, it is very likely that other users have modified the data you want to modify in a multi-user concurrent environment, this results in data inconsistency. To solve this problem, SQL server puts forward the concepts of Optimistic Locking and pessimistic locking. I will use an example below to illustrate how to use Optimistic Locking and pessimistic locking to solve this problem.

/* Create a test table: Card, which represents a real Card library for user registration. the user needs to select an unused card, that is, the F_Flag = 0 card, and register it with the user: update the F_Name, F_Time, F_Flag fields. if two users update a card at the same time, it is intolerable, that is, the data inconsistency. */

Create table Card (F_CardNO varchar (20), F_Name varchar (20), F_Flag bit, F_Time datetime)
Go
Insert Card (F_CardNo, F_Flag) select '2014-1111 ', 0
Insert Card (F_CardNo, F_Flag) select '2014-1111 ', 0
Insert Card (F_CardNo, F_Flag) select '2014-1111 ', 0
Insert Card (F_CardNo, F_Flag) select '2014-1111 ', 0
Insert Card (F_CardNo, F_Flag) select '2014-1111 ', 0
Insert Card (F_CardNo, F_Flag) select '2014-1111 ', 0
Insert Card (F_CardNo, F_Flag) select '2014-1111 ', 0
Insert Card (F_CardNo, F_Flag) select '2014-1111 ', 0
Insert Card (F_CardNo, F_Flag) select '2014-1111 ', 0
Insert Card (F_CardNo, F_Flag) select '2014-1111 ', 0
Go

-- Below is a frequently used update solution:


Declare @ CardNo varchar (20)
Begin Tran

-- Select an unused card
Select top 1 @ CardNo = F_CardNo
From Card where F_Flag = 0

-- Delay 50 seconds to simulate concurrent access.
Waitfor delay '000: 00: 50'

-- Register the selected card.

Update Card
Set F_Name = user,
F_Time = getdate (),
F_Flag = 1
Where F_CardNo = @ CardNo

Commit

Problem: If we execute the same piece of code in the same window, but remove the waitfor delay clause. after the two sides are completed, we found that although two registrations were performed, only one card was registered, that is, two people registered the same card.

Pessimistic locking Solution

-- We only need to make minor changes to the above Code to implement pessimistic locking.

Declare @ CardNo varchar (20)
Begin Tran

-- Select an unused card
Select top 1 @ CardNo = F_CardNo
From Card with (UPDLOCK) where F_Flag = 0

-- Delay 50 seconds to simulate concurrent access.
Waitfor delay '000: 00: 50'

-- Register the selected card.

Update Card
Set F_Name = user,
F_Time = getdate (),
F_Flag = 1
Where F_CardNo = @ CardNo

Commit

Are there any differences? With (updlock), yes, we use the with (UPDLOCK) option during the query. When querying records, we add an update lock to the records, indicates that the next record will be updated. note that the update lock does not conflict with the shared lock, that is, other users can query the content of this table, but it conflicts with the update lock and the exclusive lock. therefore, other update users will be blocked. if we execute this code in another window, we do not add the waifor delay clause. after execution on both sides, we found that two cards were successfully registered. we may have discovered the disadvantage of pessimistic locking: when a user performs an update transaction, other update users must wait in queue even if the user updates a different record.

1 2 Next Page

[Content navigation]
Page 1: SQL Server Optimistic Locking and pessimistic locking	Page 1: SQL Server Optimistic Locking and pessimistic locking