Resolving Deadlocks in SQL Server 2005_PHP Tutorial

Deadlocks in database operations are inevitable. This article does not intend to discuss how deadlocks occur. The focus is on solving deadlocks. Through SQL Server 2005, there seems to be a new solution.
If the following SQL statements are placed in two different connections and executed simultaneously within 5 seconds, a deadlock 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 to deal with deadlock is to sacrifice one of them, throw an exception, and roll back the transaction. In SQL Server 2000, once an exception occurs in a statement, T-SQL will not continue to run. In the sacrificed connection above, the print 'end tran' statement will not be run, so it is difficult for us to use T-SQL in SQL Server 2000. Further processing of deadlocks is provided.
It’s different now. SQL Server 2005 can capture exceptions in T-SQL, which provides us with a way to deal with deadlocks:
The following try...catch is used to solve 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 solution is of course to retry, but catching errors is a prerequisite. The waitfor after rollback is indispensable. After a conflict occurs, you need to wait for a period of time. The number of @retry can be adjusted to meet different requirements.
But now we are facing a new problem: the error is covered up. Once the problem occurs more than 3 times, the exception will not be thrown. SQL Server 2005 has a RaiseError statement that can throw exceptions, but it cannot throw the original exception directly, so the error that occurs needs to be redefined. 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'

http://www.bkjia.com/PHPjc/630998.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/630998.htmlTechArticleDeadlocks in database operations are inevitable. This article does not intend to discuss how deadlocks occur. The focus is on solving deadlocks. Locks, with SQL Server 2005, now seem to have a new solution. ...