2

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.

dinhokz
  • 895
  • 15
  • 36
  • The best option is likely to tweak your queries so that they won't deadlock eachother. Can you include the queries in the question? – Jon Tirjan Apr 20 '15 at 19:36
  • The problem is that one query updates a column that is used in the other query index. A simple 'select * from' and an update that changes any of the columns used in the unclustered index from the previous select would deadlock. – dinhokz Apr 20 '15 at 19:40
  • Locking/blocking are likely unavoidable, but that doesn't mean deadlocks can't be avoided. Tough to say for sure without seeing the actual queries. – Jon Tirjan Apr 20 '15 at 19:45
  • Just included the queries and a brief description of the deadlock cause. – dinhokz Apr 20 '15 at 19:54
  • You mentioned 2 tables in the question, but there's only 1 referenced in the queries. – Jon Tirjan Apr 20 '15 at 20:11
  • Sorry, it's only one table. – dinhokz Apr 20 '15 at 20:14

2 Answers2

1

Please try adding a nonclustered index on Category (include Data & Extra_Column) and adding the following hints to your queries:

UPDATE t SET t.DATA = @data FROM TEST WITH (index(ix_Cat)) WHERE CATEGORY = @cat

SELECT DATA, EXTRA_COLUMN FROM TEST WITH (index(ix_Cat)) WHERE CATEGORY = @cat

This will ensure that both queries will Update/Select data in the same order, and will prevent them from deadlocking eachother.

Jon Tirjan
  • 3,556
  • 2
  • 16
  • 24
  • The problem is that I will need to fix every select query that use the same index, and any other developper will have to know about it. – dinhokz Apr 21 '15 at 21:19
1

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.

usr
  • 168,620
  • 35
  • 240
  • 369
  • I agree with you, but as I sad, it can cause other errors. – dinhokz Apr 21 '15 at 21:16
  • 1
    `READ_COMMITTED_SNAPSHOT` very rarely causes errors or problems. You need to contrive cases to find problems. They do exist but it's much less worse than having to worry about inconsistent reads and deadlocks in read queries all the time. SI is an extremely nice an general solution. Very underappreciated because it's off by default and poorly understood. – usr Apr 21 '15 at 21:52
  • Reading more about the Snapshot Isolation level I noticed that there is a different kind of conflict, the update conflict. Ins't it a good reason for continue using the read commited isolation level? How can I guarantee that update conflicts will not happen? – dinhokz Apr 27 '15 at 17:39
  • I usually recommend SI for read-only queries (no-brainer) and for write queries with guaranteed non-overlapping data. In other cases different isolation levels can be more appropriate. In your question I'd make the SELECT use SI. That takes it out of the equation. – usr Apr 27 '15 at 18:36