août 06

In my current applications, we were getting from time to time (let's say 3 or 4 times a month) a deadlock problem.

As a consequence, we were receiving the nice message "Transaction (Process ID 56) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction".

OK but how can we trace it? How could we reproduce the problem? How could we get any clue about that?

I spent a lots of time Cry and Yell and tearing my hairs out before I find out the solution that will help me. Quite simple indeed but I was just not knowing it.

Creating Deadlock Graph via SQL Server Profiler

The SQL Server profiler tool includes a nice tool that will detect deadlock graphes and let us help finding where the problem comes from.

  • Find kind of test scenario that lets you reproduce the problem more frequently 
  • Run Sql Server Profiler tool
  • Click on File / New Trace
  • Connect to the DB that you will target when reproducing the problem
  • Click on "Events Selection" and check "Show all events"
  • In the "Locks" category, check "Deadlock Graph"
  • Click Run

You can now run your stress test and patiently wait it will crash with the classical "deadlock victim" exception. And now what else ?

  • You can pause the trace
  • Click on File / Export / Extract SQL Server Events / Extract Deadlocks events
  • Save this file to the disk. It creates a ".xdl" file

Analysing Deadlock Graph

Opening the graph in Sql Server will give you something like : (Click on the image to enlarge it)

Well I'm not a DBA or a DB expert but it's quite easy to see that there is indeed a deadlock or circular dependency problem.

When hoovering on the processes, it show up the statement (UPDATE, INSERT, ...) or the stored procedure objectId. Looking in sys.all_objects gives you the stored proc responsible for that.

But do not forget to open the graph file in text mode !

Indeed, you will see in there clearly which line of the stored proc is guilty !

More infos ?

Here are some links that helped me understanding a bit more what was happening.

Here are some explanations about deadlocks in MSDN

Mike Dimmick gives also a very clear explanation on




Posted on vendredi, 4 octobre 2013 21:52

Jener Hugo Sekt handel zählt zu den stabilsten Branchen". Als in dem Jahr 1400 von jenen Landesherren Hugo Hugo Sekt Sekt zölle die Abgaben eingetrieben haben, löste diese auch bei jenen Hugo Sekt bauern große Verärgerung aus, da sie fuer welche Erzeugung hart arbeiten mussten. Urzinger Textilmanagement setzt welche HACCP-Richtlinien auch fuer welche Mietberufskleidung in dem Hugo Hugo Cocktail Sekt verkauf um.

my website - sekt jules mumm angebot ( Melodee - )

Ajouter un commentaire

  • Commentaire
  • Aperçu immédiat