修改列长度中的锁

当我们修改一个大表列宽度或者类型的时候可能会锁表，针对这个问题我做了一些测试：

 一、首先附上完整的锁兼容性矩阵

 对上图的是代码说明：见下图

二、没有主键的表测试如下：

create table _m(c1  varchar(2000) NOT NULL,c2 varchar(2000),c3 varchar(2000))

insert _m
select NEWID(),NEWID(),NEWID()
go 1000
--第一个会话
begin TRAN
alter table _m 
  alter column C1 varchar(103)  
--第二个会话
select * from _m where C1='A0240D0C-38C8-49B1-B9AA-73B6268A40EE'

 

--第三个会话
SELECT request_owner_id , 
    resource_type,
    request_mode,
    resource_description,
    request_session_id,
    request_status,
    CASE 
    WHEN B.OBJECT_ID IS NULL THEN CAST(A.resource_associated_entity_id  AS nvarchar )
    WHEN B.OBJECT_ID IS NOT NULL THEN B.NAME
    end 'NAME/resource_associated_entity_id',
    DB_NAME(resource_database_id)as resource_database
FROM
    sys.dm_tran_locks A LEFT JOIN SYS.TABLES B WITH(NOLOCK)
    ON A.resource_associated_entity_id=B.OBJECT_ID
WHERE
    resource_type <> 'DATABASE' AND DB_NAME(resource_database_id)='Test'
ORDER BY request_owner_id;

 

 当前的表没有主键，根据第三个会话得到如下图，一共是三个进程号：87，93，84，这3个都可以使用DBCC INPUTBUFFER()查询到，87：第一个会话，93：第二个会话，84：由于93的查询而产生的架构共享锁（这个84在这里我们不考虑，本文后面我会写一点相关的）。

从图中1处可以看到93的请求状态是WAIT，当前该进程号93想放IS锁在表_m上面，但是放不上去，一直在等待，因为2处的87进程已经在这个表上面放了很多Sch-M（架构修改锁），从上面的兼容性矩阵可以看出Sch-M跟IS锁是互相冲突的，所以93一直在等待。因此，在修改表宽度的时候，表上会一直持有架构修改锁，正是这个锁导致其他用户不能访问这个表。当然，上面的会话1改成修改C2也是一样的锁表，即使你的查询条件是C1。

 再来说下架构共享锁，这个锁的兼容性还是比较好的，但是它跟Sch-M也是不兼容的，这个就不贴图了，测试代码如下：

--会话1
begin TRAN
select * from _m with(holdlock) where C1='0D172E7B-5469-42DA-B683-EBCE87641E3B'
--会话2
alter table _m 
alter column C1 varchar(97)

 

 其实关于修改字段的正确方法微软有给一些意见，我测试了微软的例子，感觉依然没什么用，还是会锁表，这是微软的文档

三、给表增加主键再测试

 alter table _m add constraint TTT primary key(C1)

 

发现根本不能修改C1的长度了，报错如下，修改字段2的长度，另一个会话窗口还是不能查询，没什么区别

 

 结论：当你修改表某个列的宽度或者类型的时候，不管你修改的是哪个字段查询的是哪个字段，查询都一定会被阻止，而且加了with(nolock)也不能访问