SQL SERVER 事务锁 (1)

创建SQL脚本，设置测试环境

--关于死锁
IF EXISTS(Select 1 From Sysobjects Where Name='Test_Dead')
BEGIN
	SELECT * FROM Test_Dead
END
ELSE
BEGIN
	CREATE TABLE Test_Dead
	(
		id	INT,
		name	VARCHAR(20),
		info	VARCHAR(20),
	) 
	CREATE CLUSTERED INDEX IX_Test ON DBO.Test_Dead(id)
	CREATE NONCLUSTERED INDEX IX_Test_name ON DBO.Test_Dead(name)
	INSERT INTO Test_Dead VALUES(1,'kk',null),(2,'mm',null)
	SELECT * FROM Test_Dead
END

--事务1

BEGIN TRAN
	update dbo.Test_Dead set info='A' where id =1
	waitfor delay '00:00:10'
	update dbo.Test_Dead set info='B' where id =2
COMMIT TRAN

--事务2

BEGIN TRAN
	update dbo.Test_Dead set info='C' where id =2
	update dbo.Test_Dead set info='D' where id =1
COMMIT TRAN

先后开始执行：事务1和事务2 ===> 开始出现死锁，如下图所示

SQLSERVER profiler 工具查看，如下图

至此，模拟事务锁的运行环境已经搭建完成。