It’s Not Just You: Blocked Process And Deadlock XML Is Misleading

Posted on by Erik Darling

Lucky Pencil

There you sit, an earnest database glob. Users are complaining that the database is slow again, but this time you’re ready for it.

You’ve got a monitoring tool collecting all the right things from all the right places, and it’s telling you that there’s blocking going on.

All those poor queries suffering under the tyrannical yolk of Read Committed The Garbage Isolation Level, yearning for the freedom of Read Committed Snapshot Isolation.

But when you crack open the blocked process report, it makes absolutely no sense at all.

Not one bit.

All Wrong

To show you an example of how bad these things are at collecting the actual problem, I’m going to use the code examples in this blog post about Implicit Transactions, which are horrible and you shouldn’t use them or the JDBC driver.

I mean you should always use them and call me when you need help fixing your horrible blocking problems. My rates are reasonable.

Anyway, when you look at the Blocked Process Report for that code, here’s all the stuff that’ll look screwy:

First, the object and index IDs will both have 0 for a value:

<data name="object_id">
  <type name="int32" package="package0" />
  <value>0</value>
</data>
<data name="index_id">
  <type name="uint32" package="package0" />
  <value>0</value>

Second, the lock mode will say S, which is possible when readers block writers, but…

<data name="lock_mode">
  <type name="lock_mode" package="sqlserver" />
  <value>3</value>
  <text>S</text>

Third, the statements that will be shown as blocking each other are both selects from different tables.

Blocked:

<inputbuf>
SELECT TOP (100)
    p.*
FROM dbo.Posts AS p
WHERE p.ParentId = 0
ORDER BY p.Score DESC;   
</inputbuf>

Blocking:

<inputbuf>
SELECT TOP (10000)
    u.*
FROM dbo.Users AS u
WHERE u.Reputation = 2
ORDER BY u.Reputation DESC;   
</inputbuf>

What Can You Do?

Not a whole lot. This stuff is just plain broken outside of very simple blocking and deadlocking patterns.

Monitoring tools that rely on these things to show you issues are going to bring back really confusing details.

You might think it would just be a matter of decoding the wait resource, but that’s a doggone annoying process on its own.

Thanks for reading!

Related Posts