NOVOTS KMS 词汇表 Glossary    联系我们 Contact Us
查询 Search  
   
按类别浏览 Browse by Category
NOVOTS KMS .: 数据库 .: TRYCATCH解决SQL2005的死锁

TRYCATCH解决SQL2005的死锁

SQL Server 2000 2005中都能引起死锁。在本文中,我使用 SQL Server 2005的最新CTP(社区技术预览,Community Technology Preview)版本,SQL Server 2005 Beta27月发布)也同样适用。如果您没有Beta2或最新的CTP版本,请下载SQL Server 2005 Express的最新版本,用它来进行试验。

可能发生的死锁情况有很多,但最有趣、最微妙的是那些关于阅读器和编写器互相阻塞的死锁。以下代码在pubs数据库中就产生了这样一个死锁。(您可以在SQL Server 2000的两个Query Analyzer窗口中或SQL Server 2005的两个Management Studio queries中并列运行这段代码。)在其中一个窗口中的代码正文前面添加下列语句:

-- Window 1 header

DECLARE @au_id varchar(11),@au_lname varchar(40)

SELECT @au_id = '111-11-1111',@ au_lname='test1'

在第二个窗口中添加下列语句,进行第二次连接:

-- Window 2 header

DECLARE @au_id varchar(11),@au_lname varchar(40)

SELECT @au_id = '111-11-1112', @au_lname = 'test2'

在两个窗口中都使用下列语句作为代码正文:

-- Body for both connections:

BEGIN TRANSACTION

INSERT Authors VALUES

(@au_id, @au_lname, '', '', '', '', '', '11111', 0)

WAITFOR DELAY '00:00:05'

SELECT *

FROM authors

WHERE au_lname LIKE 'Test%'

COMMIT

在第三个窗口中运行下列语句,确保authors表格中没有任何包含以下id的数据:

DELETE FROM authors WHERE au_id = '111-11-1111'

DELETE FROM authors WHERE au_id = '111-11-1112'

5秒钟内同时执行窗口1和窗口2。因为每个窗口都要等待至少5秒钟的时间才能发出SELECT语句,所有每个连接都将完成INSERT操作,这样就保证了两个窗口中的INSERT操作在各自的SELECT语句发布前就已经完成了。每个窗口中的SELECT语句都尝试读取authors表格中的所有数据,查找au_lname字段值中类似“Test%”格式的数据。因此,两个窗口中的SELECT语句都将尝试读取各自连接中的插入数据—也读取对方连接中的插入数据。

Read COMMITTED隔离级别通过发布共享锁确保SELECT语句永远不读取未提交的数据。对于同一个资源,共享锁与排它锁互不兼容,请求者在发布共享锁之前必须等待排它锁释放。每个连接对于插入的数据都设置了排它锁,因此尝试读取对方插入数据的SELECT语句将试图解除插入数据的共享锁,但它会被阻塞。两个连接将互相阻塞,从而形成一个死锁。SQL Server的锁定管理器检测到死锁时,将中止其中的一个批处理,回滚它的事务,释放它的阻塞锁,以便其他事务能够完成。作为死锁牺牲品的事务将回滚,其他事务则将成功完成。

如何使用TRY/CATCH语句避免死锁

现在,让我们来使用TRY/CATCH语句修改代码正文。(对于本示例,需要以 SQL Server 2005 版本运行代码。)使用TRY/CATCH时,操作代码和错误处理代码是分开的。您应该将执行一个操作的代码放在TRY语句块中,将错误处理代码放在CATCH语句块中。如果TRY语句块中的代码执行失败,代码执行将跳到CATCH语句块。(除了那些防碍整个批处理运行的错误(如,丢失对象),该方法几乎适用于所有的错误。)

下示例使用 TRY/CATCH 语句对前面使用的代码进行了改写。代码标题相同,但是代码正文不同:

BEGIN TRANSACTION

BEGIN TRY

INSERT Authors VALUES

(@au_id, @au_lname, '', '', '', '', '', '11111', 0)

WAITFOR DELAY '00:00:05'

SELECT COUNT(*)  FROM Authors

