"Выбор для обновления" предотвращает вставку других подключений, когда строка отсутствует

Мне интересно, будет ли запрос select for update блокировать несуществующую строку.

Таблица FooBar с двумя столбцами, foo и bar, foo имеет уникальный индекс

Возможно ли, что вставка вызовет нарушение индекса или предотвратит ли это select for update?

Заинтересован в поведении на SQLServer (2005/8), Oracle и MySQL.

Ответы

Ответ 1

В Oracle, SELECT... FOR UPDATE не влияет на несуществующую строку (оператор просто вызывает исключение No Data Found). Инструкция INSERT предотвращает дублирование значений уникальных/первичных ключей. Любые другие транзакции, пытающиеся вставить одни и те же значения ключа, будут блокироваться до тех пор, пока не произойдет первая транзакция (после чего блокированная транзакция получит дублируемую ошибку ключа) или откат (когда блокированная транзакция продолжается).

Ответ 2

MySQL

ВЫБРАТЬ... ДЛЯ ОБНОВЛЕНИЯ с ОБНОВЛЕНИЕМ

Используя транзакции с InnoDB (автоматическая фиксация отключена), SELECT... FOR UPDATE позволяет одному сеансу временно блокировать определенную запись (или записи), чтобы ни один другой сеанс не мог ее обновить. Затем в рамках одной и той же транзакции сеанс может фактически выполнить UPDATE для той же записи и зафиксировать или откатить транзакцию. Это позволит вам заблокировать запись, чтобы никакой другой сеанс не мог ее обновить, а вы, возможно, занимаетесь другой бизнес-логикой.

Это достигается с помощью блокировки. InnoDB использует индексы для блокировки записей, поэтому блокировка существующей записи кажется простой - просто заблокируйте индекс для этой записи.

ВЫБРАТЬ... ДЛЯ ОБНОВЛЕНИЯ с INSERT

Однако, чтобы использовать SELECT... FOR UPDATE с INSERT, как вы блокируете индекс для записи, которая еще не существует? Если вы используете уровень изоляции по умолчанию REPEATABLE READ, InnoDB также будет использовать блокировки пробелов. Пока вы знаете id (или даже диапазон идентификаторов) для блокировки, InnoDB может заблокировать пробел, поэтому никакая другая запись не может быть вставлена в этот пробел, пока мы не закончим с этим.

Если бы ваш столбец id столбцом с автоинкрементом, то SELECT... FOR UPDATE с INSERT INTO был бы проблематичным, потому что вы не знали бы, каким был новый id пока не вставили его. Однако, поскольку вы знаете id, который хотите вставить, будет работать SELECT... FOR UPDATE с INSERT.

ПРЕДОСТЕРЕЖЕНИЕ

На уровне изоляции по умолчанию SELECT... FOR UPDATE для несуществующей записи не блокирует другие транзакции. Таким образом, если две транзакции выполняют SELECT... FOR UPDATE для одной и той же несуществующей индексной записи, они обе получат блокировку, и ни одна из транзакций не сможет обновить запись. На самом деле, если они попытаются, будет обнаружен тупик.

Поэтому, если вы не хотите иметь дело с тупиком, вы можете просто сделать следующее:

Вставить в...

Начните транзакцию и выполните INSERT. Выполните свою бизнес-логику и либо подтвердите, либо откатите транзакцию. Как только вы сделаете INSERT для несуществующего индекса записи в первой транзакции, все другие транзакции будут блокироваться, если они попытаются INSERT запись с тем же уникальным индексом. Если вторая транзакция попытается вставить запись с тем же индексом после того, как первая транзакция совершит вставку, то она получит ошибку "дубликат ключа". Обращайтесь соответственно.

ВЫБРАТЬ... БЛОКИРОВАТЬ В РЕЖИМЕ РАЗДЕЛА

Если вы выбрали с помощью LOCK IN SHARE MODE перед INSERT, если предыдущая транзакция вставила эту запись, но еще не зафиксировала, SELECT... LOCK IN SHARE MODE будет блокироваться до завершения предыдущей транзакции.

Таким образом, чтобы уменьшить вероятность дублирования ошибок ключа, особенно если вы удерживаете блокировки некоторое время, выполняя бизнес-логику перед их фиксацией или откатом:

SELECT bar FROM FooBar WHERE foo =? LOCK FOR UPDATE
Если записи не возвращены, то
INSERT INTO FooBar (foo, bar) VALUES (?,?)

Ответ 3

В Oracle:

Сессия 1

create table t (id number);
alter table t add constraint pk primary key(id);

SELECT *
FROM t
WHERE id = 1
FOR UPDATE;
-- 0 rows returned
-- this creates row level lock on table, preventing others from locking table in exclusive mode

Сессия 2

SELECT *
FROM t 
FOR UPDATE;
-- 0 rows returned
-- there are no problems with locking here

rollback; -- releases lock


INSERT INTO t
VALUES (1);
-- 1 row inserted without problems

Ответ 4

Я написал подробный анализ этой вещи на SQL Server: Разработка модификаций, которые вызывают Concurrency

Во всяком случае, вам нужно использовать уровень изоляции SERIALIZABLE, и вам действительно нужно стресс-тест.

Ответ 5

SQL Server имеет только FOR UPDATE как часть курсора. И это относится только к операторам UPDATE, которые связаны с текущей строкой в курсоре.

Итак, FOR UPDATE не имеет отношения к INSERT. Поэтому, я думаю, ваш ответ в том, что он не применим в SQL Server.

Теперь возможно смоделировать поведение FOR UPDATE с транзакциями и стратегиями блокировки. Но это может быть больше, чем вы ищете.