sql server添加列阻塞案例

【1】测试代码

（1.1）测试数据

if db_id('test') is null
    create database test;
go

use test;
if object_id('test1') is not null
begin
    drop table test1
end

;with t
as
(
     select 1 as id,'abcdef' as str1
     union all
     select id+1,str1 from t
     where id+1<=1000000
)
select * into test1 from t   option(maxrecursion 0) ;
create clustered index CIX_ID on test1(id)

go

（1.2）测试会话

session1

begin tran
select * from test1 with(XLOCK)   where id >=999990
waitfor delay '00:10:00'
rollback tran

session 2

use test;
alter table test1 add str6 char(20) ;

session 3

use test;
set nocount on
declare @i int
set @i=1;
while @i<=1000
begin
    update test..test1 set id=id-1 where id=@i

    set @i=@i+1
    print @i
end

锁、请求查看

SELECT SPID = er.session_id ,Status = ses.status 
,[Login] = ses.login_name ,Host = ses.host_name ,BlockBy = er.blocking_session_id 
,DBName = DB_Name(er.database_id) ,cmd = er.command ,SQL_Parent = st.text 
,[SQL_Cliend] = SUBSTRING(st.text,er.statement_start_offset / 2
,(CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), st.text))* 2 ELSE er.statement_end_offset END - er.statement_start_offset ) / 2) 
,CPUTime = er.cpu_time 
,IOReads = er.logical_reads + er.reads ,IOWrites = er.writes 
,Waittype = er.wait_type 
,LastWaitType = er.last_wait_type 
,StartTime = er.start_time ,Protocol = con.net_transport 
,ConnectionWrites = con.num_writes 
,ConnectionReads = con.num_reads 
,ClientAddress = con.client_net_address 
,Authentication = con.auth_scheme 
FROM sys.dm_exec_requests er 
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st 
LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id 
LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id 
WHERE er.session_id > 50 and er.session_id <> @@SPID
and ses.login_name not in ('sa','sql_exporter')


sp_lock

【2】各版本测试

（2.1）sql server 2005 测试

Microsoft SQL Server 2005 - 9.00.1399.06 (X64)   Oct 14 2005 00:35:21   

Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) 

有聚集索引
执行顺序也按会话 1/2/3 执行；
　　会话1、先锁10行（表级别IX锁，行级别X锁）
　　会话2、然后加列（表IX锁获取，Sch-M 锁等待）=》结果 等待  LCK_M_IX ，被会话1阻塞
　　会话3、修改非会话1锁定的10行=》结果等待 LCK_M_SCH_M   ，被会话2阻塞

　　

 　　

（2.2）sql server 2016 测试

一样的结果会被阻塞，无论是否加 默认值（但如果表大，加默认值会有效果）

 　　

 　　

【参考文档】

sql server添加列并使用默认值填充已有记录

sql server添加列为什么很快？sql server中的online 添加非空字段（sql server2012新增），立刻加列