对同一个表的死锁问题

Question

http://topic.csdn.net/u/20080721/20/15a60db6-34b5-4ea1-b392-11c42270aaab.html?65217
在精华版上看到这样一个贴,地址如上.我把有疑惑的地方描述一下,是对同一个表的死锁问题.表结构
create table T1(ID int primary key,Col1 int,Col2 nvarchar(20))insert T1 select 1,101,'A'
insert T1 select 2,102,'B'
insert T1 select 3,103,'C'create index IX_t1_col1 on t1(col1)连接窗口1代码如下:
while 1=1
    update T1 set col1=203-col1 where ID=2连接窗口2代码如下:
declare @i  nvarchar(20)
while 1=1
    set @i=(select col2 from T1 with(index=IX_t1_col1)where Col1=102);
处理方法
1:  删除非聚集索引
   drop index IX_t1_col1 on t1
2:  建一个覆盖索引
   A、drop index IX_t1_col1 on t1
    B、create index IX_t1_col1_col2 on t1(col1,col2)我理解的死锁的造成是,
在同一时间两个事务都占着对方想获取的资源,互不释放,僵持着,然后数据库来牺牲其中一个.问题:
1,虽然两个窗口都是"while 1=1",无限制的循环下去,
可循环一次就是一个加锁和释放锁的过程,既然有释放,怎么会死锁?2,这两个处理方法有用吗?为什么?
  特别是第2个"建一个覆盖索引"和原来表上的索引几乎是一样,无非索引中多了个字段.

Answer 1

while 1=1 ?那不是永远循环下去,不是死琐了,是死循环了.

Answer 2

表结构
create table T1(ID int primary key,Col1 int,Col2 nvarchar(20))insert T1 select 1,101,'A'
insert T1 select 2,102,'B'
insert T1 select 3,103,'C'create index IX_t1_col1 on t1(col1)连接窗口1代码如下:
while 1=1
  update T1 set col1=203-col1 where ID=2连接窗口2代码如下:
declare @i nvarchar(20)
while 1=1
  set @i=(select col2 from T1 with(index=IX_t1_col1)where Col1=102);--分析
连接窗口1此时因为ID是PK，所以动态调节锁定为KEY锁，其他几个为IX，T1为IX连接窗口2此是虽然利用其他索引，但它只包含COL1，未包含COL2，会利用ID 来LOOKUP ，连接1肯定要等待连接2释放排它锁

Answer 3

楼主你对死锁的理解很正确。
连接窗口2的代码：set @i=(select col2 from T1 with(index=IX_t1_col1)where Col1=102);
其实可以理解成是由两个步骤组成的事务，步骤1是从表中查询，步骤2是给变量赋值。

Answer 4

我也想知道，顺便求 敏感词库 一份 ，[email protected]
  

Answer 5

疑惑:"步骤1是从表中查询，步骤2是给变量赋值",
既然窗口2的步骤1已锁定资源,那么怎么会执行不了步骤2呢?
连接窗口1无非就等待一下-----
再说一下,这两个窗口的代码是在精华版上看到的,地址:http://topic.csdn.net/u/20080721/20/15a60db6-34b5-4ea1-b392-11c42270aaab.html?65217
发贴人:中国风

Answer 6

窗口2的代码可以看成是两个步骤，则在以下情况下会死锁：
1.窗口2步骤1代码 --锁定资源，未释放（有没有索引IX_t1_col1会导致这里的锁定类型和锁定的资源不一样）
2.窗口1代码 --锁定资源，等待1释放资源
3.窗口2步骤2代码 --等待2释放资源 窗口2的两个步骤之间间隔时间非常短，实际工作中很难重现这个死锁，但在本例中，因为两个会话都是无限循环，所以容易重现。其实，只要把窗口2中的代码：
set @i=(select col2 from T1 with(index=IX_t1_col1)where Col1=102);
改成：
select @i=col2 from T1 with(index=IX_t1_col1)where Col1=102;
即可完全避免死锁。

Answer 7

更新锁:开始是共享锁,然后升级为排它锁.窗口2的代码可以看成是两个步骤，则在以下情况下会死锁：
1.窗口2步骤1代码 --锁定资源，未释放（有没有索引IX_t1_col1会导致这里的锁定类型和锁定的资源不一样）加共享锁.
2.窗口1代码 --锁定资源，等待1释放资源加更新锁,
a,若1没释放了共享锁,则更新锁是在共享锁的状态,
b,若1释放了共享锁,则更新锁可升级为排它锁.
3.窗口2步骤2代码 --等待2释放资源  
加共享锁.a的话,窗口2步骤2照常执行,因为共享锁互相兼容.不会死锁了.b的话,就是"窗口2步骤2代码 --等待2释放资源 ",总会等到2释放资源的时候的,也不会死锁了怎么会死锁啊??

Answer 8

连接1:先获取ID=2的共享锁,然后排它锁定,
在此同时连接2已获取索引键值COL1=102的共享锁定
(循环来造成,如果连接1已经完成更新,则连接2根本查不到102这个键值),
因不包含COL2所以要ID=2来LOOKUP ,所以要等待连接1释放ID=2的排它锁定,等待ID=2的共享锁,连接1还要UPDATE T SET COL1=203-COL1,此时要更新索引键值COL1=102,需要获取COL1=102的排它锁定,但连接2未释放COL1=102的共享锁定,根据上面的,连接1要提交需要等待连接2释放COL1=102的共享锁释放,连接2需要等待连接1的ID=2的排它锁定释放,互相等待死锁!!!解决方法:
1删除索引后不会造成连接1: COL1=102的锁等待
2建立覆盖索引后,连接2不需要等待连接1ID=2的排它锁释放

Answer 9

多谢楼上的朋友,特别感谢sql77