Shared and IX locks causing deadlock (Sql server)

In my environment, I have 2 tables - Issue and IssuePropertyValues (which has foreign key reference to the Issue table).

Now, we insert values into these 2 tables using the below 2 queries:

INSERT INTO [Issue] ([IssueId], [AssetId]) VALUES (?, ?)
INSERT INTO [IssuePropertyValues] SELECT TOP 1 AssetIssueId, ?, ? FROM [Issue] WHERE [AssetId] = ? AND [IssueId] = ? "

These 2 statements are invoked from the java layer using batch update as shown:

issueStmt.executeBatch();
issuePropertiesStmt.executeBatch();

Now when more than 10000 values are being inserted, SQL server is throwing deadlock errors:


java.sql.BatchUpdateException: Transaction (Process ID 506) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

When I studied the deadlock behavior using DBCC TRACEON(1204,-1), I learnt that
the deadlock is for a page lock resource. What I see is that one of the stmts is having a shared lock, and another one an IX(Intent exclusive) lock. But I am not able to understand why this should be causing a deadlock. I understand shared and IX locks are not compatible with each other, but if a deadlock has to happen , it should throw even when few records are inserted.Why is it throwing when 10,000 plus records are inserted?

The DBCC trace snippet is as below

owner-list
2011-12-16 04:02:09.40 spid32s owner id=process1b1c8ce08 mode=IX spid=372
INSERT INTO [IssuePropertyValues] SELECT TOP 1 AssetIssueId, @P0 , @P1 FROM [Issue] WHERE [AssetId] = @P2 AND [IssueId] = @P3

waiter id=process1b1c8c748 mode=S requestType=convert
pagelock fileid=5 pageid=3506256 dbid=7 objectname=Issue mode=IX

Thanks for the explanation. Yes, I shall implement the solution you suggested. I realised that fetching the id using select clause is the culprit.

But I m still not really clear on the cause of the deadlock. What I understand is, when the first insert statement to the issue table execu

You'll want to change your code to something like this.

DECLARE @AssetIssueId INT
INSERT INTO [Issue] ([IssueId], [AssetId]) VALUES (?, ?)
SET @AssetIssueID = scope_identity()
INSERT INTO [IssuePropertyValues] 
@AssetIssueID, ?, ?

As to the why...

If Java is starting a transaction for you automatically, and the insert of 10k+ rows is taking a few moments, then this thread is taking an IX on the Issue table. Another thread is then taking an IX on the issue table (which is blocked). This thread is then attempting to take an S on the issue table (for the INSERT SELECT FROM statement) which is blocked waiting for the other thread, and we now have a deadlock.

StackExchange Database Administrators Q#9409, answer score: 2