Sql Server deadlock error when performing multiple Inserts within a stored procedure

Question

I am running into a deadlock issue when my stored procedure is called in sql server 2008. an xml string is passed into the stored procedure by biztalk and can be called many times in quick succession. The problem I am running into is that if the procedure is called 5 times in quick succession, the first 4 calls are rolled back and the last call is committed to the database due to the deadlock. Below is the code for the procedure - it uses OPENXML to parse the xml string and insert into Table A. I then take the new unique identifier from Table A and insert multiple child records into Table B. Any guidance on how to resolve this issue would be much appreciated.

Error Message: System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Table Details
TableA
- Id int identity(1,1) Primary Key,
- ColumnA varchar(15) not null,
- ColumnB varchar(20) not null,
- AddedDateTime datetime default(getdate())

TableB
- Id int identity(1,1) Primary Key,
- TableAId int not null, (FK)
- ColumnC varchar(30) not null

XML

<Transactions>
<Transaction>
    <ColumnA>Column A Value</ColumnA>
    <ColumnB>Column B Value</ColumnB>
    <ChildItems>
        <ChildItem>
            <ColumnC>Column C Value</ColumnC>
        </ChildItem>
        <ChildItem>
            <ColumnC>Another Column C Value</ColumnC>
        </ChildItem>
        <ChildItem>
            <ColumnC>Yet Another Column C Value</ColumnC>
        </ChildItem>
    </ChildItems>

Stored Procedure

 CREATE PROCEDURE [dbo].[proc_ProcessXml] 
    (
        @ResponseXml varchar(max)
    )
    WITH EXECUTE AS CALLER  
    AS

    BEGIN TRANSACTION
    DECLARE @xmlHandle int
    declare @tableAId int

    EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @ResponseXml, 

    INSERT INTO TableA 
    (
        ColumnA,
        ColumnB             
   ) 
   SELECT columnA, columnB
    FROM OPENXML(@xmlHandle, '/Transactions/Transaction', 1)
    WITH(
        columnA varchar(15) 'ColumnA',      
        columnB varchar(20) 'ColumnB'
    )

    select @tableAId = SCOPE_IDENTITY()

    INSERT INTO TableB 
    (
        TableAId,
        ColumnC             
    )  
    SELECT @tableAId, columnC
    FROM OPENXML(@xmlHandle, '/Transactions/Transaction/ChildItems/ChildItem', 1) 
    WITH(       
        columnC varchar(30) 'ColumnC',
    )

    EXEC sp_xml_removedocument @xmlHandle

    IF @@ERROR <> 0
        BEGIN                   
            ROLLBACK
        END
    ELSE
        BEGIN           
            COMMIT
        END

Answer 1

There could be a lot of different causes for this behavior so you need to get more facts. You need to know the isolation levels and the locks they're fighting for. Here's what I'd do:

1. Get four windows ready in Mgmt Studio to call your proc
2. Check the Isolation Levels of each caller right before they call the proc (dbcc useroptions).
3. Identify the spid of the four callers (@@spid)
4. Take a snapshot of all the locks before starting your tests from a fifth session:

.

select
     object_name(P.object_id) as TableName, L.*
into
    #preTestLocks
from     
    sys.dm_tran_locks L
    join sys.partitions P on L.resource_associated_entity_id = p.hobt_id
where
     object_name(P.object_id) in ('TableA','TableB')

1. Add a wait within the proc after the TableA insert (WAITFOR DELAY ’00:00:30′) so that you can look at things in motion.
2. Start running the proc in each session but after each start take a snapshot of the locks from your fifth window:

.

select
     object_name(P.object_id) as TableName, L.*
into
    #lock1  --<<CHANGE AFTER EACH RUN (#lock2, #lock3 etc.)
from     
    sys.dm_tran_locks L
    join sys.partitions P on L.resource_associated_entity_id = p.hobt_id
where
    resource_session_id in (1,2,3,4) --<<YOUR SPID'S

Analyze the results and see what resources are causing the deadlock condition. You might have lock escalation issues going on that are escalating your row level locks to page or extend or even table level locks. Read here for a description of Lock Modes.

One last observation:

You might be playing with fire by starting a transaction inside a proc and not specifying SET XACT_ABORT ON (See here for details). I doubt that this is causing your current behavior unless your clients have amazingly short timeouts but I'd strongly recommend adding that.