In my environment, I have 2 tables -
IssuePropertyValues (which has foreign key reference to the
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:
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?