sql server添加列阻塞案例

【1】测试代码
- （1.1）测试数据
- 锁、请求查看
【2】各版本测试
- （2.1）sql server 2005 测试
- （2.2）sql server 2016 测试
【参考文档】

【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