Аналитический и практический уровень изоляции транзакций MySQL

1. Представление уровня изоляции MySQL

• Просмотр уровня изоляции вызова

SELECT @@session.tx_isolation;
SELECT @@tx_isolation;

• Просмотр уровня изоляции системы

SELECT @@global.tx_isolation;

2. Модификация уровня изоляции MySQL

• Уровень изоляции MySQL по умолчанию — REPEATABLE READ.
• Измените уровень изоляции в файле my.inf.

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}

• 用户可以用SET TRANSACTION语句改变单个会话或者所有新进连接的隔离级别。 Синтаксис следующий:

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

3. Четыре уровня изоляции MySQL

уровень изоляции	Грязное чтение	Неповторяемое чтение	Фантомное чтение
Читайте незарегистрированные	возможный	возможный	возможный
Чтение зафиксировано	невозможно	возможный	возможный
Повторяемое чтение	невозможно	невозможно	возможный
Сериализуемый (Сериализуемый)	невозможно	невозможно	невозможно

3.1 Незафиксированное чтение

• Грязные чтения разрешены, что означает, что одна транзакция может прочитать незаконные данные по другой транзакции

3.2. Представлено для ознакомления

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

3.3 Повторяемое чтение

• фантомное чтение

3.4 Сериализуемый

• Полная сериализация, вам нужно получить блокировку общего доступа на уровне таблицы, блок чтения и записи каждый раз, когда вы читаете

4. Пример операции

• Создайте новую таблицу для тестирования

CREATE TABLE `test` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名字',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测试';

4.1 Грязные чтения

• Когда транзакция обращается к части данных и выполняется модификация. Другая транзакция считывает измененные данные и использует их.
• sessoin1 (вставить данные без совершения транзакции)

mysql> SELECT @@session.tx_isolation; // 查询会话隔离级别可重复读
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set, 1 warning (0.04 sec)

mysql> SELECT @@tx_isolation; //查询系统隔离级别为可重复读
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql> start transaction; //开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(`name`) values("qiu"); //插入数据成功，此时事务还没有提交
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | qiu  |
+----+------+
1 row in set (0.00 sec)

• sessoin2 (повторяемое чтение, доказательство того, что грязного чтения не будет)

mysql> SELECT @@session.tx_isolation; //会话隔离级别为可重复读
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT @@tx_isolation; //系统隔离级别为可重复读
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from test; //查询不到 sessoin1 未提交的数据，不会出现脏读现象
Empty set (0.00 sec)

• SESSOIN3 (чтобы сделать чтение в грязном чтении)

mysql> SELECT @@session.tx_isolation;//会话隔离级别为未提交读
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |   --------读到了 session1 未提交的数据，出现脏读现象
+------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from test;//读到了 session1 未提交的数据，此为脏读
+----+------+
| id | name |
+----+------+
|  1 | qiu  |
+----+------+
1 row in set (0.00 sec)

4.2, неповторяемое чтение

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

• sessoin1 (первое чтение в транзакции)

mysql> SELECT @@session.tx_isolation; //隔离级别为提交读
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED         |
+------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> start transaction; //开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test; //开启事务内的第一次查询
+----+------+
| id | name |
+----+------+
|  2 | qiu  |
+----+------+
1 row in set (0.01 sec)

• sessoin2

mysql> SELECT @@session.tx_isolation;//隔离级别为可重复读
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> start transaction; //开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  2 | qiu  |
+----+------+
1 row in set (0.01 sec)

mysql> insert into test(`name`) values ("hello"); //在sessoin1第一次查询后修改了数据
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  2 | qiu   |
|  3 | hello |
+----+-------+
2 rows in set (0.00 sec)

mysql> commit; //提交事务
Query OK, 0 rows affected (0.01 sec)

• sessoin1 (второе чтение в транзакции)

mysql> select * from test; //在事务内第二次读，读到了 sessoin2 提交的数据
+----+-------+
| id | name  |
+----+-------+
|  2 | qiu   |   ---------------READ-COMMITTED级别出现不可重复读现象
|  3 | hello |
+----+-------+
2 rows in set (0.00 sec)

4.3 Повторяемое чтение

• Проверка повторяемости операций чтения на уровне REPEATABLE-READ
• sessoin1 (первое чтение в транзакции)

mysql> SELECT @@session.tx_isolation;//隔离级别为可重复读
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> start transaction; //开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  2 | qiu   |
|  3 | hello |
+----+-------+
2 rows in set (0.00 sec)

• sessoin2

mysql> SELECT @@session.tx_isolation; //隔离级别为可重复读
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  2 | qiu   |
|  3 | hello |
+----+-------+
2 rows in set (0.00 sec)

mysql> insert into test (`name`) values ("hi"); //sessoin1 第一次读之后改变数据
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  2 | qiu   |
|  3 | hello |
|  4 | hi    |
+----+-------+
3 rows in set (0.00 sec)

mysql> commit; //提交事务
Query OK, 0 rows affected (0.00 sec)

• sessoin1 (второе чтение в транзакции)

mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  2 | qiu   |
|  3 | hello | -------------sessoin1 没有读到 sessoin2 提交的数据，出现可重复读现象
+----+-------+
2 rows in set (0.00 sec)

4.4 Фантомное чтение

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

• Феномен фантомного чтения 1:

session1:                                                   session2:
mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set, 2 warnings (0.00 sec)
                                                            mysql> start transaction;     
                                                            Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;                             
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
Empty set (0.00 sec)

                                                            mysql> insert into test (`id`, `name`) values (1, "hi~~~");
                                                            Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
Empty set (0.00 sec)

                                                            mysql> commit;
                                                            Query OK, 0 rows affected (0.01 sec)

mysql> select * from test;
Empty set (0.00 sec)

mysql> insert into test (`id`, `name`) values (1, "hello");
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
//what fuck ？？？刚刚查询，告诉我没有数据。等我插入的时候就告诉我主键冲突了。此乃幻读现象

• Феномен фантомного чтения 2:

session1：                               session2：
mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)

                                       mysql> start transaction;
                                       Query OK, 0 rows affected (0.01 sec)

mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  1 | hi~~~ |
+----+-------+
1 row in set (0.00 sec)

                                      mysql> insert into test (`id`, `name`) values (2, "hello~~");
                                      Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  1 | hi~~~ |
+----+-------+
1 row in set (0.04 sec)

                                      mysql> commit;
                                      Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  1 | hi~~~ |
+----+-------+
1 row in set (0.00 sec)

mysql> update test set name = "up";
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0
//what fuck ？？？刚出查询不是只有一条数据吗？怎么更新了两条。此乃幻读现象

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | up   |
|  2 | up   |
+----+------+
2 rows in set (0.00 sec)

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

4.5, замок

• Предотвратите фантомное чтение, заблокировав

session1：                                               session2:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

                                                        mysql> start transaction;
                                                        Query OK, 0 rows affected (0.00 sec)

//加锁锁住了 id <= 1 的范围
mysql> select * from test where id <= 1 for update;
+----+------+
| id | name |
+----+------+
|  1 | up   |
+----+------+
1 row in set (0.18 sec)
                                                        //id 不在锁内，允许插入
                                                        mysql> insert into test (`id`, `name`) values (3, "lock");
                                                        Query OK, 1 row affected (0.15 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | up   |
|  2 | up   |
+----+------+
2 rows in set (0.01 sec)
                                                        //id = 1 已经加了写锁，事务等待锁释放
                                                        mysql> insert into test(`id`, `name`) values (1, "lock");
                                                        ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | up   |
|  2 | up   |
+----+------+
2 rows in set (0.00 sec)

                                                        mysql> commit;
                                                        Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | up   |
|  2 | up   |
+----+------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | up   |
|  2 | up   |
|  3 | lock |  ------------session2 插入的数据
+----+------+
4 rows in set (0.00 sec)

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

session1：                               session2:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

                                        mysql> start transaction;
                                        Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | up   |
+----+------+
4 rows in set (0.01 sec)

                                        mysql> insert into test (`id`, `name`) values (7, "hello");
                                        Query OK, 1 row affected (0.00 sec)

                                        mysql> commit;
                                        Query OK, 0 rows affected (0.08 sec)


mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | up   |
+----+------+
4 rows in set (0.00 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | up   |
+----+------+
4 rows in set (0.00 sec)

//读到了 session2 提交的数据
mysql> select * from test lock in share mode;
+----+-------+
| id | name  |
+----+-------+
|  1 | up    |
|  7 | hello |
+----+-------+
5 rows in set (0.00 sec)

//读到了 session2 提交的数据
mysql> select * from test for update;
+----+-------+
| id | name  |
+----+-------+
|  1 | up    |
|  7 | hello |
+----+-------+
5 rows in set (0.00 sec)

//读不到 session2 提交的数据
mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | up   |
+----+------+
4 rows in set (0.00 sec)

• Повторяемые чтения и фиксированные чтения по своей сути противоречат друг другу. Гарантируется повторяемость чтений, а фиксации других транзакций не могут быть прочитаны; гарантируется фиксация чтения, а данные, прочитанные дважды, могут быть несогласованными.
• Уровень изоляции MySQL по умолчанию — это повторяемое чтение, которое может получать фиксации других транзакций, блокируя чтение.
• Повторяющееся чтение MySQL не может избежать фантомных чтений, которых можно избежать, добавив Next-Key Lock.
• Блокировка следующего ключа: заблокировать диапазон, в том числе сама запись.

Суммировать

• Каждый уровень изоляции базы данных решает проблему. Уровень изоляции базы данных, в свою очередь, увеличивается, а производительность, в свою очередь, также ухудшается. Использование READ-COMMITTED возможно в большинстве сред.

использованная литература

• Описание четырех уровней изоляции транзакций MySQL
• Механизм блокировки Innodb: Next-Key Lock

Обратите внимание на публичный аккаунт

• Можете обратить внимание на мой паблик [One Day of a Scholar], там вас ждет еще больше интересных и полезных знаний