SELECT JOIN语句与SQL Server引起的死锁
当使用两个表的JOIN执行SELECT语句时，SQL Server似乎分别锁定了两个语句的表。 例如通过像这样的查询：
SELECT ...
FROM
table1
LEFT JOIN table2
ON table1.id = table2.id
WHERE ...
我发现锁的顺序取决于WHERE条件。 查询优化器会尝试生成一个只根据需要读取尽可能多的行的执行计划。 因此，如果WHERE条件包含table1的列，它将首先从table1中获取结果行，然后从table2中获取相应的行。 如果该列是来自table2，它将以相反的方式进行。 更复杂的条件或索引的使用可能也会影响查询优化器的决策。
当由语句读取的数据应该在事务中用UPDATE语句稍后更新时，不保证UPDATE语句的顺序与用于从2个表中读取数据的顺序相匹配。 如果在事务更新表时另一个事务尝试读取数据，那么当SELECT语句在UPDATE语句之间执行时会导致死锁，因为SELECT不能获取第一个表上的锁，UPDATE也无法获取锁第二张桌子。 例如：
T1: SELECT ... FROM ... JOIN ...
T1: UPDATE table1 SET ... WHERE id = ?
T2: SELECT ... FROM ... JOIN ... (locks table2, then blocked by lock on table1)
T1: UPDATE table2 SET ... WHERE id = ?
两个表都表示一个类型层次结构，并且总是一起加载。 所以使用带有JOIN的SELECT加载对象是有意义的。 单独加载两个表不会使查询优化器有机会找到最佳执行计划。 但是由于UPDATE语句一次只能更新一个表，当一个对象被另一个事务更新时，这会导致死锁。 当属于不同类型层次结构的对象的属性更新时，对象的更新通常会导致两个表上的UPDATE。
我试图给SELECT语句添加锁定提示，但这并不能改变问题。 当两个语句试图锁定表时，它只会导致SELECT语句中的死锁，而一个SELECT语句以另一个语句的相反顺序获取锁。 也许可以使用相同的语句强制锁定为相同的顺序来加载更新的数据。 这样可以防止两个要更新数据的事务之间发生死锁，但不会阻止只读取数据的事务处于死锁状态，这需要具有不同的WHERE条件。
到目前为止，唯一的工作全面，似乎是读取可能不会锁定。 对于SQL Server 2005，可以使用SNAPSHOT ISOLATION完成。 SQL Server 2000的唯一方法是使用READ UNCOMMITED隔离级别。
我想知道是否有其他可能性来防止SQL Server导致这些死锁？
当读者不阻止作者时，这在快照隔离下不会发生。 除此之外，没有办法阻止这种事情发生。 我在这里写了很多repro脚本：重现只涉及一个表的死锁
编辑：
我无权访问SQL 2000，但我会尝试使用sp_getapplock序列化对该对象的访问，以便读取和修改不会同时运行。 如果你不能使用sp_getapplock，推出你自己的互斥锁。
解决这个问题的另一种方法是将select ... from ... join分解为多个select语句。 将隔离级别设置为读取已提交。 使用表变量来管理select中要加入其他数据的数据。 使用distinct可以将插入过滤到这些表变量中。
因此，如果我有两个表A，B，我将插入/更新到A然后B中。作为sql的查询优化器首选读取B和A.我将单个选择分为2个选择。 首先我会读到B.然后将这些数据传递给下一个选择的语句，其中读取A.
这里死锁不会发生，因为表B上的读锁将在第一条语句完成后立即释放。
PS我遇到过这个问题，并且这个工作非常好。 比我的部队命令答案好得多。
我面临同样的问题。 使用查询提示FORCE ORDER将解决此问题。 缺点是你无法利用查询优化器对查询的最佳计划，但这会防止死锁。
所以（这是来自“Bill the Lizard”用户）如果您有查询FROM table1 LEFT JOIN table2并且您的WHERE子句只包含来自table2的列，那么执行计划通常会首先选择table2中的行，然后查找table1中的行。 对于table2中的小结果集，只需要获取table1中的几行。 使用FORCE ORDER，首先必须获取table1中的所有行，因为它没有WHERE子句，那么将连接来自table2的行并使用WHERE子句过滤结果。 因此降低了性能。
但如果你知道情况不是这样的话，请使用这个。 您可能想要手动优化查询。
语法是
SELECT ...
FROM
table1
LEFT JOIN table2
ON table1.id = table2.id
WHERE ...
OPTION (FORCE ORDER)
上一篇: Deadlock caused by SELECT JOIN statement with SQL Server
下一篇: What is a convenient way to store and retrieve boolean values in a CSV file