Транзакции MariaDB и уровни изоляции для пользователей SQL Server

Эта страница объясняет, как работают транзакции в MariaDB, и выделяет основные различия между транзакциями MariaDB и SQL Server.

Обратите внимание, что транзакции XA обрабатываются совершенно по-другому и не рассматриваются на этой странице. См. Транзакции XA.

Отсутствующие возможности

Эти возможности SQL Server недоступны в MariaDB:

• Автономные транзакции;
• Распределённые транзакции.

Транзакции, движки хранения и бинарный журнал

В MariaDB транзакции по желанию реализуются движками хранения. По умолчанию используется движок хранения InnoDB, который полностью поддерживает транзакции. Другие транзакционные движки хранения включают MyRocks и TokuDB. Большинство движков хранения не являются транзакционными, поэтому их нельзя рассматривать как движки общего назначения.

Большая часть информации на этой странице относится к общему поведению сервера MariaDB или InnoDB. Для MyRocks и TokuDB обратитесь к соответствующим разделам базы знаний.

Запись в не-транзакционную таблицу в транзакции всё ещё может быть полезной. Причина в том, что на таблицу для всей транзакции приобретается блокировка метаданных, поэтому ALTER TABLE ставятся в очередь.

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

• В случае отката изменения в не-транзакционных движках не будут отменены. Мы получим предупреждение `1196`, которое об этом напоминает.
• Данные в транзакционных таблицах не могут быть изменены другими подключениями посредине транзакции, но данные в не-транзакционных таблицах могут.
• В случае сбоя, данные, зафиксированные в транзакционной таблице, всегда можно восстановить, но это не обязательно верно для не-транзакционных таблиц.

Если включён бинарный журнал, запись в разные транзакционные движки хранения в одной транзакции или запись в транзакционные и не-транзакционные движки внутри одной транзакции требует дополнительных действий для MariaDB. Он должен выполнить двухфазный протокол, чтобы гарантировать, что изменения в разных таблицах записываются в правильном порядке. Это влияет на производительность.

Синтаксис транзакций

Первый доступ для чтения или записи в таблицу InnoDB запускает транзакцию. Доступ к данным вне транзакции невозможен.

По умолчанию включена автоматическая фиксация, что означает, что транзакция фиксируется автоматически после каждой SQL-команды. Мы можем отключить её и вручную фиксировать транзакции:

SET SESSION autocommit = 0;
SELECT ... ;
DELETE ... ;
COMMIT;

Независимо от того, включена ли автоматическая фиксация, мы можем явно начать транзакции, и они не будут автоматически зафиксированы:

START TRANSACTION;
SELECT ... ;
DELETE ... ;
COMMIT;

BEGIN также может использоваться для запуска транзакции, но не работает в хранимых процедурах.

Транзакции только для чтения также доступны с использованием START TRANSACTION READ ONLY. Это небольшое улучшение производительности. MariaDB выдаст ошибку при попытке записи данных в середине транзакции только для чтения.

Только DML-команды транзакционные и могут быть отменены. Это может измениться в будущих версиях, см. MDEV-17567 - Атомарные DDL и MDEV-4259 - транзакционные DDL.

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

Откат также может быть вызван неявно при возникновении определённых ошибок.

Вы можете экспериментировать с транзакциями, чтобы проверить, в каких случаях они неявно фиксируются или отменяются. Система переменная in_transaction может помочь: она устанавливается в 1, когда транзакция в процессе, или в 0, когда транзакция не в процессе.

Этот раздел охватывает только базовый синтаксис транзакций. Доступно гораздо больше вариантов. Для получения дополнительной информации см. Транзакции.

Проверка ограничений

MariaDB поддерживает следующие ограничения:

• Первичные ключи
• УНИКАЛЬНЫЙ
• CHECK
• Внешние ключи

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

MariaDB делает по-другому: она всегда проверяет ограничения после каждого изменения строки. Есть случаи, когда такая политика заставляет некоторые команды завершаться с ошибкой, даже если эти команды работали бы в SQL Server.

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

SELECT id FROM customer;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+

UPDATE customer SET id = id + 1;
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

Причина, по которой это происходит, заключается в том, что в первую очередь MariaDB пытается изменить 1 на 2, но значение 2 уже присутствует в первичном ключе.

Решение — использовать этот нестандартный синтаксис:

UPDATE customer SET id = id + 1 ORDER BY id DESC;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

Изменение идентификаторов в обратном порядке не будет дублировать никакие значения.

Аналогичные проблемы могут возникнуть с CHECK ограничениями и внешними ключами. Для их решения можно использовать другой подход:

SET SESSION check_constraint_checks = 0;
-- run some queries
-- that temporarily violate a CHECK clause
SET SESSION check_constraint_checks = 1;

SET SESSION foreign_key_checks = 0;
-- run some queries
-- that temporarily violate a foreign key
SET SESSION foreign_key_checks = 1;

