Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 313

practical. In order to prevent deadlock SQL Server would have to disallow any one of the four conditions
required which would be a sufficient measure. For example, one technique for preventing deadlocks is
to force a database process to acquire all required database objects. Furthermore, the process would not
be able to continue until all objects were acquired. This would certainly work, but the impact to the users
would be unacceptable.
Because prevention and avoidance aren't practical options, SQL Server implements a technique of
detection and recovery. Periodically SQL Server checks to see if any connections are blocking each
other — deadlock. When it finds two deadlocked connections SQL Server will choose one of the
connections as the deadlock victim and the processing done by that connection will be rolled back. SQL
Server chooses the victim based upon which one will require the least amount of work to roll back.
So, like the SQL Server engineers who strive to minimize deadlock states, you can take steps to minimize
deadlock states with design and development. Also, just like SQL Server, you can design and implement
a recovery scheme. The remainder of the section will be targeted toward achieving this goal.
Preventative Measures
Because deadlocks and blocking are variations on the same problem the techniques for addressing them
work for both. The following steps will work toward reducing these problems.
KeepingTransactionsasShortasPossible
Ideally, transactions (denoted with a BEGIN TRANS) contain only one DML statement. However, this
is not always possible. It's commonplace that multiple DML statements occur within the context of a
single transaction. For example, updating an employee's name and address information typically involves
updating multiple tables. This is an example where you may want all statements to succeed before
committing the changes, otherwise you want to roll back the changes. The goal here is to make sure
that the minimum number of DML statements occur within a transaction definition, thereby making
the transaction as short as possible. In addition to that you should make sure that once the transaction
begins, additional tasks, such as assigning variables, verifying data, and conditional statements, have
been executed.
Don'tAskforUserInputDuringaTransaction
This seems like an obvious practice, but it's worth stating. If a transaction has a user input requirement
before the transaction can finish this could definitely cause problems. Try coding the transaction without
the user input requirement. If that's not practical, then at least configure the application to time out and
abort a suspended transaction. This is essentially apartofthefirstrule.Eventhoughyoumaycodea
transaction with only one DML statement as advised above, adding a pause for the user will effectively
lengthen the transaction, which goes against the first rule.
ProgrammingtheLowest-PossibleIsolationLevel
Chapter 8 describes isolation levels. The higher the isolation level, is the more likely blocking will occur.
However, the multi-user nature of the system is diminished. The lower the isolation level, the less likely
blocking will occur. However, the lower isolation level can increase the potential for dirty reads or lost
updates. Thus, choose the lowest level that is practical in your application. Remember that SQL Server's
default isolation level is Read Committed.