Background
There are many possibilities for deadlocks to occur, this post specifically covers a condition I encountered where a table primary key and page lock on the same table created a deadlock error. I am not going to go into the specifics of troubleshooting deadlocks chances are if you have made it here you already know about using SQL Server Profiler. Deadlocks occur when two pid's are trying to use each others locked object at the same time. The most common cause I have encountered for deadlocks is timing, the longer the transaction or operation the higher the risk that another concurrent operation will create a deadlock situation.
Example
I encountered the deadlock below when a new table in the system began to grow quickly. The table in question was used in frequent and concurrent read/write operations. This situation is probably one that is frequently encountered and the solution in my case turned out to be very simple. Figure A can be distilled to the following sql:
CREATE TABLE A ( Id int IDENTITY PRIMARY KEY ) CREATE TABLE B ( Id int IDENTITY PRIMARY KEY, AId int FOREIGN KEY REFERENCES A(Id) ) CREATE TABLE C ( Id int IDENTITY PRIMARY KEY, BId int FOREIGN KEY REFERENCES B(Id) ) CREATE TABLE D ( Id int IDENTITY PRIMARY KEY AId int FOREIGN KEY REFERENCES A(Id), CId int FOREIGN KEY REFERENCES C(Id), ) //Insert some record into A,B,C,D ...// //Left oval in figure A This is the deadlock victim// DELETE B WHERE Id=1 //Right oval in figure B// DELETE C WHERE Id=1
Explanation
You might have expected the statements above to contain a delete cascade option there aren't any. If you are looking for indexes on any there would only be the primary key created by SQL server. Which leads me to the root cause of this issue, table D grew to 500K+ rows which is about where the adventure began. As I mentioned earlier the common cause I have encountered... timing or bad timing in this case. The deletes in table B and table C were conflicting because none of the foreign keys had indexes on them making the constraint checks take longer leaving more time for deadlock to occur. After adding the following indexes the deadlocks were immediately resolved:
CREATE INDEX IX_D_TO_A_FK ON D ( AId ) CREATE INDEX IX_D_TO_C_FK ON D ( CId )
