Use these techniques to prevent or avoid deadlocks
They're your database transactions. You worry about them. You try to structure them correctly from the start so that you can depend on them to execute properly and in the order you intend. But sooner or later, you see the alert error 1205: Your transaction was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction.
"How can this be?" you ask. "They always behaved so well by themselves. They must have been running with a contentious crowd again."
Compared with earlier versions, SQL Server 7.0 offers a dramatically enhanced approach to transaction contention management. Most significant, row-level locking is now the default for all types of transactions. SQL Server 6.5 had an Insert Row Locking (IRL) feature, which was an optional attribute on a table-by-table basis that governed the behavior of concurrent inserts. But in reality, IRL was still a page-locking system; it simply allowed multiple inserts into a page at the same time. True row-level locking in SQL Server 7.0 is a necessary advance that positions SQL Server as a viable relational database management system (RDBMS) for enterprise applications with high volumes of data and high degrees of concurrent use.
SQL Server aficionados have long anticipated these locking mechanism improvements. Now you can control locking of your database resources at the granularity you want—the row level. By using SQL Server 7.0 and good application development techniques, you might feel adequately armed to avoid deadlocks. But despite your best efforts and the locking mechanism enhancements in SQL Server 7.0, deadlocks can occur. Here's an example you're probably familiar with. Process A updates table Orders, then attempts an update on table Customers. Process B, seemingly in defiance, updates table Customers then circles back to Orders. SQL Server takes the locks, the processes hang, and a deadlock occurs. To avoid this situation, you try not to let Process B happen. You go through great pains to ensure that any set of updates to Orders and Customers execute just as Process A would. Where possible, you write code as a sharable component, either a T-SQL stored procedure, a 3GL function packaged as a COM component in a DLL, or any other time-honored method for sharing code. "This is easy," you think. "I could avoid deadlocks in my sleep!" Then one night, as you sleep, your pager calls again. When you check your system, you see error 1205 again. Why? Try as you might, the traditional approach of ensuring correctly ordered transactions, though necessary, simply won't prevent SQL Server 7.0 from deadlocking on resources, sometimes within locking of a single row.
To illustrate this point, you can follow these steps to create an example. First, use the script in Listing 1 to create Indexed_Table. Note that the table has a nonclustered index and a data field named Non_Indexed_Field, which isn't contained in any index.
Next, populate the new table with the data in Listing 2. You can use this simple table to create a surprising deadlock scenario. Let's say that two processes, Process A and Process B, access this table at the same time. Having reformed its contentious behavior since the previous example, each process carefully avoids updating multiple rows in reverse order.
Process A, which needs to update only one row, submits the following statement:
UPDATE Indexed_Table
SET Non_Clustered_Index_Field = 'NewData2'
WHERE Id = 2
Process B, at roughly the same time, submits a query against the table:
SELECT Non_Indexed_Field
FROM Indexed_Table
WHERE Non_Clustered_Index_Field = 'Data2'
Although each transaction in this example will contend for access to the same row, the transactions don't fit into the classic deadlock scenario I described above. Most significant, each transaction affects only one row. Thus, a scenario in which each process holds a lock that the other needs doesn't seem possible. This example seems to be a simple blocking situation with one of two possible results: Either the select statement from Process B will find the appropriate row before the update statement from Process A gets to the row, or the select statement from Process B will come out empty-handed.
However, these two processes can deadlock because they both access the nonclustered index, albeit for different reasons. A nonclustered index requires storage space separate from the table (unlike a clustered index, which orders the storage of the table itself). Thus, the index is a different set of resources, governed by separate locks from the ones the table uses.