I detected a bookmark lookup deadlock in my application, and I can't decide which solution to use. None of them seem to be optimal.
Here are the queries:
UPDATE TEST SET DATA = @data WHERE CATEGORY = @cat
SELECT DATA, EXTRA_COLUMN FROM TEST WHERE CATEGORY = @cat
The problem is that there is an unclustered index in CATEGORY and DATA that is used by both queries in reverse order with the clustered index.
i.e.: The update locks the clustered index and update the table, while the select locks the unclustered index to make the bookmark lookup, and them both want each others locks (deadlock).
Here are the options that I found:
1 - Create an index that includes all the columns from the select query. - It worked, but I don't think is a good idea, I would have to include any column that is used in any select query that can be update anywhere in the application.
2 - Change the transaction isolation level of the database to COMMITTED_SNAPSHOT
3 - Add NOLOCK hint to the select
4 - Drop the index
5 - force one of the transactions to block at an earlier point, before it has had an opportunity to acquire the lock that ends up blocking the other transaction. (Did not work)
I think the second option is the best choice, but I know that it can create other issues, shouldn't the COMMITTED_SNAPSHOT be the default isolation level in SQL SERVER?
It seems to me that there isn't any error either in the application or in the database logic, it's one simple table with an unclustered index and two queries that acces the same table, one to update and the other to select.
Which is the best way to solve this problem? Is there any other solution?
I really expected that SQL Server was able to solve it by itself.
Snapshot isolation is a very robust solution to removing reads from the equation. Many RDBMSes have them always on. They don't cause a lot of problems in practice. Prefer this solution to some manual brittle solution such as very specific indexes or hints.