I have been pulling my hair on this deadlock:
[Enlarge]
IX_OrderAmounts is an indexed view doing aggregations on the Amount table. The transaction on the left (Repeatable read - although same happens with Read commited) only inserts a single row in the Amount table. The transaction on the right (Read committed) updates a few rows (2 - 3 rows) from the Amount table (all in one UPDATE statement).
Now what I don't get is why the transaction on the left, which only needs to insert a row, needs to acquire a second update lock.
Because the deadlock is on the indexed view that I don't update directly, I can't really use SQL hints (or at least I don't know how).
How can I resolve this deadlock?
Typical methods you can use to resolve deadlocks include:
Keeping transactions as short as possible.
In SQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:
Load SQL Profiler with a focus on locking, initiate the scripts which deadlock specifically for that DB and feed it into the Database Engine Tuning Adviser, and review the suggestions.
Footnotes: http://support.microsoft.com/kb/832524
[[1]: http://support.microsoft.com/kb/832524
http://www.codeproject.com/Articles/42547/SQL-SERVER-How-To-Handle-Deadlock
Prior Stackoverflow locking question how to solve deadlock problem?