5 Easy Ways to Prevent Deadlocks

Posted In DigitalOlympus Feature Articles, DigitalOlympus News - By Josh Cole On Tuesday, August 6th, 2013 With 0 Comments

Ah, Deadlocks. The Catch 22 of SQL servers. Deadlocks can wreak havoc in your office by destroying an application’s performance and seriously affecting your transaction throughput. The best way to deal with deadlocks is to prevent them, to stop them before DBA and developers start pointing fingers because an app is slow as a snail.

 Deadlocks are a serious problem (they are not called “fatal embrace” for nothing) and they need  to be prevented.  And although I recommend employing a SQL Server Consultant specialized in identifying and dealing with these issues, there are some simple tips that can help you prevent deadlocks.

How to Avoid Deadlocks?

Even if deadlocks cannot be completely avoided, this blog post will provide a few tips that will help you prevent them. Keep in mind that the simplest way to avoid deadlocks starts with the design of the database itself, which means a lot depends on your database architect; but following these recommendations should help you a long way:

  1. I will have order!  It’s crucial to access database items in the same order every time. Microsoft provides a great example about this: “If two concurrent transactions obtain a lock on the Supplier table and then on the Part table, one transaction is blocked on the Supplier table until the other transaction is completed. After the first transaction commits or rolls back, the second continues, and a deadlock does not occur. Using stored procedures for all data modifications can standardize the order of accessing objects.”

  2. Keep it short. It’s important to keep transactions as short as possible. Deadlocks tend to occur when several long-running transaction execute simultaneously. By keeping them short, and making sure you are not performing the same reads repeatedly, you will reduce network roundtrips during transactions.

  3. Careful about user interaction. Make sure to not allow user interaction (input data) during transactions. Instead, Senior Database Administrator Basit A. Masood-Al-Farooq recommends in his article called Tips to Avoid Deadlocks to try updating all the data before the transaction starts.

  4. Use the NOLOCK. One of the most popular ways to stop deadlocks is using the NOLOCK query hint. Even though Jeremiah Peschka states in The Difficulty with Deadlocks that, the “NOLOCK doesn’t get rid of all locks, just the ones that make your queries return the right results,” the NOLOCK is still a great option to prevent locking.

  5. The secret of the isolation level. You need to test if a transaction can run at a lower isolation level to avoid deadlocks. Implementing this will enable a transaction to access data (previously read by a different transaction) without having to wait for the first one to complete.

The best way to deal with deadlocks is to prevent them, especially by making the right choices during the app’s development. But if you can’t avoid a deadlock on time, you can still eliminate it by implementing Snapshot Isolation. The problem with most of these manual options to eliminate deadlocks is that they are demanding and time-consuming.

Share your thoughts! How would you prevent or eliminate deadlocks in your server? Post your comments below.

About -

Leave a comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>