Resolving deadlocks in SQL Server 2005

Source: Internet
Author: User
Tags commit continue end insert sql rollback
server| Solution

Database operation deadlock is unavoidable, this article does not intend to discuss how deadlocks are generated, the focus is to resolve deadlocks, through SQL Server 2005, now seems to have a new solution.

The following SQL statement is placed inside two different connections and is executed in 5 seconds, and deadlocks will occur.


Use Northwind

BEGIN Tran
Insert into Orders (CustomerId) VALUES (' ALFKI ')
WAITFOR DELAY ' 00:00:05 '
SELECT * from Orders where CustomerId = ' ALFKI '
Commit
print ' End Tran '
SQL Server's way of dealing with deadlocks is to sacrifice one of them, throw an exception, and roll back the transaction. In SQL Server 2000, once an exception occurs in the statement, T-SQL will not continue to run, and the print ' End Tran ' statement will not be run in the above-sacrificed connection, so it is difficult to further handle deadlocks in SQL Server 2000 's T-SQL.

Unlike now, SQL Server 2005 can capture exceptions in T-SQL, giving us a way to handle deadlocks:

The following try ... catch is used to resolve the deadlock.


SET Xact_abort on

DECLARE @r int
Set @r = 1
While @r <= 3
Begin
BEGIN Tran

Begin try
Insert into Orders (CustomerId) VALUES (' ALFKI ')
WAITFOR DELAY ' 00:00:05 '
SELECT * from Orders where CustomerId = ' ALFKI '

Commit
Break
End Try

Begin Catch
Rollback
WAITFOR DELAY ' 00:00:03 '
Set @r = @r + 1
Continue
End Catch
End
The workaround, of course, is to try again, but capturing the error is a prerequisite. Rollback behind the WAITFOR, after the conflict needs to wait for some time, @retry number can be adjusted to meet different requirements.

But now there is a new problem: The error is masked, but the problem occurs and more than 3 times, the exception is not thrown. SQL Server 2005 has a RaiseError statement that throws an exception but does not throw the original exception directly, so you need to redefine the error that occurred, and now the solution becomes this:


DECLARE @r int
Set @r = 1
While @r <= 3
Begin
BEGIN Tran

Begin try
Insert into Orders (CustomerId) VALUES (' ALFKI ')
WAITFOR DELAY ' 00:00:05 '
SELECT * from Orders where CustomerId = ' ALFKI '

Commit
Break
End Try

Begin Catch
Rollback
WAITFOR DELAY ' 00:00:03 '
Set @r = @r + 1
Continue
End Catch
End

If Error_number () <> 0
Begin
declare @ErrorMessage nvarchar (4000);
declare @ErrorSeverity int;
declare @ErrorState int;

Select
@ErrorMessage = Error_message (),
@ErrorSeverity = Error_severity (),
@ErrorState = Error_state ();

RAISERROR (@ErrorMessage,
@ErrorSeverity,
@ErrorState
);
End
I hope that SQL Server 2005 will be able to throw the original exception directly, such as providing a raiseerror without parameters.

So the scenario is a bit bloated, but encapsulating the deadlock problem into T-SQL helps clarify responsibilities and improve the clarity of the high-level system. Now, for DataAccess code, there's probably no need to consider the deadlock problem anymore.



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.