查询死锁进程和表
SELECT request_session_id AS spid ,
OBJECT_NAME(resource_associated_entity_id) AS 'table'
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT';
杀死进程
KILL [spid];
原文链接:https://blog.csdn.net/mc_linfen/article/details/82115368
删除数据并重建索引,压缩日志
delete from pk_gateio where gateintime < '2019-01-01 00:00:01'
dbcc dbreindex(pk_gateio,'',70)
dbcc shrinkdatabase('ChePaiPark',10)
演示锁表造成等待
先创建表Test
create table Test(id int identity(1,1) primary key,
name varchar(100)
);
insert into Test(name) values ('aaa');
insert into Test(name) values ('aaa1');
insert into Test(name) values ('aaa2');
insert into Test(name) values ('aaa3');
insert into Test(name) values ('aaa4');
insert into Test(name) values ('aaa5');
新建一个查询窗口 A,执行
begin tran
update Test set name='ljkfdj12'
waitfor delay '00:00:50'
commit tran
再新建一个查询窗口 B,执行
select * from Test
可以看到A查询执行了50秒, B查询一直卡在那里直到A查询执行完成才立即完成
可见在没开启事务的情况下仍然可能因为锁表而等待
或者在A中执行:
begin tran
select * from Test with (tablockx) where id=3
waitfor delay '00:00:10'
commit tran
再新建一个查询窗口 B,执行
select * from Test
可以看到B查询被A查询阻塞
如果在A中使用行锁或更新锁,不会阻塞B查询
行锁:
select * from Test with (rowlock) where id=3
更新锁:
begin tran
--update Test set name='ljkfdj12'
--select * from Test with (tablockx) where id=3
--select * from Test with (rowlock) where id=3
select * from Test with (updlock) where id=3
waitfor delay '00:00:10'
commit tran
如果A中换成了更新锁,B中执行:
update Test set name='ljfdsra111'
where Id=3
则会被阻塞直到A完成
如果A中换成了更新锁,B中执行:
update Test set name='ljfdsra111'
where Id=4
则不会被阻塞,立即完成