Sql-server – Does SQL Server place shared locks on scanned records when using REPEATABLE READ

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.

Knowing this, why does sql server need to issue U locks (when using RCSI)? It seems to me that sql server could simply read the rows, and request a X lock directly if an update must be performed.

Unlike SI, RCSI does not detect update conflicts. As documented in Books Online, modifying data under RCSI reads currently-committed data, not a possibly out-of date version. (In the absence of update conflict detection, performing updates based on out-of-date data could result in a "lost update".)

Taking update locks is normal behaviour for a non-row-versioning query that updates data. It is a protection against a common cause of conversion deadlock, but it does not guarantee deadlock avoidance in all cases, especially where a different access path (index) is used to qualify rows to change.

You can find more details about the exact behaviour of RCSI when modifying data in my SQLperformance.com article, "Data Modifications Under Read Committed Snapshot Isolation". There is further background on RCSI in general in the article, "Read Committed Snapshot Isolation".

If the updates really are disjoint, you might consider performing the change using Snapshot Isolation rather than RCSI (which admittedly has complex behaviour in this area).