0

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?

  • What isolation level you are using? – SqlWorldWide May 15 '17 at 23:11
  • When you get deadlocks, sometimes it's just because things are taking too long, so if you go on an exercise to improve performance, you can sometimes fix your deadlocks and improve performance. Having said that, I avoid merge because I've had lots of problems with it, especially if you use when not matched. If I was in your position I would firstly get a DEV environment sorted, then I would look for obvious thing like indexes, then I would actually rewrite the merge as individual INSERT, UPDATE (and delete?) statements. How many record are in the tables being used? – Nick.McDermaid May 15 '17 at 23:14
  • 1
    Maybe you could show things like the table structure (including indexes), the MERGE statement, the deadlock graph... – Aaron Bertrand May 15 '17 at 23:52
  • Its a default isolation level. Number of records is 20Millions. – aprasy May 17 '17 at 16:01
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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.