Sql Server 乐观锁和悲观锁理解和应用

理解

事先准备下表

　　CREATE TABLE [dbo].[HAX](    --测试用表
    　　[AD_ID] [nvarchar](2) NULL,
    　　[AD_DATE] [datetime] NULL,
    　　[AD_MSG] [nvarchar](max) NULL,
    　　[AD_INFO] [nvarchar](max) NULL,
    　　[AD_NUM] [nvarchar](10) NULL,
    　　[AD_COUNT] [decimal](18, 0) NULL,
    　　[AD_TIMESAMP] [timestamp] NULL
　　)

假如两个线程同时修改数据库同一条记录，就会导致后一条记录覆盖前一条，从而引发一些问题。
例如：
　　一个售票系统有一个余票数，客户端每调用一次出票方法，余票数就减一。
情景：
　　总共300张票，假设两个售票点，恰好在同一时间出票，它们做的操作都是先查询余票数，然后减一。
一般的sql语句：

　　SELECT * FROM HAX

　　DECLARE @count AS INT
　　BEGIN TRAN
  　　  SELECT @count = AD_COUNT FROM HAX
   　　 WAITFOR DELAY '00:00:05'  --模拟并发，故意延迟5秒
    　　UPDATE HAX SET AD_COUNT = @count-1 WHERE AD_ID = 4
　　COMMIT TRAN

　　SELECT * FROM HAX

问题就在于，同一时间获取的余票都为300，每个售票点都做了一次更新为299的操作，导致余票少了1，而实际出了两张票。
打开两个查询窗口，分别快速运行以上代码即可看到效果。

定义解释：

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

悲观锁解决方案：

sql server在执行查询语句时会锁表。在锁表期间禁止增删改操作。
如果不想锁表，那就再表名或别名后面加上WITH(NOLOCK)
如果想锁表加上(UPLOCK)

 　 SELECT * FROM HAX

　　DECLARE @count AS INT
　　BEGIN TRAN
   　　 SELECT @count = AD_COUNT FROM HAX WITH(UPDLOCK)  --此处加锁
 　　  WAITFOR DELAY '00:00:05'  --模拟并发，故意延迟5秒
　　    UPDATE HAX SET AD_COUNT = @count-1 WHERE AD_ID = 4
　　COMMIT TRAN
　　SELECT * FROM HAX

 

在查询的时候加了一个更新锁，保证自查询起直到事务结束不会被其他事务读取修改，避免产生脏数据。
从而可以解决上述问题。
乐观锁解决方案：
(提示：每次修改或者插入包含timestamp列的行时，就会在timestamp列中插入增量数据库时间戳值。
timestamp列不适合于作为键使用，因为任何更新都会更改timestamp的值。)

　　ALTER TABLE HAX ADD AD_TIMESAMP TIMESTAMP NOT NULL   --事先在表里添加一个列，用于测试，可以随时被删除

　　DECLARE @count AS INT
　　DECLARE @flag AS TIMESTAMP
　　DECLARE @rowCount AS INT     --记录更新行数
　　BEGIN TRAN
   　　 SELECT @count = AD_COUNT , @flag = AD_TIMESAMP FROM HAX    --利用timestamp机制
  　　  WAITFOR DELAY '00:00:05'
   　　 UPDATE HAX SET AD_COUNT = @count-1 WHERE AD_TIMESAMP = @flag AND AD_ID = 4   --如果表被别人先更新，此处时间戳不吻合，更新失败
 　　   SET @rowcount=@@ROWCOUNT
　　COMMIT TRAN

　　IF @rowCount=1
   　　 PRINT '更新成功'
　　ELSE
   　　 PRINT '更新失败'

　　SELECT * FROM HAX

这便是乐观锁的解决方案，可以解决并发带来的数据错误问题，但不保证每一次调用更新都成功，可能会返回'更新失败'

悲观锁和乐观锁
　　悲观锁一定成功，但在并发量特别大的时候会造成很长堵塞甚至超时，仅适合小并发的情况。
　　乐观锁不一定每次都修改成功，但能充分利用系统的并发处理机制，在大并发量的时候效率要高很多。

应用

乐观锁在实际生产中用的很多，结合一个实际例子说明
数据库中创建一个lock表用来进行乐观锁锁表，保存锁表信息
事先准备下表：
　　USE [XXXXX]
　　GO
　　SET ANSI_NULLS ON
　　GO
　　SET QUOTED_IDENTIFIER ONXXXX
　　GO
　　CREATE TABLE [dbo].[LOCK](
    　　[SLIP_TYPE_CODE] [nvarchar](3) NOT NULL,
    　　[SLIP_NO] [nvarchar](12) NOT NULL,
   　　 [SESSION_ID] [nvarchar](24) NULL,
    　　[INSERT_DATETIME] [datetime] NULL
   　　 CONSTRAINT [PK_LOCK] PRIMARY KEY CLUSTERED
　　(
    　　[SLIP_TYPE_CODE] ASC,
    　　[SLIP_NO] ASC
　　)WITH (PAD_INDEX = OFF) ON [PRIMARY]
　　) ON [PRIMARY]

1.并且假设一个要执行的更新语句SQL为A，并且此查询语句主键刚好和LOCK主键保持一致，一下统称SQL-A
在执行SQL-A之前先查询这个相应的锁是否存在（用一致逐渐查询）
　　SELECT [SESSION_ID],[EMP_SHORT_NAME],[DEPT_SHORT_NAME],[INSERT_DATETIME]
　　FROM [XXXXX].[dbo].[LOCK] WHERE [SLIP_TYPE_CODE]=N'CW1' AND [SLIP_NO]=N'A000000002'
如果存在则报错

2.如果不存在的情况下，准备执行SQL-A，事先插入一条语句，获得锁。
　　INSERT INTO [XXXXX].[dbo].[LOCK]([SLIP_TYPE_CODE],[SLIP_NO],[SESSION_ID],[INSERT_DATETIME])
　　VALUES(N'CW1',N'A000000002',@SessionId,GETDATE())
如果数据不能插入，则报错，主键冲突所致

3.执行SQL-A  ... ...

4.SQL-A执行完成之后再次删除以上插入的数据
　　DELETE FROM [XXXXX].[dbo].[LOCK] WHERE [SLIP_TYPE_CODE]=N'CW1' AND [SLIP_NO]=N'A000000002'
如果数据不能删除，则报错，主键冲突所致