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  and  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

• Analysing Deadlocks with Sql Server profiler
• Lock Modes
• Key-Range locking
• Detecting and Ending Deadlocks

Mike Dimmick gives also a very clear explanation on http://mikedimmick.blogspot.com/2004/03/selectupdate-problem-or-why-updlock.html

Here is a little summary of the sessions I have been able to attend on this day:

Opening Keynote (By Rafal Lukawiecki)

Definitely an interesting speaker. Pity I was not ideally seated to hear and see good, and that I had not been able to see him another time during these days. He has been presenting a bit of past and future for development

He has been presenting a comparion between Imperative and Declarative programming languages and the evolution of application with Service Brokers and composite applications. He has also spoken a bit of "OSLO", the future - in his view - for developping software as a model, using Declarative Model Composition in order to achieve a seamless runtime hosting and deployment.

 Session 1 : WCF & WF - Integrating two key technologies of .NET 3.5 (By Ingo Rammer)

Interesting session ! Above the fact I like this speaker, it continues the talk his colleage Christian Weyer had last year. In 2007, Weyer has presented the different problems - and immaturity of the products. This year, Ingo Rammer emphasizes on the evolutions and the different scenaris for integrating the technologies.

• Recall of the WCF's ABC (Address, Binding, Contract)
• Presentation of 3 scenaris
  • Regular Service
  • Durable Service
  • Workflow Service
• Explanation of how a duplex contract work and what are the limitation of the Duplex Channels (life duration of the channel : life duration of the application itself)
• Explanation of how to put in place Durable Services to achieve statefull (and sherver-shutdown resistant) services using the
  • DurableServiceAttribute
  • DurableOperationAttribute
• Explanation of the durable operation services persistance bahvior
  • when it starts
  • when it stops
  • how to configure it using the providers in the config file
  • how to configure the database using the scripts provided by the framework under c:\windows\microsoft.net\framework\v3.5\SQL\en-US
• Presentation of the same scenario, but integrating a workflow, explaning the two ways of working : "workflow first" or "contract (interface) first"
• Explanation of the two new "Send" and "Receive" activities and of the "ListenActivity"
• Shwoing some debugging possibilities inside of a workflow

This session was interesting but a bit hard to follow as I don't have yet sufficient knowledge in WCF and WF.

Inspiration Session 1 : ASP.NET Futures (By Matt Gibbs)

No lunch time this year, but instead an extra session that I don't regret I went.

Matt Gibbs has focused this presentation on the Dynamic Data Framework (which is also a good start point for the ASP.NET MVC framework).

