Может ли операция INSERT привести к взаимоблокировке?

Предполагая, что:

Я использую REPEATABLE_READ или SERIALIZABLE изоляцию транзакций (блокировки сохраняются при каждом доступе к строке)
Мы говорим о нескольких потоках, одновременно обращающихся к нескольким таблицам.

У меня есть следующие вопросы:

Возможно ли, чтобы операция INSERT вызывала тупик? Если да, укажите подробный сценарий, демонстрирующий, как может возникнуть взаимоблокировка (например, Thread 1 делает это, Thread 2 делает это,..., тупик).
Для бонусных очков: ответьте на один и тот же вопрос для всех других операций (например, SELECT, UPDATE, DELETE).

UPDATE: 3. Для супер бонусных очков: как я могу избежать тупика в следующем сценарии?

Данные таблицы:

разрешений [id BIGINT PRIMARY KEY]
компании [id BIGINT PRIMARY KEY, name VARCHAR(30), permission_id BIGINT NOT NULL, FOREIGN KEY (permission_id) REFERENCES permissions(id))

Я создаю новую компанию следующим образом:

Разрешения INSERT INTO; - Вставляет permissions.id = 100
INSERT INTO компании (name, permission_id) VALUES ('Nintendo', 100); - Вкладыши company.id = 200

Я удалю компанию следующим образом:

SELECT permission_id FROM компании WHERE id = 200; - возвращает permission_id = 100
УДАЛИТЬ ОТ компаний WHERE id = 200;
УДАЛИТЬ ОТ разрешений WHERE id = 100;

В приведенном выше примере порядок блокировки INSERT является [разрешениями, компаниями], тогда как порядок блокировки DELETE - [компании, разрешения]. Есть ли способ исправить этот пример для изоляции REPEATABLE_READ или SERIALIZABLE?

Ответ 1

Как правило, все модификации могут вызывать тупик, а выбор не будет (дойти до этого). Так

Нет, вы не можете игнорировать их.
Вы можете несколько игнорировать выбор в зависимости от вашей базы данных и настроек, но другие будут давать вам взаимоблокировки.

Вам даже не нужны несколько таблиц.

Лучший способ создать тупик - сделать то же самое в другом порядке.

Примеры SQL Server:

create table A
(
    PK int primary key
)

Сессия 1:

begin transaction
insert into A values(1)

Сессия 2:

begin transaction    
insert into A values(7)

Сессия 1:

delete from A where PK=7

Сессия 2:

delete from A where PK=1

Вы получите тупик. Таким образом, доказанные вставки и удаления могут блокироваться.

Обновления аналогичны:

Сессия 1:

begin transaction    
insert into A values(1)
insert into A values(2)
commit

begin transaction
update A set PK=7 where PK=1

Сессия 2:

begin transaction
update A set pk=9 where pk=2    
update A set pk=8 where pk=1

Сессия 1:

update A set pk=9 where pk=2

Тупик!

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

SQL Server не будет блокировать SELECT, если вы используете SNAPSHOT ISOLATION. Oracle и я думаю, что Postgres никогда не будет блокировать SELECT (если у вас нет FOR UPDATE, который явно резервирует для обновления в любом случае).

Итак, в основном, я думаю, у вас есть несколько неправильных предположений. Я думаю, что доказал:

Обновления могут вызывать блокировки
Удаления могут вызывать блокировки
Вставки могут вызывать блокировки
Вам не нужно больше одной таблицы
Вам сделать требуется более одного сеанса

Вам просто нужно взять слово на SELECT;), но это будет зависеть от вашего БД и настроек.

Ответ 2

В дополнение к ответу LoztInSpace inserts может вызывать взаимоблокировки даже без присутствия deletes или updates. Все, что вам нужно, это уникальный индекс и порядок операций с обратным порядком.

Пример в Oracle:

create table t1 (id number);
create unique index t1_pk on t1 (id);

--thread 1 :
insert into t1 values(1);
--thread 2
insert into t1 values(2);
--thread 1 :
insert into t1 values(2);
--thread 2
insert into t1 values(1);  -- deadlock !

Ответ 3

Предположим, что у вас есть два отношения A и B и два пользователя X и Y. Таблица A WRITE Заблокирована пользователем X, а таблица B - WRITE, заблокированная Y. Затем следующий запрос даст вам мертвую блокировку, если он используется как пользователями X, так и Y.

Select * from A,B

Таким образом, операция Select может привести к тупиковой ситуации, если в нее входят операции объединения с несколькими таблицами. Обычно операции "Вставка" и "Удалить" связаны с отдельными отношениями. Поэтому они не могут вызывать тупик.