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.
Well from an application point of view there are:
- connection timeout (how long the app is willing to wait to establish a connection to SQL Server)
- command timeout (how long the app is willing to wait for a command to complete, including pulling the results down from SQL Server)
Back in my classic ASP days, the defaults for these were 15 and 30 seconds respectively, I have no idea what they are by default in .NET today.
SQL Server has its own set of timeouts, for example:
- Remote query timeout. Default is 600 seconds (10 minutes).
- Remote login timeout. Default is 10 seconds.
- Query wait. Default is -1 (25 x query cost).
- Full-text protocol handler timeout. Default is 60 seconds.
You can see these values for your system here:
SELECT * FROM sys.configurations
WHERE configuration_id IN (1519,1520,1541,1557);
There is also @@LOCK_TIMEOUT (which defaults to -1 (infinity)). This is how long SQL Server will wait on a blocked resource. You can override this for a particular session using SET LOCK_TIMEOUT. More details here.
Deadlocks I suppose could also fall into this category as well. The system scans for deadlock situations every 5 seconds, and there is no magic formula to determine when the deadlock will occur in relation to when any of the involved requests started. This is because SQL Server doesn't let the oldest transaction win; it chooses the victim based on DEADLOCK_PRIORITY and the estimated amount of resources required to roll the victim back. More details here.
There is also a memory grant timeout (which may be customized using Resource Governor). Depending on concurrency, a query won't necessarily fail if it reaches the timeout before obtaining all of the memory requested, it will just run with the allocated amount (and therefore might be less efficient). If it fails you will likely see Msg 8645.
You can get an idea for other potential timeout scenarios that may occur within SQL Server by reviewing these error messages:
SELECT message_id, [text]
FROM sys.messages
WHERE language_id = 1033
AND ([text] LIKE '%timeout%' OR [text] LIKE '%time out%')
However I don't think it is practical, feasible or productive for anyone to try to provide you with a complete and exhaustive list of every single timeout situation possible. Solve the problems you're having, rather than prematurely solving a whole bunch of problems you probably never will...
Best Answer
Deadlocks and blocking locks are two different concepts that you need to understand.
A deadlock is a situation where process/action 1 is waiting for process/action 2 to finish and at the same time process/action 2 is waiting for process/action 1 to finish. In other words. They would wait forever since they are waiting on each other.
In your scenario, something else is happening:
Process 1 is doing an action and has taken a lock on a resource to complete that action, Process 2 now wants to start a action that requires a lock on the same resource. Process 2 now has to wait for process 1 to complete and the lock is released. The key here is that at any given moment, none of the processes are waiting for each other (at the same time). One process is just waiting for the other process to finish an action on the same resource. They are not waiting for each other.
I hope that's clear.
On to how we fix your issue:
Can you post the Table definition, the indexes on the table and the delete select statement. We could have a look to see if there are ways to make the likelyhood of blocking locks less.