При выполнении оператора SELECT с СОЕДИНЕНИЕМ двух таблиц SQL Server, по-видимому, блокирует обе таблицы оператора по отдельности. Например, по такому запросу:
SELECT ...
FROM
table1
LEFT JOIN table2
ON table1.id = table2.id
WHERE ...
Я узнал, что порядок блокировок зависит от условия WHERE. Оптимизатор запросов пытается создать план выполнения, который считывает ровно столько строк, сколько необходимо. Таким образом, если условие WHERE содержит столбец table1, оно сначала получит строки результата из table1, а затем получит соответствующие строки из table2. Если столбец из таблицы2, он сделает это наоборот. Более сложные условия или использование индексов также могут повлиять на решение оптимизатора запросов.
Когда данные, считанные оператором, должны быть обновлены позже в транзакции с операторами 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 могут обновлять только одну таблицу за раз, это может привести к взаимоблокировкам, когда объект загружается, когда объект обновляется другой транзакцией. Обновления объектов часто вызывают UPDATE в обеих таблицах, когда обновляются свойства объекта, принадлежащие к разным типам иерархии типов.
Я попытался добавить подсказки блокировки в оператор SELECT, но это не меняет проблемы. Это просто вызывает взаимоблокировку в операторах SELECT, когда оба оператора пытаются заблокировать таблицы, и один оператор SELECT получает блокировку в порядке, противоположном другому оператору. Возможно, можно было бы загружать данные для обновлений всегда с одним и тем же оператором, заставляя блокировки быть в одном и том же порядке. Это предотвратит взаимоблокировку между двумя транзакциями, которые хотят обновить данные, но не предотвратит транзакцию, которая только считывает данные, до взаимоблокировки, которая должна иметь разные условия WHERE.
Единственный обходной путь, поэтому пока кажется, что чтение может вообще не блокироваться. В SQL Server 2005 это можно сделать с помощью SNAPSHOT ISOLATION. Единственным способом для SQL Server 2000 было бы использование уровня изоляции READ UNCOMMITED.
Я хотел бы знать, есть ли другая возможность предотвратить эти взаимоблокировки со стороны SQL Server?
