lock（3）——更新锁（U）、排它锁（X）、死锁及如何避免死锁

以下文章中详细介绍了update操作过程中更新锁及排它锁的分配情况

http://blog.csdn.net/zjcxc/article/details/27351779

按照以上文章中的追踪方式，发现其实文章lock（2）——创建及更新表过程中SQL SERVER锁资源分配情况中我们通过sys.dm_tran_locks动态视图查询出来的结果都是最终加锁的情况，并没有体现其中锁变化的情况。如果要想详细的知道锁的变化情况，还是使用profile的方式。

我这里为了加深印象与理解，就再将邹大师的测试自己做一遍：

--该进程为66，先创建表并初始化5条数据，然后在事务中更新数据
CREATE TABLE dbo.tb(

     c1 int,

     c2 char(10),

     c3 varchar(10)

);

GO

DECLARE @id int;

SET @id = 0;

WHILE @id <5

BEGIN;

     SET @id = @id + 1;

     INSERT dbo.tb VALUES( @id, 'b' + RIGHT(10000 + @id, 4), 'c' + RIGHT(100000 + @id, 4) );

END;



BEGIN TRAN

UPDATE dbo.tb SET c2 = 'xx' WHERE c1 = 2;

WAITFOR DELAY '00:00:30';

UPDATE dbo.tb SET c2 = 'xx' WHERE c1 = 5;

--ROLLBACK;

--进程69,开启一个事务,并更新tb表中的一条记录
BEGIN TRAN

UPDATE dbo.tb SET c2 = 'xx' WHERE c1 = 1;

ROLLBACK;

profile中的追踪情况如下：

进程66

进程69

我们首先看进程66执行的第一步（UPDATE dbo.tb SET c2 = 'xx' WHERE c1 = 2;），首先在表dbo.tb上加了IX锁，然后再在数据页上加了IU锁，最后再各个行加U锁，如果该行不是要更新的行，那么sqlserver会将获取的U锁又释放掉，如果该行刚好是我们要更新的锁，那么sqlserver就会将U锁变为IX，最后再变为X锁并进行修改，然后按照这种做法将所有的行扫描完。从以上过程来看，sql server就是从大范围到小范围一层层加锁，而且因为这里我们的表没有索引，所以导致对表进行了全表扫描。也可从profile的结果我们看到在第一步更新操作之后，表上的IX锁和page上的IU和第二行数据上的X锁都还没有释放。

再看进程66执行的第二步（UPDATE dbo.tb SET c2 = 'xx' WHERE c1 = 5;）首先获取页上的IU锁，然后获取第一行数据的U锁，但是这时由于进行进程69对该行持有X锁，并且也正在等待进程66释放第二行数据上的X锁，这时形成了死锁。最后sql server选择了回滚成本低的进程69为牺牲者，69释放了自己持有的锁，然后进程66像第一步那样一行一行的扫描数据，一开始对每一行持有U锁，发现不是自己要更新的数据行就释放该锁，如果是自己要更新的数据行，进一步将U锁变为IX锁，最后变为X锁，更新数据。

进程66第三步（提交事务），只有提交事务后，第二行数据和第五行数据上的X锁，Page上的IX和表上的IX锁才释放。

以上就是更新堆表中一条数据过程中锁的变化过程和死锁发生的过程。

 

下来我们想，那我们该如何来避免这个死锁呢？

这里我们可以尝试在c1列上添加一个索引

CREATE INDEX IX_tb_c1 ON dbo.tb
(
 c1 desc
)

然后我就先运行进程66并且不提交事务，然后再运行进程69，我发现69很快就运行完了，两个进程没有发生阻塞也没有发生死锁。

进程69的执行情况

进程66运行情况

 

从以上的结果我们可以看出，加了索引以后（聚集索引和非聚集索引都可以，聚集索引就更简单了，直接通过key定位，而且不像非聚集索引，要维护两个数据结构），sqlserver没有走表扫描了，而是直接定位到数据进行修改，这样缩小了锁的范围，避免了上面死锁的发生，提高了并发性。

其他有关死锁发生的条件及避免死锁发生的方法，大家可以参考下以下博客：

http://www.cnblogs.com/happyhippy/archive/2008/11/14/1333922.html