数据库原理 实验 死锁的产生和解除

死锁的产生和解除

死锁(deadlock)指进程之间互相永久阻塞的状态，Microsoft SQL Server 数据库引擎实例可以检测到死锁，并选择终止其中一个事务以干预死锁状态。两个进程发生死锁的典型例子是：进程T1中获取锁A，申请锁B;进程T2中获取锁B，申请锁A，下面动手来演示死锁的发生和检测：

1. 在SQL Server Management Studio 的“标准”工具栏上，单击“新建查询”按钮。此时将使用当前连接打开一个查询编辑器窗口。

2.在查询编辑器窗口输入如下脚本，并执行。将在TempDB数据库中创建Teacher表并填充3条数据：

USE TempDB;/*使用TempDB作为当前数据库*/

GO

--TempDB数据库中若存在用户创建的表Teacher，则删除之。

IF OBJECT_ID(N'TempDB..Teacher', N'U')IS NOT NULL

DROP TABLE Teacher;

GO

CREATE TABLE Teacher ([ID] int identity,[name]

nchar(10),[birthday]datetime,depatrment nchar(4),salary int null)

GO

Begin transaction

Insert into teacher values('王伟',1990-03-02,'计算机系',1000)

Insert into teacher values('李红',1900-08-08,'计算机系',1500)

Insert into teacher values('李强',1975-03-02,'计算机系',2000)

If@@error>0

rollback transaction

Else

commit transaction

select*from Teacher;

3. 在SQL Server Management Studio的两个窗口中同时执行下面的查询：

这段代码在默认的READ COMMITTED隔离级别下运行，两个进程(任务)分别在获取一个排它锁的情况下，申请对方的共享锁从而造成死锁。

可见一个进程可以正常更新并显示结果，而另一个进程已经被回滚：

消息 1205，级别 13，状态 45，第 3 行

事务(进程 ID 53)与另一个进程被死锁在锁资源上，并且已被选作死锁牺牲品。请重新运行该事务。

(1 row(s) affected)

Msg 1205, Level 13, State 45, Line 3

Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

4. 启动 SQL Server Profiler，选择下面4种Events：

再执行一次上面的死锁实验，可以看到如下所示的死锁图：

非常有趣的一点是：第二次执行上述语句不会发生死锁!这是因为此时两个进程中，SQL Server会智能的识别出update语句是不需要做的，所以都不会去获取排它锁，当然也就不会死锁了。SQL Server 2008 的查询优化器还真是非常强大!

处理死锁

Microsoft SQL Server 数据库引擎实例选择某事务作为死锁牺牲品后，将终止当前批处理，回滚事务并将 1205 号错误消息返回应用程序。

Your transaction (process ID #52) was deadlocked on {lock | communication buffer | thread} resources with another process and has been chosen as the deadlock victim. Rerun your transaction.

由于可以选择任何提交 Transact-SQL 查询的应用程序作为死锁牺牲品，应用程序应该有能够捕获 1205 号错误消息的错误处理程序。如果应用程序没有捕获到错误，则会继续处理而未意识到已经回滚其事务且已发生错误。

通过实现捕获 1205 号错误消息的错误处理程序，使应用程序得以处理该死锁情况并采

取补救措施（例如，可以自动重新提交陷入死锁中的查询）。通过自动重新提交查询，用户不必知道发生了死锁。

应用程序在重新提交其查询前应短暂暂停。这样会给死锁涉及的另一个事务一个机会来完成并释放构成死锁循环一部分的该事务的锁。这将把重新提交的查询请求其锁时，死锁重新发生的可能性降到最低。

TRY...CATCH 可用于处理死锁。CATCH 块可以捕获 1205 死锁牺牲品错误，并且事务可以回滚，直至线程解锁。

下面的示例显示如何使用 TRY...CATCH 处理死锁。第一部分创建用于说明死锁状态的表和用于打印错误信息的存储过程。

USE tempdb;

GO

-- Verify that the table does not exist.

IF OBJECT_ID(N'my_sales',N'U')IS NOT NULL

DROP TABLE my_sales;

GO

-- Create and populate the table for deadlock simulation.

CREATE TABLE my_sales

(

Itemid INT PRIMARY KEY,

Sales INT not null

);

GO

INSERT my_sales (itemid, sales)VALUES(1, 1);

INSERT my_sales (itemid, sales)VALUES(2, 1);

GO

-- Verify that the stored procedure for error printing

-- does not exist.

IF OBJECT_ID(N'usp_MyErrorLog',N'P')IS NOT NULL

DROP PROCEDURE usp_MyErrorLog;

GO

-- Create a stored procedure for printing error information.

CREATE PROCEDURE usp_MyErrorLog

AS

PRINT

'Error '+CONVERT(VARCHAR(50),ERROR_NUMBER())+

', Severity '+CONVERT(VARCHAR(5),ERROR_SEVERITY())+

', State '+CONVERT(VARCHAR(5),ERROR_STATE())+

', Line '+CONVERT(VARCHAR(5),ERROR_LINE());

PRINT

ERROR_MESSAGE();

GO

--会话

USE tempdb;

GO

-- Declare and set variable

-- to track number of retries

-- to try before exiting.

DECLARE @retry INT;

SET @retry = 5;

-- Keep trying to update

-- table if this task is

-- selected as the deadlock

-- victim.

WHILE(@retry > 0)

BEGIN

BEGIN TRY

BEGIN TRANSACTION;

UPDATE my_sales

SET sales = sales + 1

WHERE itemid = 1;

WAITFOR DELAY'00:00:13';

UPDATE my_sales

SET sales = sales + 1

WHERE itemid = 2;

SET @retry = 0;

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

-- Check error number.

-- If deadlock victim error,

-- then reduce retry count

-- for next update retry.

-- If some other error

-- occurred, then exit

-- retry WHILE loop.

IF(ERROR_NUMBER()= 1205)

SET @retry = @retry - 1;

ELSE

SET @retry =-1;

-- Print error information.

EXECUTE usp_MyErrorLog;

IF XACT_STATE()<> 0

ROLLBACK TRANSACTION;

END CATCH;

END;-- End WHILE loop.

GO

--会话

USE tempdb;

GO

-- Declare and set variable

-- to track number of retries

-- to try before exiting. DECLARE @retry INT;

SET @retry = 5;

--Keep trying to update

-- table if this task is

-- selected as the deadlock

-- victim.

WHILE(@retry > 0)

BEGIN

BEGIN TRY

BEGIN TRANSACTION;

UPDATE my_sales

SET sales = sales + 1

WHERE itemid = 2;

WAITFOR DELAY'00:00:07';

UPDATE my_sales

SET sales = sales + 1

WHERE itemid = 1;

SET @retry = 0;

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

-- Check error number.

-- If deadlock victim error,

-- then reduce retry count

-- for next update retry.

-- If some other error

-- occurred, then exit

-- retry WHILE loop.

IF(ERROR_NUMBER()= 1205)

SET @retry = @retry - 1;

ELSE

SET @retry =-1;

-- Print error information.

EXECUTE usp_MyErrorLog;

IF XACT_STATE()<> 0

ROLLBACK TRANSACTION;

END CATCH;

END;-- End WHILE loop.

GO

会话2被选做死锁牺牲品 会话1 成功执行