秒杀我们也来hold住--优化SQL SERVER锁的使用

本文针对秒杀系统的存储过程进行了优化，解决了原有代码中存在的问题，包括过多的ifelse判断影响性能、事务粒度过大等问题，并详细解释了不同锁选项的使用场景。

秒杀已经很不陌生了，秒杀对于我们程序员来说更多的是并发带来的思考，也许有天才考虑的是如何做秒杀器来横扫“秒杀江湖”。前日应邀来优化秒杀的sql。

　　让我们来看看这秒杀的存储过程。(部分代码如下)

create PROCEDURE [ dbo ]. [ kill ]
     @userid nvarchar( 64),
     @killId int
AS
BEGIN
     BEGIN TRAN
         declare @storage int
         -- 判断库存
         select @storage =storage from killProduct with(tablockx) where killID = @killId
         IF( @@rowcount = 0)
         BEGIN
             COMMIT TRAN
             select ' 没货 '
             return
         END
         IF( @storage <= 0)
         BEGIN
             COMMIT TRAN
             select ' 没货 '
             return
         END

         -- 继续判定秒杀是否结束
        ....
         -- 判断userId是否已经参加过秒杀
        ....

初看有如下几个问题：

　　1.if else 太多，影响性能，由于sql server本身不善于运算，一个if 分支的性能损耗比clr中的if高出许多，所以建议多多在clr中进行此等判定，同时建议类似分页等，第一页单独写sql，不要走这一的if（pageIndex=1）的分支。

　　2.TRAN 这东西上得太早了。干嘛这么猴急呢，也不来点前戏就直接上，太不懂情调了。怎么说呢，事物的粒度一定要把握好，不要把太多资源锁在事物里，事物粒度太大，势必影响到性能。

　　3.with(tablockx) 这个固然是有必要，因为秒杀自有的特点就是并发大，是个男人都知道，一定要挺住那么几秒钟。问题就是你要lock整个表，还要独享，那你必须要打败其他人了，所以就意味着其他人都是牺牲品了（排它锁，锁住整个表，其他人连查询其他行的数据都得排队）。

　　4.没事找事如上两个if 您不觉得第一个没有存在的必要吗？

　　好了，我也不得瑟了，下面贴一段如何hold住的代码吧。（修改后的部分代码）

-- 直接进行更新，通过子查询确认用户是否有参加过秒杀
         -- 通过hold来确保此过程结束前没有其他用户更新此行，可读，rowlock是共享锁
         UPDATE  killProduct   WITH(ROWLOCK, HOLDLOCK) SET storage =storage - 1, @storage =storage
          WHERE killID = @killId

AND storage > 0 AND  ( SELECT COUNT( 0) FROM KILLlIST(NOLOCK) WHERE USERID = @USERID) = 0

          IF( @storage > 0)
          BEGIN
             -- 秒杀成功
          END
          ELSE
          BEGIN
             -- 失败
          END

holdlock，hold住此行数据（rowlock），直到整个事务结束。

此处可以将holdlock换成UPDLOCK来实现同样的效果。

秒杀你hold住了吗？您在处理这样的并发时又是如何hold住的呢？请大家分享之！

锁定数据库的一个表

SELECT * FROM table WITH (HOLDLOCK)

注意: 锁定数据库的一个表的区别

SELECT * FROM table WITH (HOLDLOCK)

其他事务可以读取表，但不能更新删除

SELECT * FROM table WITH (TABLOCKX)

其他事务不能读取表,更新和删除

SELECT 语句中“加锁选项”的功能说明

SQL Server提供了强大而完备的锁机制来帮助实现数据库系统的并发性和高性能。用户既能使用SQL Server的缺省设置也可以在select 语句中使用“加锁选项”来实现预期的效果。本文介绍了SELECT语句中的各项“加锁选项”以及相应的功能说明。

功能说明：　

NOLOCK（不加锁）

