Database.ca SQL Server Blog

My Blog

Monday, August 12 2013

Keywords: SQL Server deadlocks, Oracle deadlocks, RDBMS deadlocks, SQL error 3930, SQL error number 1205, deadlock causes, deadlock sample example

Do not be afraid of deadlocks. Handle them adequately at the middle-tier instead.

In this article I will clarify the misconceptions regarding deadlocks; why it is so important that a DBMS raise such errors; and options for handling deadlocks. Although in an ideal world deadlocks would have no place, we are not there yet. I obviously do not condone or encourage deliberate causing of deadlocks. But accidental ones are sure to occur every now and then...although more often in some servers than others. We need to understand though that we are truly lucky that modern DBMSs are efficient and quick enough to detect these otherwise ever-lasting locks and cut them loose immediately.
 
Treat Deadlocks like taxes: Although painful they are actually for the best…they cannot be avoided, but you need to learn how to minimize them.
 
Locks are the mechanism that DBMSs created a few decades ago to keep your data’s integrity 100% safe. Thanks to locks our data stays safe and secure even under heavy concurrent use. Without locks…no one would use SQL Server or Oracle. They would be a joke of no use to anyone.
 
A deadlock is when two processes, A & B, are each waiting for the other party to commit the transaction first…causing a vicious circle. In absence of deadlocks two given affected processes would go on forever, and never ever finish. DMBSs detect and raise deadlock events instantaneously, thus saving us and our database users lots of pain and agony dealing with never ending locks.
 
Deadlocks differ greatly from ordinary locks we encounter in OLTPs during normal operations. Locks have the tendency to create delays and sluggishness in queries and all other operations…and are the usual suspects in timeouts at the middle-tier level. A seasoned senior DBA, time and other duties allowing, would be able to reach into his bag of arsenal, and come up with a variety of ways to detect and fix these contention areas.
 
Any robust enterprise caliber middle-tier application MUST be able intercept deadlock errors thrown by databases, and graciously re-queue the procedure that was rolled back...Re-submitted jobs almost always execute successfully …failing to re-submit the job is a major mistake and would demote an application from its enterprise status.
 
Here is another important notion that developers and QA staff should know: what causes a deadlock does not cause any delays, there is absolutely no long periods of waits before a deadlock error is thrown…
 
No! The application was not frozen for 30 minutes before the deadlock was raised! As soon as DMBS detects a vicious circle, it would select at its discretion a deadlock victim, whose transaction gets completely rolled back.
 
The detection of a deadlock is so very simple for a DBMS: they keep at ALL TIMES a list of ALL lock created in the database. They know exactly which columns and rows and tables are locked and by who. They detect and raise deadlocks in flash of a second: a deadlock cannot directly cause timeouts in the middle tier. If deadlocks create bugs in the application it is a sign that they are not handled properly by the middle-tier.
 
The word Deadlock is an ugly and scary word indeed: starts with ‘dead’…not an appealing word what so ever, and ends with ‘lock’ which IS a much more serious problem than deadlock to contend with in all DBMSs…interesting enough locks go unnoticed: because no error is logged in the event viewer! Making locks your real foes. And deadlocks your friends. The first is hidden and slows you down 24/7…the latter is obvious, quick and gone under 1 second.
 
Developers and QA staff are indeed scared of this word, and seeing the word ‘deadlock’ in the event viewer is enough to create havoc in the team…and for people to start pin pointing to the DBMS as the main problem…every time a deadlock is encountered the daily SCRUMs are wasted complaining about them ruining everything…instead of actually properly digesting and handling the issue at hand: deadlocks are good and are indeed the victims of their own success. They just need to be handled properly.
 
Having said that…deadlocks should only occur sporadically and not regularly. Based on my experience with DBMSs since mid-1990s…the normal rate of deadlocks I’ve experienced on a server that handles an average of 100 request at a time was between zero to twenty per week. Higher rates at regular intervals could be a sign that two often used procedures are going against each other and need to be corrected, per example:
 
Stored proc A: updates table 1 first, then table 2
 
Stored proc B: updates table 2 first, then table 1
 
If deadlocks are occurring regularly, then they should be detected and then dealt with in various ways:
 

  • 1) By refactoring code at DBMS level: In my example above, we would fix that by making sure the two stored procedures update the two tables 1 and 2 in the same exact order…IF POSSIBLE…sometime doing so is not possible though.
  • 2) Creating indexes is another common way to solve many deadlock problem. Of course you’d do this only when you see proof of its necessity, as we do not want to create indexes on an already over-indexed table…that would create even more locks. But the idea of creating indexes to fix deadlocks is to avoid table scans that are known to create long lasting dreaded locks. So an index could make your stored procs run and finish faster by creating index seek…and with less contention and much faster queries comes less deadlocks.
  • 3) By avoiding direct user interaction in the production database: if you launch SQL Server Management Studio, or SQL Developer, and start meddling with the server’s tables directly…that could sometime cause a deadlock...which is caused by a clash between what you are doing and another back-end process. DBA’s should now allow that anyone connects directly to their production environments using client tools.
  • 4) Make sure your transactions are as short as possible…less code between BEGIN TRAN and COMMIT TRAN the better, and again: index seek helps in this regard as well.
  • 5) Using different isolation levels. Per example snapshot isolation Or READ_COMMITTED_SNAPSHOT are great fixes for deadlocks: however they put much stress on TempDB. On a well optimized database server, where TempDB database is well tuned by the DBA…and the hardware can handle it… READ_COMMITTED_SNAPSHOT setting should be turned on and will do magic.
  • 6) Using bound connections. This way if an application has opened more than one database connection, the different connections would be aware of each other’s locks and cooperate, thus not blocking each other.
  • 7) Avoid using NOLOCK hint...it is often a bad practice and a cheat unless you have extreme good understanding of database lock concepts and are a senior DBA..doing so can put your database's integrity in great danger. SQL Server and Oracle are respectable only because they can guarantee your data's integrity.

 
