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

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

dbo owned stored procedure hitting another user owned table

I am trying to come up with a solution that does not involve having a version of every stored procedure for every user I have...

Here is the problem...

I am going to have multiple users that need to have their own "product table". The structures are going to be the same for all. We currently only have one user and it is a DBO... all stored procedures are dbo.[sp name]... is there any way to get it so that the product table in the SP will be the user owned product table and not the dbo table?

I have tried just taking out the dbo prefix with no luck... the user's default schema will match the table they own so when they do a straight select they get the right information but it is just the SPs that I can't seem to get to work...

The only thing that I have come up with is making the SPs dynamic with having the username as parameter.

Is there anything else I can try?

and SQL 2005 SP2 on Win 2003 SP2

No. Dynamic SQL is the only way to get the schema resolution to work like you want. Optionally, you can consider querying both the tables and adding a filter on the username value like below. The filter on the user name will get evaluated at compile/run-time thereby eliminating all the queries except one. This is of course cumbersome if you have many objects.

Code Snippet

select ...

from user1.table as t1

where CURRENT_USER = 'user1'

union all

select ...

from user2.table as t1

where CURRENT_USER = 'user2'

union all

select ...

from user3.table as t1

where CURRENT_USER = 'user3'

Why can't you create wrapper SPs for each table and call them from the dbo SP? Each wrapper SP will query the table under that schema.

|||

I figured it would not work that easily...

What do you mean by "wrapper SP"?

dbo owned stored procedure hitting another user owned table

I am trying to come up with a solution that does not involve having a version of every stored procedure for every user I have...

Here is the problem...

I am going to have multiple users that need to have their own "product table". The structures are going to be the same for all. We currently only have one user and it is a DBO... all stored procedures are dbo.[sp name]... is there any way to get it so that the product table in the SP will be the user owned product table and not the dbo table?

I have tried just taking out the dbo prefix with no luck... the user's default schema will match the table they own so when they do a straight select they get the right information but it is just the SPs that I can't seem to get to work...

The only thing that I have come up with is making the SPs dynamic with having the username as parameter.

Is there anything else I can try?

and SQL 2005 SP2 on Win 2003 SP2

No. Dynamic SQL is the only way to get the schema resolution to work like you want. Optionally, you can consider querying both the tables and adding a filter on the username value like below. The filter on the user name will get evaluated at compile/run-time thereby eliminating all the queries except one. This is of course cumbersome if you have many objects.

Code Snippet

select ...

from user1.table as t1

where CURRENT_USER = 'user1'

union all

select ...

from user2.table as t1

where CURRENT_USER = 'user2'

union all

select ...

from user3.table as t1

where CURRENT_USER = 'user3'

Why can't you create wrapper SPs for each table and call them from the dbo SP? Each wrapper SP will query the table under that schema.

|||

I figured it would not work that easily...

What do you mean by "wrapper SP"?