SQL Server 【应用】悲观锁和乐观锁(SQL Server [application] pessimistic lock and optimistic lock)

乐观锁和悲观锁

  • 悲观锁:相信并发是绝大部分的,并且每一个线程都必须要达到目的的。
  • 乐观锁:相信并发是极少数的,假设运气不好遇到了,就放弃并返回信息告诉它再次尝试。因为它是极少数发生的

数据库并发问题

假如两个线程同时修改数据库同一条记录,就会导致后一条记录覆盖前一条,从而引发一些问题,常见的就是卖东西减少库存

例如总共10件商品,卖完就没有了,多个线程同时买入,商品数据减1

CREATE TABLE temp(
Id int identity(1,1) primary key not null,
Amount int not null
)

insert into temp(Amount) values(100)

不加锁的代码

declare @count as int

begin tran
    select @count=Amount from temp
    WAITFOR DELAY '00:00:05' --模拟并发,故意延迟5秒
    update temp set Amount=@count-1 where id = 1
commit TRAN

SELECT * FROM temp

悲观锁

直接加锁

更新加

UPDLOCK

悲观锁一定成功,但在并发量特别大的时候会造成很长堵塞甚至超时,仅适合小并发的情况

declare @count as int

begin tran
    select @count=Amount from temp with(UPDLOCK)
    WAITFOR DELAY '00:00:05' --模拟并发,故意延迟5秒
    update temp set Amount=@count-1 where id = 1
commit TRAN

SELECT * FROM temp

乐观锁

可以解决并发带来的数据错误问题,但不保证每一次调用更新都成功,可能会返回’更新失败’

方案

表中加上时间戳,每次更新前获取时间戳,更新的时候比较时间戳是否被修改,更新需要同时更新时间戳

ALTER TABLE temp ADD updateStamp TIMESTAMP NOT null --首先给表加一列timestamp


declare @count as int
DECLARE @flag AS TIMESTAMP

begin tran
    select @count=Amount,@flag=updateStamp from temp
    WAITFOR DELAY '00:00:05'
    update temp set Amount=@count-1 WHERE id = 1 and updateStamp=@flag --这里加了条件
commit TRAN
IF @rowCount=1
    PRINT '更新成功'
ELSE
    PRINT '更新失败'

————————

Optimistic lock and pessimistic lock

  • Pessimistic lock: I believe that concurrency is the vast majority, and every thread must achieve its goal.
  • Optimistic lock: it is believed that concurrency is very rare. Assuming bad luck, give up and return information to tell it to try again. Because it happens very rarely

Database concurrency problem

If two threads modify the same record in the database at the same time, the latter record will overwrite the previous one, which will cause some problems. The common thing is to sell things and reduce inventory

For example, if a total of 10 items are sold out, there will be no more. If multiple threads buy at the same time, the item data will be reduced by 1

CREATE TABLE temp(
Id int identity(1,1) primary key not null,
Amount int not null
)

insert into temp(Amount) values(100)

Unlocked Code

declare @count as int

begin tran
    select @count=Amount from temp
    WAITFOR DELAY '00:00:05' --模拟并发,故意延迟5秒
    update temp set Amount=@count-1 where id = 1
commit TRAN

SELECT * FROM temp

Pessimistic lock

Direct locking

Update plus

UPDLOCK

Pessimistic locking must be successful, but it will cause long congestion or even timeout when the amount of concurrency is particularly large. It is only suitable for small concurrency

declare @count as int

begin tran
    select @count=Amount from temp with(UPDLOCK)
    WAITFOR DELAY '00:00:05' --模拟并发,故意延迟5秒
    update temp set Amount=@count-1 where id = 1
commit TRAN

SELECT * FROM temp

Optimistic lock

It can solve the problem of data errors caused by concurrency, but it does not guarantee the success of every call to update, and it may return ‘update failure’

programme

Add a time stamp to the table. Obtain the time stamp before each update. Compare whether the time stamp has been modified when updating. The time stamp needs to be updated at the same time

ALTER TABLE temp ADD updateStamp TIMESTAMP NOT null --首先给表加一列timestamp


declare @count as int
DECLARE @flag AS TIMESTAMP

begin tran
    select @count=Amount,@flag=updateStamp from temp
    WAITFOR DELAY '00:00:05'
    update temp set Amount=@count-1 WHERE id = 1 and updateStamp=@flag --这里加了条件
commit TRAN
IF @rowCount=1
    PRINT '更新成功'
ELSE
    PRINT '更新失败'