Последние решения временно отключают CHECK ограничения и внешние ключи. Обратите внимание, что, хотя это может решить практические проблемы, это опасно, потому что:

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

См. переменные системы check_constraint_checks и foreign_key_checks.

Уровни изоляции и блокировки

Для получения дополнительной информации об уровнях изоляции MariaDB см. SET TRANSACTION.

Блокирующие чтение

В MariaDB блокировки, приобретаемые при чтении, не зависят от уровня изоляции (за исключением одного случая, указанного ниже).

Как общее правило:

• Простые SELECT не блокируют, вместо этого они приобретают моментальные снимки.
• Чтобы при чтении приобрести общую блокировку, используйте SELECT ... LOCK IN SHARED MODE.
• Чтобы при чтении приобрести исключительную блокировку, используйте SELECT ... FOR UPDATE.

Изменение уровня изоляции

По умолчанию уровень изоляции в MariaDB — REPEATABLE READ. Это можно изменить с помощью переменной системы tx_isolation.

Приложения, разработанные для SQL Server и затем перенесённые в MariaDB, могут работать с READ COMMITTED без проблем. Использование более строгого уровня снизит масштабируемость. Чтобы использовать READ COMMITTED по умолчанию, добавьте следующую строку в файл конфигурации MariaDB:

tx_isolation = 'READ COMMITTED'

Также можно изменить уровень изоляции по умолчанию для текущей сессии:

SET SESSION tx_isolation = 'read-committed';

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

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Как реализованы уровни изоляции в MariaDB

MariaDB поддерживает следующие уровни изоляции:

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

Уровни изоляции MariaDB отличаются от SQL Server следующим образом:

• REPEATABLE READ не приобретает общие блокировки на все строки чтения, а также блокировку диапазона на пропущенных значениях, которые соответствуют условию WHERE.
• Невозможно изменить уровень изоляции посредине транзакции.
• Уровень изоляции SNAPSHOT не поддерживается. Вместо этого вы можете использовать START TRANSACTION WITH CONSISTENT SNAPSHOT для получения моментального снимка в начале транзакции. Это совместимо со всеми уровнями изоляции.

Вот пример использования WITH CONSISTENT SNAPSHOT:

-- session 1
SELECT * FROM t1;
+----+
| id |
+----+
|  1 |
+----+

SELECT * FROM t2;
+----+
| id |
+----+
|  1 |
+----+

START TRANSACTION WITH CONSISTENT SNAPSHOT;

-- session 2
INSERT INTO t1 VALUES (2);

-- session 1
SELECT * FROM t1;
+----+
| id |
+----+
|  1 |
+----+

-- session 2
INSERT INTO t2 VALUES (2);

-- session 1
SELECT * FROM t2;
+----+
| id |
+----+
|  1 |
+----+

Как видите, сессия 1 использует WITH CONSISTENT SNAPSHOT, поэтому она видит все таблицы такими, какими они были в начале транзакции.

Избегание ожидания блокировок

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

Для таких ожиданий существует тайм-аут, определённый переменной innodb_lock_wait_timeout. Если она установлена в 0, команды, которые сталкиваются с блокировкой строки, будут завершаться немедленно. При превышении тайм-аута MariaDB выдаёт следующую ошибку:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Важно отметить, что у этой переменной есть два ограничения (по дизайну):

• Она затрагивает только транзакционные команды, а не команды вроде ALTER TABLE или TRUNCATE TABLE.
• Она касается только блокировок строк. Она не устанавливает тайм-аут на блокировки метаданных или блокировки таблиц, приобретаемых, например, с помощью команды LOCK TABLES.

Однако, lock_wait_timeout можно использовать для блокировок метаданных.

Есть специальный синтаксис, который можно использовать с SELECT и некоторыми не-транзакционными командами, включая ALTER TABLE: оператор WAIT и NOWAIT. Этот синтаксис устанавливает тайм-аут в секундах для всех типов блокировок, включая блокировки строк, блокировки таблиц и блокировки метаданных. Например:

Session 1:
START TRANSACTION;
-- let's acquire a metadata lock
SELECT id FROM t WHERE 0;

Session 2:
DROP TABLE t WAIT 0;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Транзакции InnoDB

Типы блокировок InnoDB

Блокировки InnoDB классифицируются в зависимости от того, что именно они блокируют и какие операции они блокируют.

Первая классификация выглядит следующим образом:

• Блокировки записей (Record Locks) блокируют строку или, точнее, запись индекса.
• Блокировки промежутков (Gap Locks) блокируют интервал между двумя записями индекса. Обратите внимание, что индексы имеют виртуальные значения -Infinum и Infinum, поэтому блокировка промежутка может охватывать промежуток перед первой или после последней записи индекса.
• Блокировки следующей записи (Next-Key Locks) блокируют запись индекса и промежуток между ней и следующей записью. Они представляют собой комбинацию блокировок записей и блокировок промежутков.
• Блокировки с намерением вставки (Insert Intention Locks) являются блокировками промежутков, приобретаемыми перед вставкой новой строки.

