SQL Server Optimistic Locking and pessimistic locking instances

SQL Server Optimistic Locking and pessimistic locking instances

In the actual production environment where multiple users access data concurrently, we often try to maintain data consistency as much as possible. Among them
The most typical example is to read data from the table, check and verify the data, modify the data, and write it back to the database.
. In the process of reading and writing, if in the multi-user concurrent environment, other users have modified the data
Modifications may occur frequently, resulting in data inconsistency. To solve this problem, SQL Server
The concepts of Optimistic Locking and pessimistic locking are proposed. I will use an example below to illustrate how to use Optimistic Locking and pessimistic locking.
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.

Optimistic Locking Solution

-- First, we add the f_timestamp column in the card table. This column is of the varbinary (8) type, but this value will automatically increase during update.

Alter table card add f_timestamp timestamp not null

-- Pessimistic locking
Declare @ cardno varchar (20)
Declare @ timestamp varbinary (8)
Declare @ rowcount int

Begin tran

-- Get the card number and the original timestamp Value
Select top 1 @ cardno = f_cardno,
@ Timestamp = f_timestamp
From card
Where f_flag = 0

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

-- The registration card, but check whether the timestamp has changed. If not, the update is successful. If yes, the update fails.

Update card
Set f_name = user,
F_time = getdate (),
F_flag = 1
Where f_cardno = @ cardno and f_timestamp = @ Timestamp
Set @ rowcount = @ rowcount
If @ rowcount = 1
Begin
Print 'Update successful! '
Commit
End
Else if @ rowcount = 0
Begin
If exists (select 1 from card where f_cardno = @ cardno)
Begin
Print 'this card has been registered by another user! '
Rollback tran
End
Else
Begin
Print 'this card does not exist! '
Rollback tran
End
End

Run the code without waitfor in another window. After successful registration, return to the original window, we will find that the prompt displayed after the time is that this card is registered by another user. obviously, we can avoid two users registering a card at the same time. at the same time, another advantage of using this method is that the update lock is not used, which increases the concurrent processing capability of the system.

I have introduced in detail the use of Optimistic Locking and pessimistic locking. In the actual production environment, if the concurrency is not large, we can use the method of pessimistic locking, this method is very convenient and simple to use. however, if the system concurrency is very large, pessimistic locking will bring about a very large performance problem, so we need to choose an Optimistic Locking method.