在使用SELECT的时候如何加锁？

Question

大家好。

本人对数据库不是很熟，现在做开发遇到这样一个问题需要解决，我需要执行一个事务，语句包括：

1. 用主键查询表的一条记录
2. 更新该记录的状态

我的要求是，事务开始后，其他事务不能读取该条记录，但我们现在事务的隔离级别是READ COMMITTED，在执行第一条语句后，执行第二条语句之前，其他事务仍然能够访问该条记录，我的查询语句是这么写的：

SELECT * FROM Orders WITH(XLOCK) WHERE OrderID=1

系统使用的是ADO.NET，用SqlCommand发送SQL来和数据库交互的，请问该怎么解决这个问题？

谢谢大家！

Answer 1

SELECT * FROM Orders WITH(XLOCK, HOLDLOCK) WHERE OrderID=1

READ COMMITTED级别下，select发出的锁在语句结束后就取消，不会保持到整个事务结束，所以要加holdlock。

但注意orderid要有索引，不然会造成block。

---

想不想时已是想，不如不想都不想。

Answer 2

把WITH(XLOCK) 改成WITH(XLOCK, HOLDLOCK) 就可以了. 后面的条件字段要加索引

Answer 3

我发现SQL SERVER 2008 R2好像很不稳定，

设置SELECT * FROM Orders WITH(XLOCK, HOLDLOCK) WHERE OrderID=1后，在其他的session里查询SELECT * FROM Orders  WHERE OrderID=1，既然有时候能被阻塞，有时候不能被阻塞，而且不能被阻塞的时候发现它执行的时候既然不需要获取KEY LOCK就可以得到结果，。。。。都不知道怎么回事了，。

Answer 4

你begin tran并且没有commit？

---

想不想时已是想，不如不想都不想。

Answer 5

Yes,

session 1

begin Transaction

SELECT * FROM OrdersWITH(XLOCK, HOLDLOCK)WHERE OrderID=1

session 2

SELECT * FROM Orders WHERE OrderID=1

惊奇的发现session 2没有被blocked,立马拿出SQL Profiler跟踪LOCK的获取的情况，惊奇的发现session 2的执行既然不需要KEY S LCOK。

后来试了N次后，惊奇的发现session 2又被BLOCKED了，拿出sys.dm_tran_locks查询，发现在WAIT KEY SLOCK。 就是感觉为什么前面没有被BLOCKED，后面试着试着突然就正常了，这种事情我好像碰到很多次了。

Answer 6

Hi 怡红公子，

我知道为什么上面的XLOCK为什么无法阻塞其他session 获取的该KEY的SLOCK了，因为其他session 读取该数据的时候根本不需要获取那个SLOCK，这是SQL SERVER锁机制的一种优化手段。

这个问题我记得以前讨论过：

http://sqlblog.com/blogs/paul_white/archive/2010/11/01/read-committed-shared-locks-and-rollbacks.aspx

http://social.msdn.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/1746ea76-d309-487a-8ad8-c781910799e3

Answer 7

谢谢，应该是读取数据的时候默认不需要拿到这个锁，如果我在另一个事务里执行

SELECT * FROM Orders WITH(XLOCK, HOLDLOCK) WHERE OrderID=1

而不是

SELECT * FROM Orders WHERE OrderID=1

的话，会被阻塞直到持有这个锁的那个事务完成。

谢谢大家~

Answer 8

我现在遇到和你一样的需求~~~`

但不幸的是...where 后面的字段不是主键...但有索引..

按照 WITH(XLOCK, HOLDLOCK) 的话不起作用了..

请问我这个情况要如何解决...??

谢谢~

Answer 9

Try tablock hint.

Answer 10

关注一下  

---

给我写信： QQ我：