Deadlock occurs when issuing SELECT and UPDATE statements in SQL Server 2019

Question

I'm using SQL Server 2019, but a deadlock occurs when there is a lot of access.
The target SELECT statement refers to only one record in a table, and the UPDATE statement rewrites the value in one record in a table.
When I used Jmeter and issued a mixture of this SELECT statement and UPDATE statement via JDBC, a deadlock occurred.

After investigating, it was said that turning on READ_COMMITTED_SNAPSHOT would solve the problem, so I was able to change it and solve it, but this method has a considerable effect on the application and cannot be changed easily.

If there is another solution, please let me know.

If there is no solution, I will give up.

Answer 1

Also check if the next simplistic opinion still has sense.

Some kinds of deadlocks mean “please wait while I am busy”, representing occasional transient phenomena. You should consider reducing this effect according to various techniques. In addition, it was recommended to intercept such errors programmatically, and retry the operation: https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms177453(v=sql.90). A series of SQL errors advises “retry the transaction” or “rerun the transaction”: https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-errors. You can follow these suggestions.

The retry loops can be implemented on client side (which is probably easier) or in SQL: https://learn.microsoft.com/en-us/previous-versions/sql/legacy/aa175791(v=sql.80).

Answer 2

Being chosen as a victim in a deadlock situation is always a possibility, the question is how big the risk is.

If optimistic concurrency isn't the solution, then you might reduce the risk by having supporting indexes for the WHERE clause for your UPDATE and SELECT. This way less data has to be touched when finding each row to be read/modified. No guarantees, of course, but it can help.

Answer 3

Hi @sato ,

Welcome to Microsoft Q&A!

Maybe you could use UPDLOCK. Here has a similar thread:

My guess is that the select-statement aquires a read-lock, when you come with the update-statement, then it needs to upgrade to a write-lock.

Upgrading to a write-lock requires that all other read-locks are removed (Their select-transactions completes). But if another process already have the brilliant idea to upgrade to a write-lock, then you suddenly have two processes waiting for each other to release the read-lock, so they can get the write-lock.

If using select-for-update (UPDLOCK) then it will aquire a write-lock from the beginning and then you don't have the deadlock issue.

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.