关于X锁的问题--由select+X锁是否持有到事务结束的误区

前言：看了宋桑的文章《一次意外的X锁不阻塞问题》，结合本人的测试，说明一下我对select中使用X锁是否会持有到事务结束产生的误区；

详情不多说了，详见宋桑的《一次意外的X锁不阻塞问题》和《消失的共享锁》，对Select+X锁和Select+S锁的情况进行了解释。以下只描述我的测试；测试表结构及数据如下：

/****** Script for SelectTopNRows command from SSMS  ******/
CREATE TABLE [test_a].[dbo].[tmp_byxl_01](id INT IDENTITY,flag int)
INSERT INTO [test_a].[dbo].[tmp_byxl_01](flag) VALUES(null)
go 7
UPDATE [test_a].[dbo].[tmp_byxl_01] SET flag=id 

SELECT TOP 1000 [id]
      ,[flag]
  FROM [test_a].[dbo].[tmp_byxl_01]


-------------------------------
id          flag
----------- ----
1           1
2           2
3           3
4           4
5           5
6           6
7           7

(7 行受影响)

 

由于案例出自系统续费问题，业务采用的是调用存储过程的方式实现，因此每一次调用时，都是select+X锁的方式；这和上述文章中提到的“Select+X锁和Select+S锁”的情况不太相同

先说我的误区

误区：select中指定的X锁将在查询结束后立即释放，并不持续到tran结束

测试代码如下：

--Session_A
BEGIN TRANSELECT * FROM [test_a].[dbo].[tmp_byxl_01](xlock) WHERE flag=2
  WAITFOR DELAY '00:00:10'
COMMIT--Session_B
BEGIN TRANSELECT * FROM [test_a].[dbo].[tmp_byxl_01](xlock) WHERE flag=2
COMMIT

由于两个tran都是申请xlock，在执行时，Session_A(spid=53)先执行，Session_B(spid=55)大约5秒后执行，通过SP_LOCK可以看到，spid=55申请X锁时被阻塞

从执行时间上看，spid(55)晚于spid(53)5秒左右开始，执行时间上基本吻合。

这个测试验证了上述的误区。加在Select上的X锁持续到了tran结尾，因此才能阻塞其他进程的相同查询（也是Xlock）如此长的时间；

 

对于此类情况，一般应用的场景如商家的充值系统、抢购系统等

需要将大并发的环境转化为单一进程持有锁的情况（select是为了进行判断，如账户起始金额不能为负、或查询当前商品信息以防出现超售的情况）

对于此类问题，个人认为，增加Xlock进行查询，是为了有效的避免脏读，尽管增加pagelock的方式可以避免S锁的优化问题，但可能导致锁范围过大。

如果不存在普通S锁的查询，不添加pagelock提升锁级别，也是可以满足大并发需求的。