A Serious Problem to Avoid: Deadlocks
Imagine that your database application has two users: Paul and Mary.
Paul starts a transaction and modifies some attributes of the Acme Ltd. customer. Later, inside the same transaction, Paul tries to modify this customer's payments. However, Paul cannot modify these payments because Mary holds an exclusive lock on these payment records. Paul must wait for these records to be unlocked before completing the transaction.
Mary is modifying customers' payments, and that's why this information is locked. Inside the same transaction, Mary tries to modify some data about the Acme Ltd. customer. At this moment, Paul, who modified this record just a few minutes ago, locks this information.
Mary cannot update this information because Paul is holding an exclusive lock on it, so Mary must wait for this resource to be unlocked before proceeding with her transaction. However, Paul cannot continue with his transaction because he's waiting for Mary to unlock the information he needs to update.
This situation of mutual blockings is called deadlock. If SQL Server detects this situation, it decides which process has a bigger execution cost, and selects this process as a winner. After the winner is selected, SQL Server notifies the other processes waiting in this deadlock situation with error 1205, telling them that they have been selected as victims in a deadlock situation.
If the processes involved in a deadlock situation are blocking one another in a circular reference, SQL Server selects which process can be selected to break the deadlock with the least overall cost, and notifies this process with error 1205.
Note
You can propose your specific session as a potential deadlock victim by using the statement SET DEADLOCK_PRIORITY LOW.
Two processes can create a deadlock situation when they access resources in opposite orders and try to convert a shared lock into an exclusive lock at the same time. Figure 13.8 illustrates this scenario:
Figure 13.8. A typical deadlock situation.
Connection A starts a transaction and reads the UnitPrice column from the Product 37. This connection uses the HOLDLOCK locking hint to maintain the shared lock on the row corresponding to Product 37.
Connection A tries to update the Order Details table to reset the unit price of Product 37 to the value stored in the Products table. To execute this statement, Connection A needs an exclusive lock on the affected rows, but this exclusive lock must wait because Connection B holds a shared lock on the same rows.
Connection B tries to update Product 37 in the Products table with the average unit price retrieved from the Order Details table. Connection B requests an exclusive lock on Product 37, but this lock must wait because Connection A holds a shared lock on it.
SQL Server detects this deadlock situation, selects Connection B as victim of this situation, and sends message 1205 to Connection B. Resources locked by Connection B are unlocked.
After Connection B has been selected as a victim and its locks have been released, Connection A can continue its operation.
Another typical case is when two transactions want to convert an existing shared lock on a common locked resource into an exclusive lock. To prevent this situation, you should use the UPDLOCK locking hint in transactions in which you read data with intentions of updating it later in the same transaction.
Caution
When a transaction is selected as a victim in a deadlock situation, the process is cancelled and changes applied are rolled back. However, the calling application could usually resend the transaction and, hopefully, the previous locks have disappeared.
Avoiding deadlock is not always possible; however, you can help to reduce deadlocks by following these guidelines:
Keep transactions as short as possible.
Avoid user interaction inside transactions. In other words, start a transaction only when required and release it as soon as possible.
Always access resources in the same order and check for potential circular references.
Use the READ COMMITTED isolation level if possible, because it produces fewer locks than higher isolation levels. Try to avoid SERIALIZABLE as much as possible.
If an application uses several connections, bind them to share the same locking space. You can execute the stored procedure sp_bindsession to keep more than one session in the same transaction.
What's Next?
Transactions and locks are key aspects to provide the adequate concurrency to your database application in a multiuser environment. However, they are restricted, as covered in this chapter, to a single-server operation. The following two chapters focus on the multiserver environment from two different perspectives:
Chapter 14 shows how to transfer data to and from SQL Server databases stored in the same or different servers. Data Transformation Services (DTS) is a feature-rich application which, integrated in SQL Server or as a standalone subsystem, transfers data between hetero-geneous systems, including all the required transformations.
Chapter 15 discusses the multiserver environment and the implications of the distributed transactions. In Chapter 15, you learn how to use linked servers to maintain data in multiple servers, as an alternative to DTS and Replication.