最近有一个朋友问我一个关于给查询操作强制上X锁却不阻塞的问题。该查询写在一个存储过程中，代码如代码1所示：
1: create PROC [dbo].[GetCityOrders]
2: @city NVARCHAR(10) , 3: @num INT
4: AS
5: SET NOCOUNT ON
6: 7: BEGIN TRY
8: 9: BEGIN TRAN
10: 11: SELECT TOP ( @num )
12: id , 13: number , 14: price , 15: mid , 16: @city city 17: INTO #cityorders
18: FROM cmcc WITH ( XLOCK )
19: WHERE prov = 0
20: AND status = 0
21: AND city = @city
22: 23: UPDATE cmcc
24: SET status = 100
25: WHERE id IN ( SELECT id
26: FROM #cityorders )
27: 28: SELECT o.* ,
29: c.attach 30: FROM #cityorders o
31: LEFT JOIN cmcc_attach c ON o.id = c.id
32: 33: DROP TABLE #cityorders
34: 35: COMMIT TRAN
36: 37: END TRY
38: BEGIN CATCH
39: 40: ROLLBACK
41: 42: END CATCH
代码1.
该存储过程首先通过对查询操作加X锁，使得其他读取操作更新时不影响该部分加X锁的操作。乍一看没有任何问题，但是当业务上线后就发现，即使查询有了X锁，但实际上还是会有多个调用该存储过程的客户端同时读取到同一条数据的现象现象。
原因？
为了验证原因，我们来做一个Demo测试，首先我们创建测试表，代码如代码2所示。
1: CREATE TABLE dbo.DemoX
2: ( 3: [key] INT PRIMARY KEY ,
4: [value] INT,
5: ); 6: GO
7: INSERT INTO dbo.DemoX
8: ( [key], value )
9: VALUES ( 1, 100 );
10: GO
代码2.创建测试DEMO
接下来，对该DemoX表进行Select操作，并查看锁。如代码3所示。
1: BEGIN TRAN
2: SELECT [key],value
3: FROM dbo.DemoX D WITH (XLOCK);
4: 5: SELECT L.resource_type,
6: L.request_mode, 7: L.request_status, 8: L.resource_description, 9: L.resource_associated_entity_id 10: FROM sys.dm_tran_current_transaction T
11: JOIN sys.dm_tran_locks L
12: ON L.request_owner_id = T.transaction_id;
代码3.使用X锁提示查语句
在代码3中显式指定了X锁，并查看上锁情况，可以看出X锁以及对应父对象上的意向锁都正常存在，如图1所示。
图1.
我们再开另外一个窗口运行一个普通的Select，结果如图2所示。
图2.
为什么没有阻塞
理论上来说，第二个查询应该会被阻塞，因为第二个查询所需加的S锁和第一个查询的X锁不兼容。后来在网上找打StackOverFlow的一篇博文：“http://stackoverflow.com/questions/4609217/sql-server-the-misleading-xlock-optimizations”，找到了答案。
在SQL Server中，默认的已提交读为了保证不读脏数据（既在内存中修改，还未落盘的数据），会对需要查找的数据上S锁，但如果发现数据并不是脏数据，则会优化跳过加S锁的步骤，代码3中的查询语句强制使用了X锁提示，但未进行任何数据修改，所以不存在脏数据，因此后续查询就通过优化放弃使用S锁，从而不阻塞，导致了意料之外的结果。
解决办法
SQL Server对于该特性的优化仅仅对行锁生效，如果在指定查询时使用页锁提示，则会按照语句，对阻塞后续查询，代码如代码4所示。
1: SELECT [key],value
2: FROM dbo.DemoX D WITH (PAGLOCK,XLOCK);
代码4.
但显而易见，该方法会降低并发，如果有可能，请不要对Select操作使用X锁提示，否则请加上页锁提示。
另一个办法是使用CTE进行表更新，将代码1中的代码两部分合二为一，数据在更新时会导致脏数据，因此不会出现跳过S锁的情况。
- 6楼桦仔
- 应该用merge语句或CTE
- Re: CareySon
- @桦仔，yes
- 5楼我是大菠萝
- @宋桑 由于这个业务每次都是调用存储过程实现功能，即便是大并发环境，相邻进程的事务都是先申请xlock，也不会出现文中描述的S锁不被X锁阻塞的情况啊
- Re: wy123
- @我是大菠萝，他这个读是没有以排他（xlock）的方式去读，，仅仅普通的select，刚好绕过那个规则了（读取数据时因为某些条件，跳过了加S锁的步骤），，，您写的那篇读的时候是以xlock的方式去读的，当另外一个会话以xlock的方式去读，事物未提交之前，再以xlock的方式去读，就被阻塞了，，如果（以多线程）去执行select+xlock的存储过程，不同会话之间的读，当然会被阻塞，他这里表达的是select+xlock不会阻塞select，，您的测试证明select+xlock会阻塞select+xlock，说明排他所保持到事物结束，也没有问题
- Re: CareySon
- @我是大菠萝，如@wy123所述，是因为优化器跳过加S锁的步骤。我个人认为这应该属于BUG
- 4楼Antineutrino
- 什么情况下需要在没有脏数据的时候还要加锁呢？
- Re: Shao明
- @Antineutrino，其实一些特殊的行业都是那样的，数据只能展现在一个客服端
- 3楼wy123
- 另外这种情况是不是也是因为楼主后面说的规则的原因呢，（读的时候发现非脏读，就不加S锁），，drop table tUpdatecreate table tUpdate(id int primary key,name varchar(100))insert into tUpdate values (1,#39;AAA#39;)begin tran--更新值与原值相同update tUpdate set name=#39;AAA#39; where id=1;--先不提交--再开一个会话执行select * from tUpdate where id=1;--正常查询到结果
- Re: CareySon
- @wy123，是的，其实可以用Trace或扩展事件跟踪下就一目了然了。
- 2楼ShanksGao
- 有内容,赞下
- Re: CareySon
- @ShanksGao，多谢高兄
- 1楼桦仔英语学习
- By design 的，https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b1138478-730a-45f9-b4b5-6736dca3c747/pessimistic-locking?forum=transactsql，，Using XLOCK in SELECT statements will not prevent reads from happening. This is because SQL Server has a special optimization under read committed isolation level that checks if the row is dirty or not and ignores the xlock if the row has not changed. Since this is acceptable under the read committed isolation level semantics it is by design. So you will have to use a more aggressive locking hint like UPDLOCK with ROWLOCK.
- Re: CareySon
- @桦仔英语学习，但在这个particular场景下算是BUG。。，PS:你的名字什么情况