COMMIT

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER() AS ErrorNumber

ROLLBACK

END CATCH;

SELECT @@TRANCOUNT AS '@@Trancount'

现在,在连接到SQL Server 2005的并列窗口中运行这些代码,在此之前您需要确认已经删除了authors 表格中任何可能阻止插入操作的数据;或者您可以使用前置DELETE语句。两个窗口返回的 @@TRANCOUNT 级别都为0,这表明仍然发生了死锁,但TRY/CATCH语句捕获了这次发生的死锁。死锁牺牲品的批处理没有再次中止,可在它的输出结果中看到错误:

ErrorNumber

-----------

1205

@@Trancount

-----------

0

您应该已经发现TRY/CATCH语句具有的威力了。因为死锁错误能够为CATCH语句块所捕获,所以批处理将不再中止,T-SQL代码也能继续执行。对于死锁牺牲品而言,死锁错误1205将代码放入CATCH语句块—在这里您可以使用新的错误处理函数浏览死锁错误。前置代码仅使用ERROR_NUMBER()函数取代@@ERROR变量,您也可以使用ERROR_MESSAGE()ERROR_PROCEDURE()ERROR_SEVERITY()ERROR_STATE()。这些函数的功能一目了然,它们提供的功能比我们以往使用的更多。

请注意,这个前置CATCH语句块包含一个ROLLBACK。这样做的原因是,即使捕获了死锁错误,事务也不会回滚。事务仍然要失败,但是,现在您有责任在 TRY/CATCH 语句中回滚事务。那么,区别在哪里?尽管您不能使事务继续进行,但是您能够 重试事务!

TRY/CATCH 语句中进行重试

SQL Server 2000T-SQL中,错误1205令人沮丧之处是它提供的建议:“Rerun the transaction.”问题是,至少在SQL Server 2000T-SQL中,您不能做到这一点。但是由于SQL Server 2005TRY/CATCH为我们提供了捕获死锁错误的方法。现在重试事务是可能实现的。

以下代码正文说明了一种执行重试操作的方法。这段代码仍然使用与前面相同的标题:

DECLARE @Tries tinyint

SET @Tries = 1

WHILE @Tries <= 3

BEGIN

BEGIN TRANSACTION

BEGIN TRY

INSERT Authors VALUES

(@au_id, @au_lname, '', '', '', '', '',

'11111', 0)

WAITFOR DELAY '00:00:05'

SELECT * FROM authors WHERE au_lname LIKE 'Test%'

COMMIT

BREAK

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER() AS ErrorNumber

ROLLBACK

SET @Tries = @Tries + 1

CONTINUE

END CATCH;

END

这段代码的功能是通过一个WHILE循环添加一个重试操作。我将重试次数设置为3,重试次数是可以配置的。至少我们现在有了一种在T-SQL内重试一个死锁牺牲品代码的方法。

但是,需要注意整个事务是在WHILE循环内进行的—而不是在循环外部。因此执行循环时,事务不仅在每个循环体内部开始,而且也在其中结束—不是TRY语句块执行完毕,返回一个COMMIT,就是CATCH语句块执行,返回一个ROLLBACK。如果TRY成功,TRY语句块将以一个BREAK语句结束,退出WHILE循环。否则,CATCH语句块将重试计数器加1,以一个CONTINUE语句结束本次循环,重新执行下次WHILE循环。事实上,您有实现重试事务的代码—就像错误1205告诉我们做的那样。但现在,重试操作完全在T-SQL内部完成。

sql Server 2005也提供帮助解决死锁问题的其他方法,例如SNAPSHOT ISOLATION 级别和用于READ COMMITTED的新选项(称为READ COMMITTED SNAPSHOT)。然而,这一事实现在,通过SQL Server 2005,您能够对事务进行编码并捕获死锁错误(并重试它们)—已经意味着您拥有一个可任意支配、功能更加强大的工具。

 


这篇文章对你多有用?

用户评语

添加评语
当前还没有评语.


.: .: .: .: .:
[ 登陆 ]
北京护航科技有限公司 2006

Novots Technologies Limited