Tip #53 – Locks, Blocks, Deadlocks and Timeouts | What are they

Recently I’ve been asked to do a code review of a vertical solution. Since these are always interesting projects I cleared my schedule and planned an interview to see what it was about. I’m curious by nature.

During the interview I was told that some of their customers were complaining about deadlocks. This was going on for a while now and they did their best to look at the problem and even created a chart with deadlocks per day.

This was honestly not that much so I figured something else must be wrong. After the intake we spent some time looking at one customers system and I started up some measurements in SQL Server Profiler. Nothing fancy, just some lock events.

This showed that not deadlocks, but locktimeouts are causing the problem. From a user perspective they almost look the same.

Here is the deadlock message:

And the locked message:

But the source of the problem can be entirely different.

Simulate a (b)lock timeout

This is realatively easy. Simply create one codeunit that reads a customer record and hold the transaction with a confirm. (Never do this in real life kids)

Now simply run this codeunit twice and the timeout message will appear.

Simulate a deadlock

This is a little harder. We need two codeunits this time.

In this scenario we read two different customer records with a confirm in between.

Start both codeunits and then quickly press the confirm message. If you do this within the locktimeout window (default 10 seconds) you will get a deadlock.

Locktable

This last example also proves the myth about locktable busted. This is probably the most ambiguous command in C/AL language. It does not what it implies. It does NOT lock the table, but only locks the specific rows you read in the database.

Microsoft, can you please rename this to something that makes more sense?

Tools

You can also measure blocks and deadlocks using the tools Jorg Stryk provides for free. http://dynamicsuser.net/blogs/stryk/archive/2014/10/10/directions-emea-2014-troubleshooting-nav-2013-r2.aspx