Can a deadlock issue be raise while running two select queries in sql server

Question

Do we get a deadlock issue while running two select statements of two different table in sql server

Answer 1

no. a deadlock occurs when two queries lock access to 2 common resource:

• query 1 needs a write lock on resource a, and holds a lock on resource b.
• query 2 needs a write lock on resource b, and holds a lock on resource a

query 1 needs will not release it lock on b until it gets a write lock on a. query 2 is the opposite. a deadlock.

note: the locked resource can be a row, page, index page, allocation page, etc.

Answer 2

You can indeed get a deadlock between two queries running a SELECT on two different tables.

The precondition is that both processes have a multi-statement transaction in progress, and both processes have updated rows in the table that the other process is reading.

Answer 3

Hi @Khan-CW, P Ali Altaf 63769,

Thanks for your information.

Deadlock happens when one query acquires a lock on an object (rows, data pages, extent, tables etc) and other resource tries to access it.

You can check out this article to learn more about deadlocks and how to monitor them. Hope this can help you well.

Please feel free to share your issue here if you have any confused.

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.

https://docs.microsoft.com/en-us/answers/support/email-notifications

Answer 4

Hi @Khan-CW, P Ali Altaf 63769,

Thanks for your kindly feedback.

We cannot determine whether a deadlock will occur just rely on the where or with where clause, we should look at the logic of the statement, all deadlocks boil down to out-of-order resource access patterns. Just as Erland and Omar say, the reason for the deadlock is:

You are pointing to the same table; they are using the same resources. Both processes have a multi-statement transaction in progress, and both processes have updated rows in the table that the other process is reading

You can also check out the article I provided in last answer; it explains the deadlock in detail. ,

In addition, this article is well worth for us to spend time reading it, hope this can help you understand more about deadlock and how to troubleshoot.

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.

https://docs.microsoft.com/en-us/answers/support/email-notifications