先用以下语句查询锁表进程(复制即可)
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type='OBJECT'
然后用以下语句执行解锁操作(注意替换锁表进程)
declare @spid int
Set @spid = 107 --锁表进程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid as varchar)
exec(@sql)
或者参考下面
https://blog.csdn.net/snowfoxmonitor/article/details/7182948
还可以模拟锁表测试
sqlserver 锁表处理
1.模拟锁表
//建立模拟表
CREATE TABLE Lock1(C1 int default ( 0 ));
CREATE TABLE Lock2(C1 int default ( 0 ));
INSERT INTO Lock1 VALUES ( 1 );
INSERT INTO Lock2 VALUES ( 1 );
2.发生死锁
//在新开窗口1执行
Begin Tran
Update Lock1 Set C1 = C1 + 1 ;
WaitFor Delay ' 00:01:00 ' ;
SELECT * FROM Lock2
Rollback Tran ;
//在新开窗口2执行
Begin Tran
Update Lock2 Set C1 = C1 + 1 ;
WaitFor Delay ' 00:01:00 ' ;
SELECT * FROM Lock1
Rollback Tran ;
3.查询发生死锁的表
select request_session_id spid,
OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type='OBJECT'
//清除死锁注意:在新开窗口执行语句否则会发生
declare @spid int Set @spid =121//spid为上一sql查询出来的结果这里是52、53、57
declare @sql varchar(1000)set @sql='kill '+cast(@spid as varchar)exec(@sql)
注:模拟死锁代码参照https://www.oschina.net/question/2004005_238833