MySQL事务和事务的四种隔离级别 for INNODB

什么是事务

一组原子性的SQL语句，可以提交或回滚的原子工作单元 。当事务对数据库进行多次更改时，要么在提交事务后所有更改成功，要么在回滚事务时撤消所有更改。

事务特性

ACID特性

• A: atomicity（原子性）：整个事务中的所有操作要么全部成功，要么全部失败后回滚；
• C: consistency（一致性）：整个数据库总是从一个一致性状态转换为另一个一致性状态；
• I: isolation（隔离性）：一个事务所做的的操作在提交之前，是不被其它事务所见的；
• D: durability（持久性）：事务一旦提交，其所做的修改会永久保存于数据库中。

事务生命周期

事务管理

显式启动事务：

BEGIN
BEGIN WORK
START TRANSACTION

结束事务：

# 提交
COMMIT
# 回滚
ROLLBACK

注：只有事务型的存储引擎的DML语句才能支持此类操作

自动提交：

默认为1，为0时设为非自动提交
> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

set autocommit={1|0}

注：建议显式请求和提交事务，而不要使用“自动提交”功能。

事务支持保存点：

SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

查看事务：

# 查看当前的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
#查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
#查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

事务隔离级别

MySQL事务支持四种隔离级别，从上至下更加严格

MVCC和事务的隔离级别

MVCC（多版本并发控制机制）只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容,因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁

如何指定事务隔离级别

参考：https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tx_isolation

1.系统变量tx_isolation指定，默认为REPEATABLE-READ，有效范围：Global, Session

# 语法：
set tx_isolatioin='value';
# 有效值
READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ
SERIALIZABLE

2.服务器选项中指定

在/etc/my.cnf文件的[mysqld]块中使用transaction-isolation选项设置
[mysqld]
transaction-isolation = value

事务隔离级别验证

背景：通过两个终端显示启动两个事务来验证，用作验证的user表的表内容和表结构如下

mysql> desc user;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select * from user;
+----+-------+
| id | name  |
+----+-------+
|  2 | root  |
|  3 | admin |
+----+-------+
2 rows in set (0.00 sec)

1.READ UNCOMMITTED级别

2.READ COMMITTED级别

3.REPEATABLE READ级别

4.SERIALIZABLE级别

未提交的读事务阻塞修改事务（加读锁，但不阻塞读事务）

未提交的修改事务阻塞读事务（加写锁，其它事务的读、写都不可以执行）

注：此种事务隔离级别会导致并发性能差。

附加案例：如果一个事务产生了写锁，然后一直未结束事务，这样会导致其它事务的读、写都不可以执行，对于这种情况，可以找到未结束并且导致阻塞的事务，手动将其kill。

拿上面未提交的修改事务阻塞读事务的案例来验证

打开第三个会话执行以下操作

死锁