that can appear with concurrent transactions.
They can cause major problems for a system and can slow and even halt performance.
These problems can be handled in the application,
or SQL Server will deal with them as best it can;
they will be described here only so that you will be aware of them and understand the concepts.
Avoiding and resolving blocking and deadlock issues
is the responsibility of the programmer.
* Blocking occurs when
one transaction is holding a lock on a resource
and a second transaction requires a conflicting lock type on that resource.
The second transaction must wait for the first transaction to release its lock
- in other words, it is blocked by the first transaction.
* Blocking usually occurs when
a transaction holds a lock for an extended period,
causing a chain of blocked transactions that are waiting for other transactions to finish
so that they can obtain their required locks -
a condition referred to as chain blocking.
Figure 19-1 shows an example of chain blocking.
Gambar:
Example of Chain Blocking
* A deadlock differs from a blocked transaction
in that a deadlock involves two blocked transactions waiting for each other.
For example,
assume that one transaction is holding an exclusive lock on Table_1
and a second transaction is holding an exclusive lock on Table_2.
Before either exclusive lock is released,
the first transaction requires a lock on Table_2
and the second transaction requires a lock on Table_1.
Now each transaction is waiting for the other to release its exclusive lock,
yet neither transaction will release its exclusive lock
until a commit or rollback occurs to complete the transaction.
Neither transaction can be completed
because it requires a lock held by the other transaction in order to continue—deadlock!
Figure 19-2 illustrates this scenario.
When a deadlock occurs, SQL Server will terminate one of the transactions,
and that transaction will have to be run again.
Microsoft SQL Server 2000 Administrator's Companion eBook