当查询条件为非主键字段时，为什么在SQLServer的更新锁(UPDLOCK)会锁表

Question

大家能不能帮我看看，当查询条件为非主键字段时，为什么在SQLServer的更新锁(UPDLOCK)会整表锁定？===========================================================
当我采用主键au_id列作为查询条件时，可以正常锁定查询的行：
begin tran
select * from authors with(updlock) where au_id=1
update authors set au_lname='aaa' where au_id=1
waitfor delay '00:00:30'
commit tran
上面语句中，我延时了30秒，在这30秒内：
1、另外打开第一个查询窗口，编写如下代码：（结果：正常已锁定，不能更新）
update authors set au_lname='aaa' where au_id=1
2、另外打开第二个查询窗口，编写如下代码：（结果：正常未锁定，可以更新）：
update authors set au_lname='aaa' where au_id=1=============================================================
当我采用非主键au_lname列作为查询条件时，则不能正常锁定查询的行：
begin tran
select * from authors with(updlock) where au_lname='White'
update authors set au_lname='bbb'  where au_lname='White'
waitfor delay '00:00:30'
commit tran
上面语句中，我一样延时了30秒，在这30秒内：
1、另外打开第一个查询窗口，编写如下代码：（结果：正常已锁定，不能更新）
update authors set au_lname='bbb' where au_lname='White'
2、另外打开第二个查询窗口，编写如下代码：（结果：不正常已锁定，不能更新）：
update authors set au_lname='bbb' where au_lname='Green'

Answer 1

单实例情况下，也就是不包含集群，默认是5000行数据需要update、delete的时候，就会造成锁升级，成为页锁甚至表锁。另外你那个没显式表锁啊。

Answer 2

2、另外打开第二个查询窗口，编写如下代码：（结果：正常未锁定，可以更新）：
update authors set au_lname='aaa' where au_id=1
例子应该是au_id=2之类的吧
-----------
准确的说不是主键的问题，而是聚集索引的缘故
数据的物理顺序是由聚集索引决定的，而且数据量少的话会精确锁定行
（一般数据量大的话都会引起锁升级）
而非聚集索引列应该是存储的聚集索引键值与对应列的数据，
所以非聚集索引锁定行的时候范围可能会大

Answer 3

谢谢你纠正了我发的错误!
我再次做了测试，就是你说的这种情况，那我要怎么样才能使用任意条件来updlock锁定表中查询行的数据呢？

Answer 4

用with(rowlock)试一下，这个主要由于非聚集索引所在的数据页范围大，可能锁住多个页
一般要精确锁定的话最好还是通过聚集索引来选定行

Answer 5

szm341太谢谢您了，rowlock，我测试过了，不行，有没有其他办法？

Answer 6

现在我的需求，就是必须得有多个字段同时查询，所以不能仅通过聚集索引来选定行--------------------------------------------------------------------------
我的最初始需求是这样的（与12306订票网站有点类似）：
页面上有一个按钮，点击按钮时，会去减少数据库中剩余票的张数，当减少到0时，就会提示用户“剩余票数已经为0”，可是现在问题是，出现了多用户同时点击并发的情况，所以导致数据库中剩余票张数出现了负数，所以我想通过数据库中upblock锁定行，再事务操作的方式，来解决这个问题，不知大家有没有其他的解决办法，帮帮小弟，谢谢了！

Answer 7

在au_lname上建个索引就可以更新了。
原因嘛，我还不能准确的解释。