Shulou(Shulou.com)06/01 Report--

This article mainly shows you "how to use TRY/ catch statement in sql to avoid deadlock", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to use TRY/ catch statement in sql to avoid deadlock" this article.

Deadlocks are normal and inevitable. If a deadlock occurs, a process will be sacrificed and the transaction will be rolled back. SQL will not retry on its own, but will need to retry with TRY/CATCH and WAITFOR. The specific methods are as follows

How to use TRY/CATCH statements to avoid deadlocks

Now, let's modify the body of the code using the TRY/CATCH statement. (for this example, you need to run the code in the SQL Server 2005 version. When using TRY/CATCH, the operation code and the error handling code are separate You should put the code that performs an operation in the TRY statement block and the error handling code in the CATCH statement block. If the code execution in the TRY statement block fails, the code execution jumps to the CATCH statement block. This method applies to almost all errors except those that prevent the entire batch from running (for example, missing objects). )

The following example rewrites the previously used code using the TRY/CATCH statement. The code title is the same, but the code body is different:

BEGIN TRANSACTION

BEGIN TRY

INSERT Authors VALUES

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

WAITFOR DELAY '00VOG 05'

SELECT COUNT (*) FROM Authors

COMMIT

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER () AS ErrorNumber

ROLLBACK

END CATCH

SELECT @ @ TRANCOUNT AS'@ @ Trancount'

Now, run this code in a side-by-side window connected to SQL Server 2005, before you need to confirm that any data in the authors table that may prevent the insert operation has been deleted, or you can use the pre-DELETE statement.

Both windows returned a @ @ TRANCOUNT level of 0, indicating that a deadlock still occurred, but the TRY/CATCH statement captured the deadlock that occurred this time. The batch processing of the deadlock victim is not aborted again, but the error can be seen in its output:

ErrorNumber

-

1205

@ @ Trancount

-

0

You should have found the power of TRY/CATCH statements. Because deadlock errors can be caught by CATCH statement blocks, the batch will no longer be aborted and T-SQL code can continue to execute. For deadlock victims, deadlock error 1205 puts the code into the CATCH statement block-here you can use the new error handler to browse for deadlock errors. The preceding code only replaces the @ @ ERROR variable with the ERROR_NUMBER () function, and you can also use ERROR_MESSAGE (), ERROR_PROCEDURE (), ERROR_SEVERITY (), and ERROR_STATE (). The functions of these functions are clear at a glance, and they provide more functionality than we have used in the past.

Notice that this pre-CATCH statement block contains a ROLLBACK. The reason for this is that even if a deadlock error is caught, the transaction will not be rolled back. The transaction still fails, but it is now your responsibility to roll back the transaction in the TRY/CATCH statement. So, what's the difference? Although you cannot keep the transaction going, you can retry the transaction!

Retry in a TRY/CATCH statement

In SQL Server 2000's T-SQL, what is frustrating about error 1205 is the advice it provides: "Rerun the transaction." The problem is, at least in the T-SQL of SQL Server 2000, you can't do that. However, because TRY/CATCH of SQL Server 2005 provides us with a way to catch deadlock errors, it is now possible to retry the transaction.

The following code body illustrates a way to perform a retry operation. This code still uses the same title as before:

DECLARE @ Tries tinyint

SET @ Tries = 1

WHILE @ Tries

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.