Mysql InnoDB事物隔离机制

1. 事务（Transaction）及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。

• 原性性(Actomicity)：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行
• 一致性(Consistent)：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以操持完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的
• 隔离性(Isolation)：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
• 持久性(Durable)：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

2. 默认是可重复读的（REPEATABLE READ)

3. 提供四个隔离级别，可以使用--transaction-isolation为所有连接指定事物隔离级别

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

4. 理论知识图谱

隔离级别	脏读(Dirty Read)	不可重复读(NonRepeatable Read)	幻读(Phantom Read)
未提交读(Read uncommitted)	是	是	是
已提交读(Read committed)	否	是	是
可重复读(Repeated Read)	否	否	是
可串行化(Serializable)	否	否	否

4. 隔离级别介绍

• 未提交读：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
• 已提交读：只能读取到已经提交的数据
• 可重复读：在同一个事物内重复读取数据能够保证数据一致性，但还是存在幻读
• 可串行化：每次读取都需要获得表级别的共享锁，读写互相阻塞

5.脏读，不可重复读， 幻读介绍

• 脏读： 当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。只有未提交读(Read uncommitted)会出现脏读的情况

session1:
MariaDB [blog]> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set (0.00 sec)
MariaDB [blog]> start transaction;
1 row in set (0.00 sec)
insert into articles(title, content) values("a", "zzz");
1 row in set (0.00 sec)

-----------------------------------------------------------------------------
session2:
MariaDB [blog]> set session transaction isolation level read uncommitted;
MariaDB [blog]> select * from articles;
+----+-------+---------+---------------------+---------------------+
| id | title | content | created_at          | updated_at          |
+----+-------+---------+---------------------+---------------------+
|  1 | a     | zzz     | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+----+-------+---------+---------------------+---------------------+
1 row in set (0.00 sec)
MariaDB [blog]> set session transaction isolation level REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)
MariaDB [blog]> select * from articles;
Empty set (0.00 sec)

• 不可重复读： 在一个事物内多次读取同一条数据，在此期间有另外一个事物对此数据进行了修改导致前者多次读取的数据内容不一致

session1:
MariaDB [blog]> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set (0.00 sec)
-----------------------------------------------------------------------------
session2:
MariaDB [blog]> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |
+------------------------+
1 row in set (0.00 sec)
MariaDB [blog]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [blog]> select * from articles where id = 2;
+----+-------+---------+---------------------+---------------------+
| id | title | content | created_at          | updated_at          |
+----+-------+---------+---------------------+---------------------+
|  2 | z     | zzz     | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+----+-------+---------+---------------------+---------------------+
1 row in set (0.00 sec)
-----------------------------------------------------------------------------
session1:
MariaDB [blog]> update articles set title = "b" where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-----------------------------------------------------------------------------
session2:
MariaDB [blog]> select * from articles where id = 2;
+----+-------+---------+---------------------+---------------------+
| id | title | content | created_at          | updated_at          |
+----+-------+---------+---------------------+---------------------+
|  2 | b     | zzz     | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+----+-------+---------+---------------------+---------------------+
1 row in set (0.00 sec)
-----------------------------------------------------------------------------
session1:
MariaDB [blog]> start transaction;
Query OK, 0 rows affected (0.00 sec)
MariaDB [blog]> select * from articles where id = 2;
+----+-------+---------+---------------------+---------------------+
| id | title | content | created_at          | updated_at          |
+----+-------+---------+---------------------+---------------------+
|  2 | b     | zzz     | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+----+-------+---------+---------------------+---------------------+
1 row in set (0.00 sec)
-----------------------------------------------------------------------------
session2:
MariaDB [blog]> update articles set title = 'a' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-----------------------------------------------------------------------------
session1:
MariaDB [blog]> select * from articles where id = 2;
+----+-------+---------+---------------------+---------------------+
| id | title | content | created_at          | updated_at          |
+----+-------+---------+---------------------+---------------------+
|  2 | b     | zzz     | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+----+-------+---------+---------------------+---------------------+
1 row in set (0.00 sec)

• 幻读: 一个事物对表中所有数据进行了修改，再去查询时由于其他事物新增了数据，导致查到的数据还存在未修改的

session1:
MariaDB [blog]> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set (0.00 sec)
MariaDB [blog]> start transaction;
Query OK, 0 rows affected (0.00 sec)
-----------------------------------------------------------------------------
session2:
MariaDB [blog]> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
MariaDB [blog]> start transaction;
Query OK, 0 rows affected (0.00 sec)
MariaDB [blog]> update articles set title = 'zzz';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
MariaDB [blog]> select * from articles;
+----+-------+---------+---------------------+---------------------+
| id | title | content | created_at          | updated_at          |
+----+-------+---------+---------------------+---------------------+
|  2 | zzz   | zzz     | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+----+-------+---------+---------------------+---------------------+
1 row in set (0.00 sec)
-----------------------------------------------------------------------------
session1:
MariaDB [blog]> insert into articles(title) values("as");
Query OK, 1 row affected, 2 warnings (0.01 sec)
-----------------------------------------------------------------------------
session2:
MariaDB [blog]> select * from articles;
+----+-------+---------+---------------------+---------------------+
| id | title | content | created_at          | updated_at          |
+----+-------+---------+---------------------+---------------------+
|  2 | zzz   | zzz     | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
|  9 | as    | NULL    | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+----+-------+---------+---------------------+---------------------+
2 rows in set (0.00 sec)