Databases Reference
In-Depth Information
FOR EXAMPLE
Lower Level—Better Performance?
SQL Server 2005 defaults to the read committed transaction isolation level,
but is that always the best choice? The fact that SQL Server offers choices
for setting the transaction isolation level in itself tells you that this is not
always the case. Not only can you set the transaction isolation level when
you open a command line connection, like a query window, but you can
also specify the transaction isolation level when establishing a connection
from a database application. But how do you choose?
Choosing the right transaction isolation level depends on several factors.
The time you take to make the right choices can pay off, though, in better
data integrity and possibly in better performance. The data integrity is easy
to see. The higher the isolation level, the better protected the transaction
from interference from other transactions. Performance issues might not be
as obvious.
In the simplest terms, the less restrictive the isolation level, the less likely
that your application might need to wait for resources. Does this mean that
a lower level always means better performance, though? Not necessarily. Per-
formance includes both database and application performance. Any perfor-
mance gains from lowering the transaction isolation level could be lost if the
application has to do additional verification testing or has to correct errors
that it has detected.
T1 accesses the TextBookInventory, Customer, OrderHead and OrderTail
tables, in that order. T2 accesses OrderHead, OrderTail, and then Customer. Each
transaction acquires a lock on each table it accesses. In this situation, T1 is hold-
ing Customer open and trying to access OrderHead. T2 is holding OrderHead
and OrderTail open and trying to access Customer. Neither one can continue
because its access is blocked by the other. This situation will continue until one
of the transactions times out.
10.3.3 Recognizing, Clearing, and Preventing Deadlocks
Blocked transactions and deadlocks are perhaps your biggest potential concerns
in a transaction processing environment. These conditions can cause perfor-
mance to grind to a halt and lead to transaction processing errors and time-outs,
resulting in critical data updates not being made.
The first thing you have to do is accept that in almost any transaction pro-
cessing environment, intermittent occurrences of blocked transactions are going
to be a fact of life. Your database application must be able to account for these
