question

sato-5079 avatar image
0 Votes"
sato-5079 asked CarrinWu-MSFT commented

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


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.

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @sato-5079, we have not get a reply from you. Did any answers could help you? If there has an answer helped, please do "Accept Answer". If not, please let us know. By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

1 Vote 1 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered ErlandSommarskog commented

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://docs.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://docs.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://docs.microsoft.com/en-us/previous-versions/sql/legacy/aa175791(v=sql.80).


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Certainly, that method seems to solve the problem.
I thought I would get a timeout error instead of a deadlock if I meant to wait while I was busy.
Does SQL Server use deadlock and timeout interchangeably?

0 Votes 0 ·

Does SQL Server use deadlock and timeout interchangeably?

No, SQL server searches for deadlocks every 5 seconds. This is not based on timeouts. However, you typically have a blocking situation that leads up to deadlocks. So blocking and deadlocks are related.




1 Vote 1 ·

I thought I would get a timeout error instead of a deadlock if I meant to wait while I was busy.
Does SQL Server use deadlock and timeout interchangeably?

No. First of all, SQL Server, by default never times out, but waits forever if a statement is blocked, as long as there isn't a deadlock. The common timeout error Timeout expired , is an error from the client API. Most client APIs by default waits for thirty seconds before raising the error.

You can induce timeouts on the SQL Server level with the command SET LOCK_TIMEOUT, so that SQL Server will raise an error if lock is not obtained within n milliseconds. This is a fairly specialised option that is not used very frequently.

1 Vote 1 ·
TiborKaraszi avatar image
0 Votes"
TiborKaraszi answered TiborKaraszi commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

In this SQL, both the select statement and the update statement refer to or update the same record.
My sense is that deadlocks don't occur, is there something wrong?

0 Votes 0 ·

I'm not sure what you are asking.

Are you saying that you don't get a deadlock and ask if there is something wrong that you don't get a deadlock?

Or are you saying that you do get a deadlock and if there is something wrong in that?

Bottom line is that deadlocks can happen. And, yes even with the same row. One aspect is to go though your indexes and make sure that you have the indexes that help the query . but no more. We can't say anything more since we have close to no information about your system, tables, SQL Code, indexes etc.

1 Vote 1 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @sato-5079,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.