SQL 2008 SP - Deadlock cause or red herring?

Question

One of my co-workers has a stored procedure that does the following
Begin tran
1) Dynamically generate a select statement.
2) Insert into table x
3) Execute the select statement
End tran

If this stored procedure is ran by two septate threads simultaneously, he gets the following error: System.Data.SqlClient.SqlException: Transaction (Process ID 57) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction

Is this stored procedure really the issue? From my naive mind, this looks to be at worse a race condition, not a deadlock.

Answer 1

run a trace in the profiler (pick the blank template), select the deadlock graph event, and on the new tab that appears (Events Extraction Settings), save each (check save deadlock XML events separately) in its own file. Open this file in an xml viewer and it will be easy to tell what is happening. Each process is contained, with a stack of procedure calls, etc. and all locks are in there too, so you can be sure what is causing the deadlock.

Let this trace run until the deadlock happens again, info is only recorded when a deadlock happens, so not much overhead.

Answer 2

Two 'write then read' sequences can definetely deadlock. You omitted some 'details' in your post, like what is the actual resource the deadlock occurs on and that requests are involved. We'll fly by the sit of our pants and read between the lines, making up much of the case from such a poorly documented post:

1. Cross write-read. Thread 1 inserts row with key A and then selects row with key B. Thread 2 inserts row with key B then selects row with Key A. Execution order is T1(A), T2(B), T1(B)-wait, T2(A)-deadlock.
2. Independent write-read: T1 inserts A then reads A, T2 inserts B then reads B. Critical info: there is no index on the key, so a tabel scan is needed to read A and/or B. Execution order is T1 writes A, T2 writes B, T1 reads A, starts scan, blocks on T2's X-lock on B, T2 reads B, starts scan, blocks on T1's X-lock on A, deadlock.
3. Independed optimized write-read. This case is the most bafeling for most newcomers, when proper access indexes are in place yet deadlocks still occur. I've presented this case in Read/Write deadlock, an update can deadlock agaisnt a read because of different index access order. Unlikely to be your case, but with such poor documentation, anything is possible.

Many many more deadlock scenarios are possible, but we'd enter esoterics or start to extrapolate quite far for the missing info in the OP.

If I'd venture a guess, the most likely case is 2). The case 1) would probably easily be identifier. Case 2) is a bit harder to detect in simple code analysis because it depnds on the physical schema design (index structure).