We are blessed that DBMSs detect and raise deadlocks in a flash of a second. The contrary to this fact would mean that we’d have hung processes every time, and that a DBA would need to be called to literally kill a locking process. Oh, I’ve done that a thousand times!

 

SQL Server deadlocks, Oracle deadlocks, RDBMS deadlocks, SQL error 3930, SQL error number 1205, deadlock causes, deadlock sample example

The different methods of finding deadlocks
 

  • The original way of detecting them was through the use of trace flags. SQL 2005 and above have trace #1222 which provides better detail than 1204 from SQL 2000. If you turn trace #3605 on, it will write the deadlock encountered data out to the SQL Log. You could turn the traces on and off for the duration of a session, or you could have them turned on each time SQL Server service restarts
  • SQL Profiler...(See picture above)
  • Enabling email alerts every time a deadlock occurs. I will write a separate article with an actual working sample of how to do this.
  • Using database Alerts (see picture below)

 

SQL Server deadlocks, Oracle deadlocks, RDBMS deadlocks, SQL error 3930, SQL error number 1205, deadlock causes, deadlock sample example

Let's create a lock, and then turn it into a deadlock
 
1) Create two tables first:

 

SQL Server deadlocks, Oracle deadlocks, RDBMS deadlocks, SQL error 3930, SQL error number 1205, deadlock causes, deadlock sample example

2) Open two different query windows, and type in the following scripts. Note that script 1 and 2 differ slightly: the order of table access is reversed in the 2nd script.
 
3) Highlight the first 2 parts of the first script and run it. That'll insert one record into the database...although the transaction is not yet committed.
 
4) Do the same for the 2nd script. That'll insert another row...transaction not committed yet of course.

 

SQL Server deadlocks, Oracle deadlocks, RDBMS deadlocks, SQL error 3930, SQL error number 1205, deadlock causes, deadlock sample example

5) Now highlight the remaining parts of script #1, and run it (F5).
 
Doing so should show no output, and will basically create an infinite lock. You could wait there for hours and nothing will happen.
 
Congratulations, you have just created a lock! Remember that although locks are keeping your data's integrity intact, they are silent killers for your applications' performance: There are no logs in windows event viewer while locks are being kept, for as long as they may be kept; and there are no errors raised by stored procedures neither. Unless your middle-tier app is configured to time out in seconds or minutes (not all client apps are, SSMS is not) then you will never find out.

 

SQL Server deadlocks, Oracle deadlocks, RDBMS deadlocks, SQL error 3930, SQL error number 1205, deadlock causes, deadlock sample example

6) Now highlight the remaining part of the second script, and F5 it.
 
A message will appear shortly, displaying error number 1205 (SQL Server error message) and the error message regarding a deadlock occurring.

 

SQL Server deadlocks, Oracle deadlocks, RDBMS deadlocks, SQL error 3930, SQL error number 1205, deadlock causes, deadlock sample example

7) Question: was the rollback tran absolutely necessary in the CATCH clause?

 

SQL Server deadlocks, Oracle deadlocks, RDBMS deadlocks, SQL error 3930, SQL error number 1205, deadlock causes, deadlock sample example

The answer to the above question is NO, the DBMS had seen the lack of ROLLBACK in your script, and had rolled it back for you. However it just looks neater if you do put it in your error handling section. I do include them. However it doesn't seem mandatory.

 

SQL Server deadlocks, Oracle deadlocks, RDBMS deadlocks, SQL error 3930, SQL error number 1205, deadlock causes, deadlock sample example

8) Question: What if we detect the deadlock in stored procedure's CATCH clause and re-execute the failed code ourselves in the DB tier?

 

SQL Server deadlocks, Oracle deadlocks, RDBMS deadlocks, SQL error 3930, SQL error number 1205, deadlock causes, deadlock sample example

DBMSs are too quick. Chances are your retry would look like a vicious circle to the optimizer engine and would be tagged and stopped by DBMS and depending on your SQL Server vesion you'd get different results. In SQL Server 2008 you'd get the above message (SQL Error #3930), in SQL Server 2012 you could cause the first script to become the victim of the deadlock...even though it was the script that started first.

 

Written by Ramin Haghighat