The Briefing

The application that manipulated this database was written as VB.net running as COM+. Yes, COM+, I didn't know what was this as well, it is a technology for running your application as software components that had been introduced by Microsoft in 1993.

The company was in a close city to São Paulo — Brazil, so I took the bus early in the morning and went there. On my first day at the company, my deadlock knowledge very basic, I knew what was the concept, but I had no idea on the details of locking of the database engine.

Initial Investigation

I started by asking the developers about what they thought it could be the cause of the deadlocks, I've got: "It is the database fault", and you could guess the answer of the DBA's was simple "It is the application fault".

The DBA showed me the existing SQL Profiler sessions from times of deadlocks occurred, there were normal queries, but there wasn't anything too obvious on why the deadlocks were occurring.

My first rounds of investigations was a brief look at the code. From which I found some really bad practices that would waste application memory, network usage and had low maintainability:

But still, I was not a single step closer to the deadlocks, so I went home and watched a Pluralsight course about deadlocks on the bus back and at home. That gave me all the tools I needed for solving this problem as it taught me the types of locks used by SQL, their granularity and especially how to capture a visual representation of deadlocks using SQL Profiler (graph event).

The Solution

The graphs the SQL Profiler gave me for almost all the deadlocks were similar to this:

Very good representation isn't it? Not only it draws for you who is blocking what, but it also gives the type of locks that conflicted. What was strange on the diagnostic is this "Request Mode: RangeS-S", that means a key range shared lock. Why are we getting such a lock?

Remember the lock we got on the deadlock graph is from a Serializable transaction level, this is the definition:

All right, still very strange, why was the transaction using the most restrictive transaction isolation level? I showed my finding to the DBAs and they still blamed the application. I asked the developers and they blamed the database.

So we checked the default isolation level in the database and it was as "ReadCommited", which was all right. So my scope was why some transactions were as serializable?

I had an epiphany and remembered we were using COM+, so I googled "COM+ default transaction isolation level" and got this:

So it was just the default isolation level of COM+. Moving to a different isolation level than the default stopped the deadlocks that were occurring as now the locks were not so restrictive.

Conclusion

Hopefully, here you got one of your first exposures into a bit of the internals of transaction levels, locking in SQL Server and also on how to capture deadlock events if needed.