Update语句的锁定模式是键锁还是表锁？

[wys1978]

从联机文档上看到如果对于一个表作Update的话，Sql server会自动选择一个最优锁定策略，但是在实际应用当中，系统出现的一些问题感觉有点疑惑，于是做了个实验：

环境：Win2K Server + SQL Server SE SP3

数据库使用SQL Server安装时候带的example数据库：Northwind

进程A：
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
BEGIN TRANSACTION
update customers set CompanyName = 'compTest1' where customerID = 'ALFKI'
waitfor delay '00:00:10' --等待10秒模拟实际系统中系统繁忙时提交事务时间长
update employees set LastName = 'empTest1' where employeeID = '1'
COMMIT TRANSACTION

进程B
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
BEGIN TRANSACTION
update employees set LastName = 'empTest1' where employeeID = '1'
waitfor delay '00:00:10'
update customers set CompanyName = 'compTest1' where customerID = 'ALFKI'
COMMIT TRANSACTION

如果按照联机文档中所说话，Sql Server会自动选择键锁，这样就不会出现死锁的情况。但是实际同时运行A，B进程，却出现了死锁，牺牲了一个进程。

我对于Sql Server的事务/锁定机制不是很了解，不知道是否我的理解、测试有误，请大家解释一下这个现象。谢谢！

[CSDNM]

你的测试有问题

你修改的都是同一条纪录，什么锁都会死锁

你把例子改成

进程A：
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
BEGIN TRANSACTION
update customers set CompanyName = 'compTest1' where customerID = 'ALFKI'
waitfor delay '00:00:10' --等待10秒模拟实际系统中系统繁忙时提交事务时间长
update employees set LastName = 'empTest1' where employeeID = '1'
COMMIT TRANSACTION

进程B
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
BEGIN TRANSACTION
update employees set LastName = 'empTest1' where employeeID = '2'
waitfor delay '00:00:10'
update customers set CompanyName = 'compTest1' where customerID = 'ANATR'
COMMIT TRANSACTION

试试

[yone]

对!楼主的UPDATE是必锁无疑啊.
你可以用sp_lock查一下对employees 表加的是什么锁

[txlicenhe]

/********** 加锁 ***************
设table1(A,B,C)
A B C
a1 b1 c1
a2 b2 c2
a3 b3 c3

1）排它锁
新建两个连接
在第一个连接中执行以下语句
begin tran
update table1
set A='aa'
where B='b2'
waitfor delay '00:00:30' --等待30秒
commit tran
在第二个连接中执行以下语句
begin tran
select * from table1
where B='b2'
commit tran

若同时执行上述两个语句，则select查询必须等待update执行完毕才能执行即要等待30秒

2）共享锁
在第一个连接中执行以下语句
begin tran
select * from table1 holdlock -holdlock人为加锁
where B='b2'
waitfor delay '00:00:30' --等待30秒
commit tran

在第二个连接中执行以下语句
begin tran
select A,C from table1
where B='b2'
update table1
set A='aa'
where B='b2'
commit tran

若同时执行上述两个语句，则第二个连接中的select查询可以执行
而update必须等待第一个连接中的共享锁结束后才能执行 即要等待30秒

3）死锁
增设table2(D,E)
D E
d1 e1
d2 e2
在第一个连接中执行以下语句
begin tran
update table1
set A='aa'
where B='b2'
waitfor delay '00:00:30'
update table2
set D='d5'
where E='e1'
commit tran

在第二个连接中执行以下语句
begin tran
update table2
set D='d5'
where E='e1'
waitfor delay '00:00:10'
update table1
set A='aa'
where B='b2'
commit tran

同时执行，系统会检测出死锁，并中止进程


--------------------------------------------------------------
SET IMPLICIT_TRANSACTIONS ON --用户每次必须显式提交或回滚。否则当用户断开连接时,
--事务及其所包含的所有数据更改将回滚

SET IMPLICIT_TRANSACTIONS OFF --自动提交模式。在自动提交模式下，如果各个语句成功
--完成则提交。

[wys1978]

谢谢大家的解释，我明白我的理解错在哪里了，原来这个测试是键锁的，可惜还是不能找出系统的问题。

现在初步怀疑系统出现deadlock的有问题的代码部分是suspend/resume transaction. 我们的系统在某些操作在一个Transaction里面需要先suspend当前的transaction，然后处理一个sub transaction，再resume先前的transaction。出现deadlock的时候都是在做这种类似的操作。

系统是Java的，用Datasource连接数据库，用Transaction Manager控制事务，在sql的查询分析器里用什么命令可以实现suspend/resume transaction？ 查找了联机丛书没有提到这些命令，真不知道Java的TM是发送什么命令.

请大家再帮帮忙，谢谢了！

[yone]

另外SQL会根据记录集的范围自动升级锁,所以UPDATE的
锁不是唯一不变的