SQL Server optimistic lock and pessimistic lock instance

This article uses an example to illustrate how optimistic locking and pessimistic locking can be used to solve a multiuser concurrency situation where other users have modified the data you want to modify to create inconsistencies in the data.

In the actual multi-user concurrent access 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 Test table: Card, representing a real Cacu for users to register. The user wants to choose an unused card from inside, namely F_flag=0 Card, register to the User: Update 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(20),F_Name varchar(20),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: