SQL Server processing for concurrency-optimistic and pessimistic locks

If two threads modify the same record in the database at the same time, it causes the latter record to overwrite the previous one, causing some problems.

For example:

A ticketing system has a number of votes, the client every call to the ticket method, the number of votes minus one.

Scene:

A total of 300 tickets, assuming that two tickets, exactly at the same time ticket, they do the operation is first check the number of votes, and then minus one.

General SQL statements:

　　

123456789

declare@countasintbegintran    select@count=countfromttt    WAITFOR DELAY ‘00:00:05‘--模拟并发，故意延迟5秒    updatettt set count[email protected]count-1commitTRANSELECT* FROMttt

　　

The problem is that the same time to obtain the remaining tickets are 300, each ticket has done an update to 299 of the operation, resulting in less than 1 of the votes, and actually out of two tickets.

Open two query windows and quickly run the above code to see the effect.

Definition Explanation:

Pessimistic lock: Believe that concurrency is the overwhelming majority, and each thread must achieve the purpose.

Optimistic Lock: Believe that concurrency is very rare, assuming bad luck encountered, give up and return information to tell it to try again. Because it is very rare to happen.

Pessimistic lock Solution:

　　

1234567

declare@countasintbegintran    select@count=countfromtb WITH(UPDLOCK)　　 WAITFOR DELAY ‘00:00:05‘--模拟并发，故意延迟5秒    updatetb setcount[email protected]count-1committran

　　

An update lock is added to the query to ensure that no dirty data is generated until the transaction ends without being read by other transactions.

To solve the above problems.

Optimistic locking solution:

123456789101112131415161718192021

--首先给表加一列timestampALTERTABLEttt ADDtimesFlag TIMESTAMPNOTnull然后更新时判断这个值是否被修改declare@countasintDECLARE@flag ASTIMESTAMPDECLARE@rowCount ASintbegintran    select@count=COUNT,@flag=timesflag fromttt    WAITFOR DELAY ‘00:00:05‘    updatettt setcount[email protected]count-1 WHERE[email protected] --这里加了条件    SET@[email protected]@ROWCOUNT  --获取被修改的行数commitTRAN--对行数进行判断即可IF @rowCount=1    PRINT ‘更新成功‘ELSE    PRINT ‘更新失败‘

This is the optimistic locking solution that solves the data error problem caused by concurrency, but does not guarantee that every call to update will succeed and may return ' update failed '

Pessimistic lock and optimistic lock

Pessimistic locks must be successful, but when the concurrency is particularly large, it can cause a long blockage or even timeout, only suitable for small concurrency situations.

Optimistic locks do not always succeed every time, but can take full advantage of the system's concurrency processing mechanism, in large concurrency when the efficiency is much higher.

SQL Server processing for concurrency-optimistic and pessimistic locks