Режимы блокировок следующие:

• Исключительные блокировки (Exclusive Locks, X) обычно приобретаются при записи, например, непосредственно перед удалением строки. Только одна исключительная блокировка может быть приобретена на ресурсе одновременно.
• Разделяемые блокировки (Shared Locks, S) могут быть приобретены при чтении. Несколько разделяемых блокировок могут быть приобретены одновременно (поскольку предполагается, что строки не изменятся при разделяемой блокировке), но они несовместимы с исключительными блокировками.
• Блокировки с намерением (IS, XS) приобретаются, когда нельзя приобрести исключительную или разделяемую блокировку. Когда блокировка на строке или промежутке освобождается, самая старая блокировка с намерением на этом ресурсе (если есть) преобразуется в блокировку X или S.

Для получения дополнительной информации см. Режимы блокировок InnoDB.

Схема информации

Запрос к information_schema — лучший способ увидеть, какие транзакции приобрели блокировки и какие транзакции ожидают освобождения блокировок.

В частности, проверьте следующие таблицы:

• INNODB_LOCKS: запросы на блокировки, которые еще не выполнены или блокируют другую транзакцию.
• INNODB_LOCK_WAITS: очереди запросов на получение блокировки.
• INNODB_TRX: информация обо всех текущих выполняемых транзакциях InnoDB, включая выполняемые запросы SQL.

Вот пример их использования.

-- session 1
START TRANSACTION;
UPDATE t SET id = 15 WHERE id = 10;

-- session 2
DELETE FROM t WHERE id = 10;

-- session 1
USE information_schema;
SELECT l.*, t.*
    FROM information_schema.INNODB_LOCKS l
    JOIN information_schema.INNODB_TRX t
        ON l.lock_trx_id = t.trx_id
    WHERE trx_state = 'LOCK WAIT' \G
*************************** 1. row ***************************
                   lock_id: 840:40:3:2
               lock_trx_id: 840
                 lock_mode: X
                 lock_type: RECORD
                lock_table: `test`.`t`
                lock_index: PRIMARY
                lock_space: 40
                 lock_page: 3
                  lock_rec: 2
                 lock_data: 10
                    trx_id: 840
                 trx_state: LOCK WAIT
               trx_started: 2019-12-23 18:43:46
     trx_requested_lock_id: 840:40:3:2
          trx_wait_started: 2019-12-23 18:43:46
                trx_weight: 2
       trx_mysql_thread_id: 46
                 trx_query: DELETE FROM t WHERE id = 10
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
          trx_is_read_only: 0
trx_autocommit_non_locking: 0

Тупиковые ситуации

InnoDB автоматически обнаруживает тупиковые ситуации. Поскольку это расходует время процессора, некоторые пользователи предпочитают отключить эту функцию, установив переменную innodb_deadlock_detect в значение 0. Если это сделано, заблокированные транзакции будут ожидать, пока они не превысят время ожидания innodb_lock_wait_timeout. Поэтому важно установить innodb_lock_wait_timeout в очень малое значение, например, в 1.

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

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Последнюю обнаруженную тупиковую ситуацию и прерванную транзакцию можно увидеть в выводе SHOW ENGINE InnoDB STATUS. Вот пример:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-12-23 18:55:18 0x7f51045e3700
*** (1) TRANSACTION:
TRANSACTION 847, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 46, OS thread handle 139985942054656, query id 839 localhost root Updating
delete from t where id = 10
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 40 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 847 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000034e; asc      N;;
 2: len 7; hex 760000019c0495; asc v      ;;

*** (2) TRANSACTION:
TRANSACTION 846, ACTIVE 25 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 39, OS thread handle 139985942361856, query id 840 localhost root Updating
delete from t where id = 11
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 40 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 846 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000034e; asc      N;;
 2: len 7; hex 760000019c0495; asc v      ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 40 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 846 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 8000000b; asc     ;;
 1: len 6; hex 00000000034f; asc      O;;
 2: len 7; hex 770000019d031d; asc w      ;;

*** WE ROLL BACK TRANSACTION (2)

Последняя обнаруженная тупиковая ситуация никогда не исчезает из вывода SHOW ENGINE InnoDB STATUS. Если вы не видите никакой, это означает, что MariaDB не обнаружила тупиковых ситуаций InnoDB с момента последней перезагрузки.

Другой способ отслеживания тупиковых ситуаций — установить innodb_print_all_deadlocks в значение 1 (значение по умолчанию — 0). InnoDB будет записывать все обнаруженные тупиковые ситуации в журнал ошибок.