选择查询的事务死锁 [英] Transaction deadlock for select query

问题描述

有时，对于仅作为Select查询的存储过程，我会遇到以下错误：事务（进程ID 91）在锁上死锁

Occasionally, I have the following error for a stored procedure which is only a Select query: Transaction (Process ID 91) was deadlocked on lock

我最初的理解是，选择查询不会锁定表，也不会导致死锁，即使它试图查询的表正在被另一个进程更新/锁定，但是它

My initial understanding was that a select query won't lock a table, or won't cause a deadlock even if the table it tries to query is being updated/locked by another process, but it seems that a select query can cause deadlocks as well.

如果我将隔离级别设置为不提交查询，那是否可以解决问题？

If I set the isolation level to read uncommitted for the query, will that solve the problem?

推荐答案

我的初步理解是Select
查询不会锁定表，也不会锁定表
导致死锁

My init understanding is that a Select query won't lock a table, or won't cause a deadlock

这种理解是错误的。 SELECT查询在其分析的行上使用共享锁。共享锁可能与来自更新/删除/插入语句的排他锁冲突。两个SELECT语句不会死锁，但是SELECT可以通过UPDATE死锁。当发生这种死锁时，SELECT通常是受害者，因为它没有执行任何更新，因此总是会丢失抽签。

This understanding is wrong. SELECT queries take shared locks on the rows they analyze. Shared locks may conflict exclusive locks from update/delete/insert statements. Two SELECT statements are not going to deadlock, but a SELECT can deadlock with an UPDATE. When such deadlock occurs, the SELECT is usually the victim as it did not perform any update so is always going to loose the draw.

与任何死锁一样，您需要发布所涉及表的确切架构，确切的T-SQL语句和死锁图。请参见如何：保存死锁图（SQL Server Profiler）。通过此信息，您可以获取有关如何修复死锁的指导。

As with any deadlock, you need to post the exact schema of the tables involved, the exact T-SQL statements and the deadlock graph. See How to: Save Deadlock Graphs (SQL Server Profiler). With this information you can receive guidance how to fix the deadlock.

这篇关于选择查询的事务死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！