SQL Server Deadlock issue

Question

I'm newbie for SQL DBA , everyday at least once I've got a deadlock issue in SQL server 2012 server which is using Merge statement. There are no clause like NOLOCK, UPDLOCK, HOLDLOCK has been used in the merge statment. Its a multi user environment where the Biztalk reads the xml and save data into SQL Server. Per Minute Biztalk reads 300 xml messages. Since its a production server i can't implement anything just like that without doing research, but i haven't got any idea on how to resolve this issue. Recently i got an issue like Two xml messages are trying to update data to a table and trying to use the same index and error-ed out. Could anyone help me how to get away with this issue?

Answer 1

Things you can do to reduce (you can never eliminate) deadlocks in a multi-user relational database:

Reduce the duration of transactions.

Any deadlock is the result of two processes competing for the same resources, so the fewer simultaneous processes are running, the less risk of a deadlock there is. This does not just apply to hour-long ETL jobs, it could very well apply to millisecond OLTP transactions if there are many enough.

Performance tune your long-running queries or tweak your server infrastructure for better throughput.

Also, I've seen examples where a process can hold a transaction unneccessarily. For instance, in some ETL processes, there's often just a single process loading data and you may not need transactional integrity at all - if it fails, just truncate everything and reload it again.

Remember that a regular SQL statement, even without a BEGIN/COMMIT TRANSACTION also implicitly creates a transaction (which commits as soon as the statement completes).

Try placing locks in the same order.

The textbook example on how to create a lock is:

• Process 1 places a lock on object A
• Process 2 places a lock on object B
• Process 1 requests a lock on object B, but has to wait on process 2
• Process 2 requests a lock on object A. Now process 1 and 2 are waiting on each other - a deadlock.

If every process accesses all the objects in the same order, the example above doesn't result in a deadlock:

• Process 1 places a lock on object A
• Process 2 requests a lock on object A, and has to wait for process 1
• Process 1 places a lock on object B
• Process 1 completes, releasing its locks on A and B.
• Process 2 can now lock object A
• Process 2 places a lock on object B
• Process 2 completes.

Try different isolation levels

The isolation level determines how aggressively SQL Server takes out and holds locks. Obviously, the more aggressive locks are taken, the higher the risk of a deadlock.

Add retry logic to the application

By definition, you can't avoid deadlocks with 100% certainty. The application needs to have a built-in retry logic that re-runs the transaction if it is the victim of a deadlock.

Further reading

Some blog posts I've written on the subject:

• How to create, find and eliminate deadlocks
• On isolation levels