Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Dec 19 '11 at 19:11

This question came from our site for professional and enthusiast programmers.

3  
Are you sure you want to retrieve the newly inserted id with a select? There's a special thing for that. –  GSerg Dec 19 '11 at 18:34
    
GSerg, these days I usually prefer to use an OUTPUT clause for that type of thing. –  Toby Dec 19 '11 at 19:14
1  
Why would you use OUTPUT to get the identity value? OUTPUT causes the virtual tables to be created and loaded which means extra IO. It would be better just to grab the value from memory with the function instead of creating virtual tables (which are basically just temp tables) and loading them with data. –  mrdenny Dec 19 '11 at 22:07
add comment

2 Answers 2

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.

share|improve this answer
    
Not scope_identity!! What if there's parallelism?? Noooo!!! (Kidding. If you could get that statement to be multi-threaded, I would be very impressed) –  Anon246 Dec 19 '11 at 20:30
    
@Strommy - The bug you're referring to has been fixed. Unfortunately, Microsoft hasn't made it clear starting what version it's been fixed. –  Nick Chammas Dec 20 '11 at 21:05
    
Yep, in SQL 2012, which is in RC0 right now. Not too many people using that one yet (Aside from Denny and other dedicated folks). ;-) This sucker has been one of the top bugs since SQL 2005... –  Anon246 Dec 21 '11 at 19:31
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.