SQL Server Optimistic Locking and pessimistic locking instances

[ReferenceLao ChenBlog]

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 '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 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 WhereF_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 WhereF_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 is about to 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 the 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

-- Add the f_timestamp column to the card table, which is of the varbinary (8) type. However, this value will automatically increase during the update process.

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 WhereF_cardno = @ cardnoAndF_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 whereF_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.

If you find any errors in the article, please remind me in time and welcome to study and discuss them together.