tailieunhanh - Hướng dẫn học Microsoft SQL Server 2008 part 143

Thay vì để cho SQL Server quyết định giao dịch sẽ được thế bế tắc''nạn nhân'',''một giao dịch có thể tình nguyện''để phục vụ như các nạn nhân bế tắc. Đó là, các giao dịch với các ưu tiên bế tắc thấp nhất sẽ được cuộn lại đầu tiên. | Part IX Performance Tuning and Optimization declare @retry i nt set @retry 1 while @retry 1 begin begin try set @retry 0 begin transaction UPDATE SET Name qq WHERE DepartmentID 2 UPDATE SET Name x WHERE DepartmentID 1 commit transaction end try begin catch if error_number 1205 begin print error_message set @retry 1 end rollback transaction end catch end Instead of letting SQL Server decide which transaction will be the deadlock victim a transaction can volunteer to serve as the deadlock victim. That is the transaction with the lowest deadlock priority will be rolled back first. Assuming the deadlock priorities are the same SQL will fallback to the rollback cost to determine which to rollback. The following code inside a transaction will inform SQL Server that the transaction should be rolled back in case of a deadlock SET DEADLOCK_PRIORITY LOW The setting actually allows for a range of values from -10 to 10 or normal 0 low -5 and high 5 . Minimizing deadlocks Even though deadlocks can be detected and handled it s better to avoid them altogether. The following practices will help prevent deadlocks Set the server setting for maximum degree of parallelism maxdop to 1. Keep a transaction short and to the point. Any code that doesn t have to be in the transaction should be left out of it. 1382 Managing Transactions Locking and Blocking 66 Never code a transaction to depend on user input. Try to write batches and procedures so that they obtain locks in the same order for example TableA then TableB then TableC. This way one procedure will wait for the next avoiding a deadlock. Plan the physical schema to keep data that might be selected simultaneously close on the data page by normalizing the schema and carefully selecting the clustered indexes. Reducing the spread of the locks will help prevent lock escalation. Smaller locks help prevent lock contention. Ensure that locking is done at the lowest .