Shared and IX locks causing deadlock (Sql server)

Question

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 executes, it will have an IX lock on the table. Meanwhile, when the second insert statement to IssuePropertyValue table tries to execute, it will try acquiring a shared lock on the Issue table(because of the select clause). But it cannot get the lock as the table is already having an IX lock. But why this should be a deadlock? Once the IX lock is released, second statement should be able to get the Shared lock. Am i correct in my understanding?

It's only the second statement that is waiting on the first stmt to release the lock, but the first statement is not waiting on the second statement . So, ideally its not a deadlock. Does it have something to do with the executeBatch() called from java layer?

Answer 1

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.

Answer 2

Is it possible the source of problem in your case is lock escalation? According to MSDN article Lock Escalation (Database Engine) a lock escalation is triggered when a Transact-SQL statement acquires at least 5,000 locks on a single reference of a table or index, or, if the table is partitioned, a single reference of a table partition or index partition. For example, lock escalation is not triggered if a statement acquires 3,000 locks in one index and 3,000 locks in another index of the same table. Similarly, lock escalation is not triggered if a statement has a self join on a table, and each reference to the table only acquires 3,000 locks in the table.

Another great article related to Lock Escalation for SQL Server 2005 is Lock Escalation in SQL2005.

Hope this would help you to investigate the problem.