请求select语句也会触发死锁吗

Question

请求select语句也会触发死锁吗

eric zhao 0

我的sqlserver版本是2016，最近遇到个死锁问题困扰了很久，三张表left join的查询触发了死锁，我印象中好像select语句是不会触发死锁的吧，请问有大神帮忙看下原因吗

用户的图像

0 个注释

2 个答案

你的答案

Answer 1

博雄胡 765

默认事务隔离级别下，只要存在同时发生查询和增删改的业务场景，就可能会出现死锁。如果只有查询，就不会出现。

所以很显然，这个查询的同时，还有增删改业务在发生。

你可以捕捉下次发生死锁时的死锁xml，里面能告诉你哪段sql和这个查询冲突导致死锁。然后做针对性优化。

eric zhao 0 信誉分

2026-06-03T00:50:16.59+00:00

谢谢回复，我们这是开发，死锁事件需要通过sql server的管理工具查询吧，我去问下运维有没有

Answer 2

Lakshmi Narayana Garikapati 1,335 Microsoft 外部员工审查方

Hi @eric zhao ,
Thanks for reaching out to SQL Q&A Forum

Even though SELECT queries don’t modify data, they can still participate in deadlocks in SQL Server. This happens because SELECT acquires shared locks under the default isolation level, which may conflict with exclusive locks from concurrent UPDATE or INSERT operations. When multiple transactions hold locks that the other needs, SQL Server detects a cycle and chooses one query as the deadlock victim — often the SELECT. To reduce these conflicts, you can consider options like adding appropriate indexes, using snapshot isolation (row versioning), or reviewing query design to ensure consistent access order across tables. Capturing a deadlock graph will help identify the exact queries and resources involved.

https://learn.microsoft.com/en-us/answers/questions/386771/deadlock-occurs-when-issuing-select-and-update-sta

https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-deadlocks-guide?view=sql-server-ver17&

Thanks,

Lakshmi.

eric zhao 0 信誉分

2026-06-02T03:20:01.6566667+00:00

@Lakshmi Narayana Garikapati First of all, thank you for your reply.

I simulated a typical deadlock scenario using Navicat. The numbers preceding the SQL statements indicate the execution order:

Transaction A:

1- intsert into tableA

3- intsert into tableB

Transaction B:

2- intsert into tableB

4- intsert into tableA

The result met my expectations.

Then I changed Transaction B to a single query statement, which would block until Transaction A committed or rolled back

2- select from tableA left join tableB

The result met my expectations too

Therefore, I still can't understand why a single query would cause a deadlock.
Lakshmi Narayana Garikapati 1,335 信誉分 Microsoft 外部员工审查方

2026-06-05T05:34:11.4966667+00:00

Hi @eric zhao ,
Thank you for your detailed explanation. It looks like you've already followed the recommended steps, but the issue persists.

To help resolve this further, I recommend raising a support ticket so our support team can assist you directly. Please refer to the following link for instructions on how to create a support ticket:

https://support.serviceshub.microsoft.com/supportforbusiness/create

We appreciate your engagement and thank you for being an active part of the community.

Thanks,

Lakshmi.