此选项被选中时，SQL Server 在读取或修改数据时不加任何锁。在这种情况下，用户有可能读取到未完成事务（Uncommited Transaction）或回滚(Roll Back)中的数据, 即所谓的“脏数据”。

HOLDLOCK（保持锁）

此选项被选中时，SQL Server 会将此共享锁保持至整个事务结束，而不会在途中释放。

UPDLOCK（修改锁）

此选项被选中时，SQL Server 在读取数据时使用修改锁来代替共享锁，并将此锁保持至整个事务或命令结束。使用此选项能够保证多个进程能同时读取数据但只有该进程能修改数据。

TABLOCK（表锁）

此选项被选中时，SQL Server 将在整个表上置共享锁直至该命令结束。这个选项保证其他进程只能读取而不能修改数据。

PAGLOCK（页锁）

此选项为默认选项，当被选中时，SQL Server 使用共享页锁。

TABLOCKX（排它表锁）

此选项被选中时，SQL Server 将在整个表上置排它锁直至该命令或事务结束。这将防止其他进程读取或修改表中的数据。

HOLDLOCK 持有共享锁，直到整个事务完成，应该在被锁对象不需要时立即释放，等于SERIALIZABLE事务隔离级别

　　NOLOCK 语句执行时不发出共享锁，允许脏读，等于 READ UNCOMMITTED事务隔离级别

　　PAGLOCK 在使用一个表锁的地方用多个页锁

　　READPAST 让sql server跳过任何锁定行，执行事务，适用于READ UNCOMMITTED事务隔离级别只跳过RID锁，不跳过页，区域和表锁

　　ROWLOCK 强制使用行锁

　　TABLOCKX 强制使用独占表级锁，这个锁在事务期间阻止任何其他事务使用这个表

　　UPLOCK 强制在读表时使用更新而不用共享锁

注意: 锁定数据库的一个表的区别

　　SELECT * FROM table WITH (HOLDLOCK) 其他事务可以读取表，但不能更新删除

　　SELECT * FROM table WITH (TABLOCKX) 其他事务不能读取表,更新和删

相关的存储过程动态视图

sp_lock 说明见 http://msdn.microsoft.com/zh-cn/library/ms187749.aspx

spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
52 7 0 0 DB S GRANT
55 7 0 0 DB S GRANT
57 7 0 0 DB S GRANT
57 1 1131151075 0 TAB IS GRANT

spid:锁会话id，dbid：数据库id，objid：持有锁的对象id，type：锁类型行锁页锁表锁等，resource：锁定资源如文件号页号行号，锁模式：共享排他等锁状态：获得等待冲突

sys.dm_tran_locks 也可查看msdn

sp_who2 会话相关的信息

dbcc inputbuffer(pid) 查看会话的语句参数为会话id

二，死锁实例

建立环境：
  ----死锁例子,建立表数据
  create table [dbo].[zping.com1]( A varchar(2) ,B varchar(2) ,C varchar(2))
  --插入数据
  insert into [dbo].[zping.com1]
  select 'a1','b1','c1'
  union all select 'a2','b2','c2'
  union all select 'a3','b3','c3'
  --建立表数据
  create table [dbo].[zping.com2](D varchar(2) ,E varchar(2))
   --插入数据
  insert into [dbo].[zping.com2]
  select 'd1','e1'
  union all select 'd2','e2'

[sql]view plaincopy 
   
 begin tran --会话一  
   update [dbo].[zping.com2] set D='d5' where E='e1' --更新未提交，锁住该行  
   waitfor delay '00:00:05'   
   update [dbo].[zping.com1] set A='aa' where B='b2' --请求更新行，但被会话二锁住（排他），等待  
      
      
 begin tran --会话二  
   update [dbo].[zping.com1] set A='aa' where B='b2' --更新未提交，锁住该行  
   waitfor delay '00:00:05'    
   update [dbo].[zping.com2] set D='d5' where E='e1' --请求更新行，但被会话一锁住（排他），等待  