SQL Server 锁的排队机制，sqlserver排队机制

SQL Server 锁的排队机制，sqlserver排队机制

1、新建一个表，插入1010000数据：

create table test(id int identity(1,1) ,name varchar(600)) 
go


insert into test
values(replicate('a',600));
go 1010000


create index idx_test_id on test(id)

2、新开一个会话（A），运行如下语句，由于没有提交，所以会阻塞其他药修改相同数据的会话：

begin tran
	update test 
	set name = replicate('f',500)
	where id = 100000

3、再新开一个会话（B），运行如下语句，由于需要修改id为 100000的数据，所以被上一个会话A阻塞了。

需要特别注意的是，在语句中使用了查询提示repeatableread，那么select语句对记录获取的S锁，只有在commit或rollback时，才会释放。

begin tran

select *
from test with(repeatableread)
where id >= 9000 and id <= 100000

select  sysdatetime()

begin tran
	  
	update test 
	set name = replicate('k',589)
	where id = 100000

	select  sysdatetime()

5、在会话A中，通过commit来提交事务，这个时候，会话B会马上返回结果，但会话C还是继续被阻塞，这个时候C被B会话阻塞。

接下来，在会话B中执行commit，于是会话C立即返回。

从以上的现象大致能看出，数据库锁中的队列机制，也就是当很多会话都需要访问相同的资源时，会有一个队列机制，一开始A对资源加上独占锁，获取到资源，但没有提交，所以没有释放资源上的锁。

会话B也需要访问相同的资源，由于会话需要读数据，所以需要加上共享锁，但由于共享锁和之前资源上的独占锁，不兼容，就导致了会话B被阻塞住了，那么会话B就进入到一个等待队列里。

会话C也需要访问相同的资源，由于需要修改数据，所以需要加上独占锁，但于会话A已加上的独占锁，不兼容，于是会话C也被阻塞住了，那么会话C也会进入到等待队列中，排在会话B之后。

会话C被阻塞的原因：

从上图可以看出，会话C在把Page  1  ：21339上的IU锁改为IX锁时，没有grant，而是处于convert状态，之所要修改，是因为需要修改id为100000的数据。

而之前已经在1  ：21339上加了IU锁，在RID 1:21339:1 上加了U锁，要修改这个这个页中的数据，必须要把IU锁修改为IX锁，如果成功，那么继续把U锁修改为X锁。

从上述分析中，可以看出SQL Server中的锁采用是队列的机制，即先进先出。

这个问题要具体分析：
第一，事务隔离级别基本两种模式，一种是阻塞式（read committed，repeatable read，serializable）
，一种是非阻塞式(read uncommitted,snapshot)。

默认是read committed，这种情况一般在更新表的时候，如果不使用hint 提示，基本是先对表添加IX锁，级别不算高，基本和其他锁兼容，但是repeatable read，serializable 事务隔离级别就会先对表添加IX锁，然后向X锁转化，而X锁和大多数锁都不兼容，容易发生表阻塞。

第二种隔离级别不会有以上问题，但是又引入了其它的问题。

以上是一种情况。
另外一种就是 锁升级，一个锁是96B内存，如果太多，sqlserver就会升级为表锁，一般是5000以上行级锁就升级为一个表X锁。
所以适当的文件分组和表分区 是有必要的。

其次就是资源互相引用导致事务长时间不能释放，导致真正的死锁，不过SQL2005以后，这种情况发生的概率很低。

留个问题你自己去想。

两个SQL，两个连接，同时执行。

update A set A.NAME=xxx where A.id=55

update A set A.NAME=xxx where A.id=56, 如果 56 不存在你说会发生什么情况呢？
 

前面两位兄弟回答的不是死锁，是正常的锁定。

死锁是这样形成的，假设有两个事物A和B
A事物在执行中需要更新两个表，假设为T1，T2，此时A已执行完T1，正在申请使用T2.
B事物也需要更新这两个表，但B事物先执行了T2,正在申请使用T1，
因为T1已被A事物锁定，所以B必须等待A事物执行完后释放锁，但A事物此时正在申请T2，而T2确被B事物先锁定了，需等待B事物完成后释放锁后才可获得T2的锁，此时死锁就发生了，如果没有死锁机制，这两个事物就会一直等下去。
sql server会定期检查死锁，如果发现死锁，就会权衡两个事物，牺牲掉其中一个执行代价较小的事物，使另一个事物能继续执行。

要避免死锁的发生，有很多需要注意的，如
1.保持事物尽可能的简短。
2。事物更新的顺序尽量一致，如上例中A和B如果更新顺序都为T1，T2或T2，T1的话就不会发生死锁了。
3.可以修改锁的粒度，如页锁改为行锁