ORM tools and other abstraction on RDBMS have become ubiquitous. But there is no substitute for understanding the basics of a database. This opinion of mine was only reinforced by a recent issue which I was fixing with a colleague.
Bug: Error log for showed the below exception
System.Data.SqlClient.SqlException: Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Tech stack: .Net 3.5, sqlserver 2005, nhibernate
The exception stack trace pointed to the table that was being deadlocked.
Could not execute command: UPDATE Email SET PersonId = @p0 WHERE Id = @p1
Recreating deadlock issues is not a trivial thing. But thankfully in our case the deadlock was so severe that when I ran my tests in parallel, almost 50% of the transactions failed at a concurrent load of just 2. That was a decent first step since we were consistently able to reproduce the issue.
Sqlserver Management studio comes with some tools which are quite useful in this situation. To see what was causing the deadlock all I had to do was to run profiler on the database. To launch a profile follow the below steps.
tools > profiler > file > new trace > mention database details
The trace properties window should open up. Open the event selection tab and select show all events. This should show more events. Under the locks section select all the events that may be useful to you.
Start the trace, run your tests in parallel sit back with some popcorn and enjoy the action packed adventure. Run a find for Deadlocks and you should be presented with a nice picture of what is happening.
Lets zoom in on the action.
Inferences:
- The deadlock is not on the object, because the object ids are the same. This is something which we also guessed from the query in the exception log
UPDATE Email SET PersonId = @p0 WHERE Id = @p1 - But the page ids are different.
Quite puzzled we looked at the table design to see if something was wrong there. And yes we saw what the problem was. The table did not have a primary key column.
Even though that may look like harmless issue, there are consequences of creating a table without a primary key in sqlserver. When you define a primary key a unique clustered index is created. But this table had a unique constraint on the id column, which would create a unique non-clustered index. Non-clustered secondary indexes may introduce deadlocks. More details in this link (See Non-Clustered indexes). You will also find it very useful to know how clustered and non-clustered indexes work.
In this case, the primary key and there by the clustered index was missing. We introduced a primary key constraint on the id column and ran tests again. Even at a much higher concurrent user count the deadlocks did not happen again.
HariKrishnan 