Avoiding and/or Minimizing Deadlocks

In relational database systems (RDBMS), a deadlock is a situation where two concurrent transactions cannot make progress because each one is waiting for the other to release the lock. In Part 1 of this series, we we established what Object Locking is in Relational Databases, the different types of locks, and deadlocking. Then, in Part 2, we compared the pros and cons of Pessimistic and Optimistic locking. In this installment, we'll be exploring a few causes of deadlocks, as well as strategies for avoiding, or at least, minimizing them.

Inefficient Queries

Deadlocks are unavoidable to some degree, but their infrequent occurrence does not automatically spell disaster, as long as one of the two transactions ends in a timely fashion. As it turns out, one of the most common sources of blocking issues are long and inefficient SQL statements that cause the database to "hang" while they run their course. These may be remedied in two steps:

• Optimize poorly performing SQL statements so locks are released in the shortest time possible.
• Identify whether the locks can be released before any long-running SQL statements are executed within the same session.

For example, if locks are acquired due to a DELETE statement being executed and is immediately followed by a SELECT statement that performs a complete table scan, you should ascertain whether it may possible to execute a COMMIT statement between them. This should help the locks release earlier.

Nested Transactions

Another frequent cause of blocking issues are sleeping sessions that have lost track of the nesting level of the transaction. For example, if an application cancels an SQL statement or is timed out but doesn't issue a COMMIT or ROLLBACK statement, then resources could remain locked indefinitely. Some ways to deal with this issue include:

• Following any application error, submit an IF@@TRANCOUNT > 0 ROLLBACK TRAN statement within the application's error handler.
• Include the SET XACT_ABORT ON statement in any stored procedures that start transactions - especially if they aren't cleaning up after an error. By doing so, should a run-time error occur, any open transactions will be aborted and control returned to the client.
• If connection pooling is employed by an application that opens the connection and runs a few queries before returning the connection to the pool, then you may want to consider temporarily disabling connection pooling. By doing so, the DB Server connection is physically logged out, resulting in the server rolling back any open transactions.

Fetching Partial Results

A lesser known source of deadlocks are applications that don't fetch all result rows in one go. This is a problem because, when a query has been sent to the server, applications must be able to fetch all result rows to completion. If this doesn't happen, locks can be kept on tables, which results in blocking for other users. Therefore, try to code your applications so that they fetch all of the rows they need rather than spread it out over several iterations.

Conclusion

Today's blog listed a few causes of deadlocks, as well as strategies for avoiding them and dealing with deadlocks when they do occur. Next week, we'll be moving on to an entirely new subject.

Rob Gravelle resides in Ottawa, Canada, and has been an IT Guru for over 20 years. In that time, Rob has built systems for intelligence-related organizations such as Canada Border Services and various commercial businesses. In his spare time, Rob has become an accomplished music artist with several CDs and digital releases to his credit.