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:

　　

Declare @count as Intbegin tran    select @count =count from TTT    WAITFOR delay ' 00:00:05 '--analog concurrency, intentional delay 5 sec.    Update TT t set [email protected]commit transelect * from TTT

　　

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:

　　

Declare @count as Intbegin tran    select @count =count from TB with (UPDLOCK) WAITFOR delay ' 00:00:05 '--analog concurrency, deliberate delay of 5 seconds 
   
    update TB Set [email Protected]commit Tran
   

　　

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:

--First add a column Timestampalter table TTT ADD Timesflag TIMESTAMP NOT null and then update to determine whether this value was modified declare @count as intDECLARE @flag as TIM Estampdeclare @rowCount as Intbegin tran    select @count =count, @flag =timesflag from TTT    WAITFOR DELAY ' 00:00:05 ' C2/>update TTT Set [email protected] where [email protected]--added conditional    set @[email protected] @ROWCOUNT  -- Gets the number of rows modified commit tran--to determine the number of rows if @rowCount =1print ' update succeeded ' elseprint ' update failed '

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