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)

注：模拟死锁代码参照http://www.2cto.com/database/201309/242860.html

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!