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 int begin Tran    Select @count=Count  fromTTTWAITFORDELAY'00:00:05' --analog concurrency, intentional delay of 5 seconds    UpdateTttSet Count=@count-1Commit TRAN SELECT *  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:

　　

Declare @count  as int begin Tran    Select @count=Count  fromTb with(UPDLOCK)WAITFORDELAY'00:00:05' --analog concurrency, intentional delay of 5 seconds    UpdateTbSet Count=@count-1Commit 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 to the table timestamp ALTER TABLETttADDTimesflagTIMESTAMP  not NULLthen the update determines whether the value has been modifiedDeclare @count  as intDECLARE @flag  as TIMESTAMPDECLARE @rowCount  as intbegin Tran    Select @count=COUNT,@flag=Timesflag fromTTTWAITFORDELAY'00:00:05'    UpdateTttSet Count=@count-1 WHERETimesflag=@flag --conditions are added here.    SET @rowcount=@ @ROWCOUNT  --gets the number of rows that were modifiedCommit TRAN --the number of rows can be judged IF @rowCount=1    PRINT 'Update Successful'ELSE    PRINT '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