1066
Part VIII: Performance Tuning and Optimization
Minimizing Deadlocks
Even though deadlocks can be detected and handled, it’s better to avoid them altogether.
The following practices help prevent deadlocks:■ (^) Keep a transaction short and to the point. Any code that doesn’t need to be in the
transaction should be left out of it.
■ (^) 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, and then TableC. This way, one pro-
cedure can wait for the next, and a deadlock can likely be avoided.
■ (^) Choose appropriate indexes to support your data usage. The quicker SQL Server can
fi nd the data that needs to be modifi ed, the shorter the duration of the transaction.
■ (^) Don't increase the isolation level unless it’s necessary. A stricter isolation level
likely increases the duration of the transaction, which increases locks to be held
longer and limit database concurrency.
Understanding SQL Server Locking
SQL Server implements the I (Isolation) portion of the ACID property via locks. Locks
ensure that the data modifi ed by one transaction is protected from data modifi ed in a dif-
ferent transaction. There are a series of different levels for locking data. Before these locks
can be controlled, they must be understood. SQL Server offers several methods to control
locks.Lock Granularity
A portion of the data controlled by a lock can vary from only a row to the entire database,
as shown in Table 47-1. Several combinations of locks, depending on the lock granularity,
can satisfy a locking requirement.TABLE 47-1 Lock GranularityLock Size Description
Row Lock Locks a single row. This is the smallest lock available. SQL Server does not
lock columns.
Page Lock Locks a page, or 8KB. One or more rows may exist on a single page.
Extent Lock Locks eight pages, or 64KB.
Table Lock Locks the entire table.
Database Lock Locks the entire database. This lock is used primarily during schema
changes.
Key Lock Locks nodes on an index.c47.indd 1066c47.indd 1066 7/31/2012 10:23:30 AM7/31/2012 10:23:30 AM
http://www.it-ebooks.info