MySql: правильный уровень изоляции транзакции, чтобы использовать для увеличения числа

Question

Предположим, что есть таблица в БД, например:

id                                       code
a8e09395-771c-4c6b-bb49-4921eeaf3927    2018-1
726b1390-b502-11e8-96f8-529269fb1459    2018-2
7a7ac7a6-b502-11e8-96f8-529269fb1459    2018-3
81758ea6-b502-11e8-96f8-529269fb1459    2019-1

Предположим, есть несколько клиентов, пишущих в эту таблицу.

Для столбца "код" мы хотим убедиться, что он следует строгому шаблону "год этого года".

Каков правильный уровень изоляции транзакции, который должны использовать все клиенты?

---- обновление ----2018-09-11 11:31:24---------

START TRANSACTION;

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET @code = (SELECT CODE
             FROM hey
             WHERE id = 123);

UPDATE hey
SET code = @code + 1
WHERE id = 123;

COMMIT;

Сделал быстрый тест с вышеуказанной транзакцией.

Я запустил 2 консоли, затем запустил приведенный выше код и запустил их обе в строку, которая читает столбец кода.

Затем заставьте одного из них обновить столбец кода, он будет ждать блокировки.

Затем я заставляю другой обновлять столбец кода, он блокируется и выполняется откат.

Теперь блокировка первой разрешена и она может быть зафиксирована.

Похоже, эта изоляция транзакций может помешать им наступить друг другу на ногу, верно?

Answer 1

Вам нужно решить это с блокировкой.

Неважно, какой уровень изоляции транзакции.

За один сеанс:

mysql1> begin;
mysql1> select max(code) from mytable where code like '2018-%' for update;

Выход:

+-----------+
| max(code) |
+-----------+
| 2017-3    |
+-----------+

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

mysql2> begin;
mysql2> select max(code) from mytable where code like '2018-%' for update;
(waits for lock)

В первом сеансе используйте возвращаемое значение select для вычисления следующего значения. Затем вставьте следующую строку и подтвердите.

mysql1> insert into mytable values (uuid(), '2018-4');
mysql1> commit;

Второй сеанс возвращается сразу после коммита в первом сеансе. Он правильно возвращает новый максимальный код:

+-----------+
| max(code) |
+-----------+
| 2017-4    |
+-----------+

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

Любая транзакция изоляции будет работать, если вы используете FOR UPDATE для блокировки строк и обеспечения последовательной работы транзакций.