MySQL интерпретирует SERIALIZABLE менее строго, чем PostgreSQL. Это правильно?

Когда используешь SERIALIZABLE транзакции для реализации шаблона вставки значения в базу данных, только если он еще не существует, я наблюдал существенную разницу между MySQL и PostgreSQL в их определении SERIALIZABLE уровень изоляции.

Рассмотрим следующую таблицу:

CREATE TABLE person (
    person_id INTEGER PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR NOT NULL
);

И следующий код вставки запускается одновременно на двух соединениях:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT person_id FROM person WHERE name = 'Bob Ross';
-- sync point: both transactions should run through here before proceeding to
-- demonstrate the effect

-- 0 results, so we will insert
INSERT INTO person (name) VALUES ('Bob Ross');
SELECT last_insert_id();
COMMIT;

В PostgreSQL (после соответствующей трансляции SQL) эффект, как я и ожидал, может быть успешным - только одна из транзакций. Это согласуется с моим пониманием SERIALIZABLE, как описано в PostgreSQL, и другими источниками, цитирующими из стандарта ANSI: существует последовательное выполнение транзакций, которое будет производить тот же эффект. Последовательное выполнение этих двух транзакций не возвращает 0 результатов поиска, а затем добавляет запись.

В MySQL 5.7 обе транзакции завершаются успешно, и в таблице есть 2 записи "Боба Росса". Документация MySQL определяет SERIALIZABLE с точки зрения запрета грязного чтения, неповторимых чтений и фантомных чтений; это не делает ссылки на существование последовательного выполнения.

SQLite также правильно предотвращает двойную вставку, по крайней мере, в режиме по умолчанию, благодаря своей консервативной стратегии блокировки.

Мой вопрос: правильное ли поведение MySQL в этом случае или это нарушает стандарт SQL, позволяя обеим транзакциям успешно выполняться? Я подозреваю, что ответ может зависеть от определения "эффекта" - наблюдает ли пустой набор результатов из первого SELECT считать "эффект" для целей двух последовательных исполнений, имеющих одинаковый эффект?

Несколько других комментариев, чтобы помочь охватить этот вопрос:

  • Я знаю, что могу добиться желаемого поведения в MySQL, выполнив сначала вставку с ON CONFLICT IGNORE, а затем делать выбор. Я пытаюсь понять, почему эквивалентный стандартный SQL не демонстрирует одинаковое поведение в обоих движках.
  • Я знаю, что я мог бы, вероятно, также исправить это, установив уникальное ограничение на name поле, которое в любом случае, возможно, будет лучшей моделью данных. Но основной вопрос все еще остается: почему обе транзакции успешны?
Источник

1 ответ

Решение

Стандарт SQL говорит в главе 4.35.4 Уровни изоляции SQL-транзакций (выделено мое):

Выполнение параллельных SQL-транзакций на уровне изоляции SERIALIZABLE гарантированно сериализуемо. Сериализуемое выполнение определяется как выполнение операций одновременного выполнения SQL-транзакций, которые производят тот же эффект, что и некоторое последовательное выполнение тех же SQL-транзакций. Последовательное выполнение - это выполнение, в котором каждая SQL-транзакция завершается до начала следующей SQL-транзакции.

Чуть дальше, это продолжает путать проблему:

Уровень изоляции определяет вид явлений, которые могут возникнуть при выполнении параллельных SQL-транзакций. Возможны следующие явления:

[пропуск определения P1 ("грязное чтение"), P2 ("неповторяемое чтение") и P3 ("фантомное чтение ") ]

Четыре уровня изоляции гарантируют, что каждая SQL-транзакция будет выполнена полностью или не выполнена вообще, и что обновления не будут потеряны. Уровни изоляции различны по отношению к явлениям P1, P2 и P3. В таблице 8 "Уровни изоляции транзакций SQL и три явления" указаны явления, которые возможны и невозможны для данного уровня изоляции.

+------------------+--------------+--------------+--------------+ 
| Level            | P1           | P2           | P3           |
+------------------+--------------+--------------+--------------+
| READ UNCOMMITTED | Possible     | Possible     | Possible     |
+------------------+--------------+--------------+--------------+
| READ COMMITTED   | Not Possible | Possible     | Possible     |
+------------------+--------------+--------------+--------------+
| REPEATABLE READ  | Not Possible | Not Possible | Possible     |
+------------------+--------------+--------------+--------------+
| SERIALIZABLE     | Not Possible | Not Possible | Not Possible |
+------------------+--------------+--------------+--------------+

ПРИМЕЧАНИЕ 53 - Исключение этих явлений для SQL-транзакций, выполняемых на уровне изоляции SERIALIZABLE, является следствием требования сериализации таких транзакций.

Эта формулировка имела печальное следствие, что многие разработчики решили, что достаточно исключить чтение по директивам, неповторяющиеся чтения и фантомные чтения, чтобы правильно реализовать SERIALIZABLE уровень изоляции, хотя в примечании разъясняется, что это не определение, а следствие определения.

Поэтому я бы сказал, что MySQL не прав, но он не одинок: база данных Oracle интерпретирует SERIALIZABLE таким же образом.

Источник

Я не могу воспроизвести это в MySQL 5.7. Другая транзакция всегда получает ошибку:

ОШИБКА 1213 (40001): обнаружен тупик при попытке получить блокировку;

Причина в том, что SELECT не использует индексированный столбец в WHERE-части, поэтому он устанавливает s-блокировки для каждой найденной строки, gap-s-lock для каждого разрыва между найденными строками и блокировки следующего ключа на положительную бесконечность после найден последний ряд Таким образом, в этой ситуации одновременные вставки невозможны.

Одна из возможных причин полученных вами результатов может быть такова:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Он устанавливает уровень изоляции только для следующей транзакции. Если после этого вы выполнили хотя бы один SELECT, уровень изоляции вернется к нормальному (REPEATABLE READ).

Лучше использовать

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Источник
Другие вопросы по тегам