Recommended: SQL optimistic lock and pessimistic lock usages

SQL in the actual multi-user concurrent access to the production environment, we often have to keep the data consistent as much as possible. And the most typical example is that we read the data from the table, check the validation, modify the data, and then write back to the database. In the process of reading and writing, if in the context of multiuser concurrency, other users have changed the data you want to modify is very likely to occur, which results in inconsistencies in data. To solve the problem, SQL Server puts forward the concept of optimistic locking and pessimistic locking, and below I use an example to illustrate how optimistic locking and pessimistic locking can be used to solve such problems.

* Set up a test table: card, representing a real Cacu, for users to register. The user wants to select an unused card from inside, that is, the f_flag=0 cards, register with the user: Update the F_name,f_time,f_flag field. If two users update a card at the same time, it is intolerable, which is what we call inconsistent data lines. */

CREATE TABLE card (f_cardno varchar, f_name varchar, f_flag bit,f_time datetime)
Go
Insert Card (f_cardno,f_flag) Select ' 1111-1111 ', 0
Insert Card (f_cardno,f_flag) Select ' 1111-1112 ', 0
Insert Card (f_cardno,f_flag) Select ' 1111-1113 ', 0
Insert Card (f_cardno,f_flag) Select ' 1111-1114 ', 0
Insert Card (f_cardno,f_flag) Select ' 1111-1115 ', 0
Insert Card (f_cardno,f_flag) Select ' 1111-1116 ', 0
Insert Card (f_cardno,f_flag) Select ' 1111-1117 ', 0
Insert Card (f_cardno,f_flag) Select ' 1111-1118 ', 0
Insert Card (f_cardno,f_flag) Select ' 1111-1119 ', 0
Insert Card (f_cardno,f_flag) Select ' 1111-1110 ', 0
Go

--Below are the updates that we often use as follows:


DECLARE @CardNo varchar (20)
Begin Tran

--Select a card that is not used
Select top 1 @CardNo =f_cardno
From the card where f_flag=0

--delay 50 seconds to simulate concurrent access.
WAITFOR DELAY ' 000:00:50 '

--Register the card you just selected.

Update Card
Set F_name=user,
F_time=getdate (),
F_flag=1
where f_cardno= @CardNo

Commit

Question: If we execute the same piece of code in the same window, but remove the WAITFOR DELAY clause. After the two sides were executed, we found that although two registrations were registered, only one card was registered, that is, two people registered the same card.

Pessimistic lock solution

-we can achieve pessimistic locking by making minor changes to the code above.

DECLARE @CardNo varchar (20)
Begin Tran

--Select a card that is not used
Select top 1 @CardNo =f_cardno
From the card with (UPDLOCK) where f_flag=0

--delay 50 seconds to simulate concurrent access.
WAITFOR DELAY ' 000:00:50 '

--Register the card you just selected.

Update Card
Set F_name=user,
F_time=getdate (),
F_flag=1
where f_cardno= @CardNo

Commit

Notice the difference? With (Updlock), yes, we used the WITH (updlock) option in the query, we added an update lock to the record when we queried the record, indicating that we were about to update the record. Note that updating and sharing locks is not a conflict. That is, other users can also query the contents of this table, but the update lock and exclusive lock are conflicting. So the other updates are blocked. If we execute this code in another window, we do not add the waifor delay clause. After execution on both sides, We found a successful registration of two cards. Maybe we've found the downside of pessimistic locking: When a user makes an updated transaction, other update users must wait in line, even if that user is not updating the same record.

Optimistic locking solution

--First we add a list of F_timestamp columns to the card table, which is the varbinary (8) type. But this value will grow automatically at the time of the update.

ALTER TABLE card add F_timestamp TimeStamp NOT NULL


--Pessimistic lock
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 to compare whether the timestamp has changed. If no changes have occurred. Update succeeded. If a change occurs, 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 ' does not exist for this card! '
Rollback Tran
End
End

Executing code without WAITFOR in another window, after the registration is successful, return to the original window, we will find that after the time it shows the prompt is this card to be registered by another user. Obviously, we can also avoid the appearance of two users registering a single card at the same time. Another advantage of using this method is that the update lock is not used, which increases the system's concurrent processing power.

I've covered the use of optimistic locking and pessimistic locking in detail, in the actual production environment, if the amount of concurrency is small, we can use pessimistic locking method, because this method is very convenient and easy to use. But if the system's concurrency is very large, pessimistic locking can cause very large performance problems, So we need to choose the optimistic locking method.

If you find anything wrong in the article, please remind me in time, but also welcome the interest of a study and discussion.