Deadlock problem

I've load testing a database solution and keep hitting a deadlock situation that I don't understand. I'm hoping that someone on this forum might have some solutions..

I've 3 tables: document, documentVersion and promotion table where documentVersion stores XML and the promotion table stores data extracted from the XML. I've an insertDocument stored procedure that:

Begins a transaction Inserts a new row into document (which has an identity column as a primary key) and records the scope_identity Inserts a new row into documentVersion with a FK reference to the new document row. The documentVersion table also has an identity columan as a primary key). Again scope_identity is recorded. The name of a custom stored procedure is formed and an execute statement is used to run the stored procedure. The custom stored procedure uses XQuery to extract rows from the XML and inserts the rows into the promotion table with a FK reference to the new documentVersion. The transaction is committed.

The deadlocks always occur in step 5 and invariably are caused by process1 having an X lock on PK_documentVersion (presumably because of the insert) and waiting for a shared lock on PK_documentVersion (presumably to check the FK constraint from the promotion table insert). Process2 is in exactly the same situation (i.e. holding X lock on PK_documentVersion and waiting for shared lock on PK_documentVersion).

If I run the test without the promotion (i.e. just inserting into document and documentVersion) and build up several thousand rows then I can reenable promotion and run my load test without deadlocks.

I've tried changing lock hints on the inserts, changing the isolation level (including trying snapshot) but all to know avail.

Can anyone explain the cause of the deadlock and suggest a remedy?

Much obliged,

David.

P.S. there are clustered indexes on the identity columns of document and documentVersion.

Here is an article I wrote a few months ago regarding how to track deadlock errors with SQLDiag, a helpful tool for such a purpose. http://articles.techrepublic.com.com/5100-9592_11-6116287.html

Have you tried using the table hint READPAST in your sql statements?|||

Thanks I'll look at the article.

Unfortunately, I have no control of the shared locks because the database engine sets these because of the FK check. If I was doing a select I could use the READPAST hint. Similarly, approaches such as using READ_COMMITTED_ISOLATION or SET TRANSACTION ISOLATION LEVEL SNAPSHOT have no effect on the FK check's use of locks.

David

|||

not sure if you have resolved this now,

if not, do you have deadlock trace information? deadlocks can occur for non-obvious reasons at the auto commit level, which won't be directly apparent from the sql