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)
