SQL Server processing for concurrency-optimistic and pessimistic locks

Source: Internet
Author: User
Tags comments rowcount ticket

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-1commitTRANSELECTFROMttt

  

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.