Database operation deadlocks are inevitable. This article does not discuss how deadlocks are generated, but focuses on solving deadlocks. Through SQL Server 2005, there seems to be a new solution.
When the following SQL statement is placed in two different connections and executed simultaneously within five seconds, a deadlock will occur.
Use Northwind
Begin tran
Insert into Orders (mermerid) values ('alfki ')
Waitfor delay '00: 00: 05'
Select * from Orders where CustomerId = 'alfki'
Commit
Print 'end tran'
The SQL Server method to deal with deadlocks is to sacrifice one of them, throw an exception, and roll back the transaction. In SQL Server 2000, once a statement exception occurs, the T-SQL will not continue to run and the print 'end tran 'statement will not be run in the link sacrificed above, so it is difficult for us to further process the deadlock in the T-SQL of SQL Server 2000.
Now it's different, SQL Server 2005 can capture exceptions in the T-SQL, which provides us with a way to handle deadlocks:
Try... catch is used below 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 (mermerid) 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 retry, but error capture is a prerequisite. Waitfor after rollback is indispensable. It takes some time to wait after a conflict. The number of @ retries can be adjusted to meet different requirements.
But now there is another new problem: the error is hidden. Once the problem occurs more than three times, the exception will not be thrown. SQL Server 2005 has a RaiseError statement that can throw an exception, but cannot directly throw the original exception. Therefore, you need to redefine the error. Now, the solution is like this:
Declare @ r int
Set @ r = 1
While @ r <= 3
Begin
Begin tran
Begin try
Insert into Orders (mermerid) values ('alfki ')
Waitfor delay '00: 00: 05'
Select * from Orders where CustomerId = 'alfki'
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.
