Resolving deadlocks in SQL Server 2005

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.