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 http://mikedimmick.blogspot.com/2004/03/selectupdate-problem-or-why-updlock.html

Tags:
août 04

You may alreay know, but a quick post to tell that Ayende Rahien will be in Brussels tomorrow (tuesday 5th of August) at 21h, in place Saint-Gery to share some beers Smile

You must know him, he is the father (or active contributor) to projects like RhinoMocks and Castle.

I'm not sure I will be able to get there tomorrow, but Steve Degosserie should go. Follow his blog to get some news !

août 02

Hello all !

As I told in my last post, I left a month to go to Perú. After this very nice trip, it took me some time to get back in my life and in my work. But here I am now, ready to continue ! Meaning also that I will soon blog other news. But for now, let's share some pics about my trip !

And see you soon for more technical news !

Click on each picture to enlarge it !

     

The flag of Cusco on the left, and the Peruvian one on the right 

A demonstration of marinera Norteña in the center of Cusco The internal courtyard of an artisan's workshop. Where inca style is mixed to spanish style
     
 View of one temple in the archeological site of Pisaq The great walls of the city of Sacsayhuaman  Celebration of the "Inti Raimy", the celebration of the winter solstice. When the Inca is blessing the mummies 
     
 Still the "Inti Raimy" in the "Plaza de Armas" de Cusco. The Inca is blessing the crowd. Ecstatic spectators !  Una llama Musica de  "El Condor Pasa" in the "Magical Fountain Park" of Lima