• Demo of how to create a Data-Driven Web Application using Dynamic Data
  • How to create a model using LINQ to SQL
  • How to configure the application (via global.asax to register the DB context
  • Demo of the generated website with the different tables and the following capabilities:
    • Sorting and Filtering
    • Adding, Updating and Deleting
    • RelationShips being understood
    • Validation done, based on the model (required or not, type, length)
    • Ajax integration
• Explanation of how to customize the result
  • Use of a partial class of the model to use some "MetaDataTypeAttribute" to add extra validation for example
  • Create a new "Fied Template" to show some special controls for some types or properties and make the link to a partial class using a UIHintAttribute
  • Create a new "Custom Page" for a specific table name
  • How to use Routing for special navigation / routing instructions 

30mn to present quite in-deph the framework... Quite intense. Hopefully I had already seen part of this typical MS presentation on many other blogs (like among others http://www.loicbar.com/)

Session 2 : Building Applications with Silverlight 2.0 (By Nikhil Kothari)

• Quick presentation of some features
  • Cryptography API
  • Threading support
  • Sandboxed execution
  • 4Mo size client download, including multi support browser
• and information about the Silverlight 2 SDK including
  • the new controls (like FormsControl, DataGrid)
  • Syndication
  • XML Serialization
  • Will be shipped in Beta2 (with Go-Live licence) in Q2 2008
  • Expression Suite and Visual Studio will be updated at that time to allow a nice integration experience
• Explanation that a Silverlight app is hosted in a website, via a compiled ".xar" file
• Demo of the XAML code-behind files and of the initialization of the application that may (re)load saved properties from client disk
• Demo of how to save and load client specific data using the System.IO.IsolatedStorage namespace
• Demo of how to manipulate the Browser (HtmlPage.Windows.Navigate) for redirectind

Session 3 : Advanced debugging using Visual Studio (By Ingo Rammer)

This session is following the one he has done in 2007 (Hardcore Production Debugging - PPT) but focusing on the tools available in Visual Studio.

• Presentation of the different capabilities of the BreakPoint (conditional or not, hit count, based on a method name, wherever it is, ...)
• Presentation of the Debugger class, more specifically the Debugger.Break() and Debugger.IsAttached
• Presentation of the Trace Point (Breakpoint using the option - when hit) to use macros to dump the stack trace for instance
• Presentation of the Command Window with the aliases
• Presentation of the Watches
• Presentation of some debugging-related attributes like DebuggerDisplay, DebuggerTypeProxy and DialogDebuggerVisualizer
  • Note that for the two lasts, the target object must be serializable
  • The attributes can be set at object level, or at assembly level
• Presentation of the Immediate Windows
• Presentation of the Object Test Bench, from a class diagram
• Recall of the rules to have a good "debuggability" in a project like
  • Avoiding non-essentials exceptions (by using TryParse, ...)
  • Catch exception when you handle it
  • Throw essential exceptions
    • Use throw; and not throw ex;
• Quick presentation of MDbg to exttract some information when an application crashes (can be used in production)

Definitely a passionated (and passionating speaker). Thanks Ingo

Session 4 : Sql Server 2008 - What's new for Developpers (By Dandy Wein)

The presentation has focused to the T-SQL enhancements

• Date and Time types and DateTime2 type with the new precision
  • from 1/1/1 to 31/12/9999 with a precision to 100 nanoseconds
  • DateTimeOffset type and SYSDATETIME function
• Localization capabilities using the SET DATEFORMAT and SET LANGUAGE prerogatives
• Demo of the new Table-Valued parameters to avoid doing too many queries when dealing with tables with 1..n relationship
• Being able to declare and initialize a variable on the same line : DECLARE @myInt INT = 10
• Increment operators : SET @myInt += 1
• Row constructors : being able to insert several rows at the same time
• INSERT INTO MyTable(Col1, Col2)VALUES(1, "Val 1"), (2, "Val 2"), (3, "Val 3"), (4, "Val 4")
• The COMPUTE BY statements are now deprecated and will be replaced by some GROUPING SETS instructions (WITH CUBE and WITH ROLLUP remain)
• A new MERGE instruction has been added to facilitate merge vetween tables
• HIERARCHY and HIERARCHYID have been added to facilitate the recursive queries
• New spatial data and functions (to see for example if a certain point is present in a given region, ...)
• A new FILESTREAM object : when storing some files in a table, we'll know declare them as "VARBINARY(MAX) FILESTREAM" to improve performance and avoid DB fragmentation. Note also that the DB Server will no longer limitate the size of files (it was 2Go before)

Session 5 : Ajax Patterns (By Nikhil Kothari)

The goal of the presentation was to show all the challenges we were facing when working in a AJAX application and how we could resolve them. He has shown among all :

• How to call a web service from javascript using a ScriptServiceAttribute, after having registered it in the ScriptManager
• The difference (in performance) between an UpdatePanel and calling a web service directly (and so using JSON)
• How to create an AJAX control behavior
• How to provide good navigation and bokkmarkability by updating the url and adding steps to the history using the addHistoryState
• How to provide Indexability on a page by providing static data
• Demo on the integration of a Silverlight app in an ASP.NET with AJAX, and showing how to call a OpenFileDialog from Silverlight

Quite an interesting day !

Hello,

I went today to the launch day of the Tech Days 2008 in Ghent, Belgium. I will try to do a little résumé (as for the two next days) of the conference I have been able to see. Last year I was very excited after the first day with the great conferences of Jeff Prosise which will remain one of my top speakers.

This year, this first day, was less technical and more "commercial". Quite normal in fact for a launch day as it was announcing the launch of Windows Server 2008, Visual Studio 2008 and Sql Server 2008. In fact

• Visual Studio 2008 has been launched (and in RTM) since October 2007
• Sql Server 2008 has been delayed. A new CTP is available in February 2008 but the RTM version is planned for Q3 2008

Anyway, this first day was interesting.

Opening keynote (by Bill Hilf)

Many other guest came for little demos. Here Bill Hilf has spoken a bit of the three products, emphasizing on "Dynamic IT" and on the vitualization. Here are the key facts he has presented:

• Windows Server 2008
  • Since Vista, Microsoft has rewritten all the TCP / IP layer
  • They have tried to limit the footprint size (meaning more or less the installation size and the component installed) in order to limit the patch quantity and sizes (and so to maximize the uptime) and also to limit the possible doors for hackers
    • If you want a web server, you may not want to install the UI
  • Includes BitLocker to encrypt hard disks
  • Delivered with IIS 7 that will support natively PHP
  • "Hyper-V" : the virtualization platform of Windows Server 2008
• Visual Studio 2008
  • Improvement of the compilation time by 30%
  • Presentation of some new features as the split view, javascript debugging and Expression Blend
• Sql Server 2008
  • The "Resource Governor" will allow to decide which percentage of the CPU will be attributed to which resource pool
  • It will be possible to encrypt a DB on the fly (and not only on a table level as now)
  • Will allow you to test a database agains some defined rules  

Session 1 : Visual Studio 2008 Overview (by Peter Himschoot)

The president of the VISUG has shown us some the main improvement of VS 2008. There was nothing I was not knowing already but I was curious to see how he was presentings this, just to get inspired :-)

What I can note ?

• That the code of the demos he has done will be downloadable on his blog
• He has presented the new Orcas build capabilities (build queuing and build triggering)
• The Unit Testing will be available in the Professional Editions of VS

Session 2 : Next Generation Web Application with Visual Studio 2008 (by Gil Cleeren)

The webmaster of the VISUG has focused this time on the web part of VS 2008. Again I was knwoing many things. What I will note anyway ?

• The new CSS windows ("CSS Style Manager", "Apply Styles Window" and "CSS Explorer") to ease the CSS manipulation in a web site and detect easilty which style is responsible of what effect on a control
• The Javascript debugging and intellisense capabilities
• How to use WCF with AJAX
• Some controls of the "ASP.NET Ajax Control Toolkit" as the PopUp control and the Watermark control
• The Visual Studio's WebTests will allow testing AJAX pages

Session 3 : Pervasive Business Intelligence with Sql Server 2008 (by Frederik Vandeputte)

This was an interesting session ! Indeed I have a very few knowledge of BI, Data Mining, Data Warehouse, Reporting using Sql Server. And this session allowed me to enter a bit in this (of course, I still have a lot to do !)

• He has shown among other things the CDC (Change Data Capture) possibilities : how it works (based on the transaction log files), how we can interrogate the DB to see the changes, ...
• The profiling capabilities within the "Data Profile View" that will allow to extract some statistics on a table like the min or max value, the number of null values, the distribution of the values, ...
• Presentation of the new "Dundas" charts that are now included with the Reporting Services
• The datamining tools in Excel (that have appeared in the Sql Server 2005 SP2) have been improved
• Being able to analyse the data of a table to see the influence of datas on a specific values

Finally the vice-president of the "SQL User Group" has given us a rendez-vous for more session in its user group !

See you tomorrow !

You may know one of the improvement of Sql Server 2005 was the tight integration with the .NET CLR, giving the ability of creating stored procedures in C#.
We have never used in production environment and it's time to exchange on this subject.
One of my colleague will give a training on this subject

You would like to attend ? Leave a comment !