sql-server - SQL Server 的 SELECT JOIN 语句导致的死锁

Question

当执行带有两个表 JOIN 的 SELECT 语句时，SQL Server 似乎分别锁定语句的两个表。例如通过像这样的查询这个:

SELECT ...
FROM
    table1
    LEFT JOIN table2
        ON table1.id = table2.id
    WHERE ...

我发现锁的顺序取决于WHERE条件。这查询优化器尝试生成一个只读取尽可能多的执行计划根据需要行。所以如果WHERE条件包含table1的列它会首先从table1中获取结果行，然后获取相应的表 2 中的行。如果该列来自 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 = ?

两个表都代表类型层次结构并且始终一起加载。所以使用 SELECT 和 JOIN 加载对象是有意义的。加载两个表单独使用不会让查询优化器有机会找到最好的执行计划。但由于 UPDATE 语句只能更新一个表当对象加载时，这可能会导致死锁由另一笔交易更新。对象的更新通常会导致更新当对象的属性属于不同类型时，这两个表类型层次结构已更新。

我尝试向 SELECT 语句添加锁定提示，但这并没有成功改变问题。它只会导致 SELECT 语句中的死锁两条语句都尝试锁定表，并且一个 SELECT 语句获得锁定以与其他语句相反的顺序。也许有可能始终使用相同的语句加载更新数据，强制锁定以相同的顺序。这将防止两个事务之间出现死锁想要更新数据，但不会阻止仅读取的事务数据死锁需要有不同的 WHERE 条件。

到目前为止，唯一的解决办法似乎是读取可能无法获得锁根本不。在 SQL Server 2005 中，可以使用快照隔离来完成此操作。这SQL Server 2000 的唯一方法是使用 READ UNCOMMITED 隔离级别。

我想知道是否还有其他方法可以阻止 SQL Server造成这些僵局？

Answer 1

在快照隔离下，当读取器不阻止写入器时，这种情况永远不会发生。除此之外，没有办法阻止此类事情的发生。我在这里写了很多重现脚本:Reproducing deadlocks involving only one table

编辑:

我无权访问 SQL 2000，但我会尝试使用 sp_getapplock 序列化对该对象的访问，以便读取和修改永远不会同时运行。如果您无法使用 sp_getapplock，请推出您自己的互斥体。