Mysql事务隔离级别

事务的四种隔离级别

READ UNCOMMITTED（读未提交）

查看当前的隔离级别
select @@tx_isolation;

SET @@session.transaction_isolation = 'READ-UNCOMMITTED';
create database test;
use test;

create table test(
  id int primary key,
  age int not null default 0
  );

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
| age   | int(11) | NO   |     | 0       |       |
+-------+---------+------+-----+---------+-------+

insert into test(id,age) values(1,12);
mysql> select * from test;
+----+-----+
| id | age |
+----+-----+
|  1 |  12 |
+----+-----+
1 row in set (0.00 sec)

---------------------------终端一------------------------------------

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

mysql> update test set age = 21 where id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0


---------------------------终端二------------------------------------
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----+-----+
| id | age |
+----+-----+
|  1 |  21 |
+----+-----+
1 row in set (0.01 sec)


---------------------------结果------------------------------------

最后一步读取到了 mysql 终端 1 中未提交的事务（没有 commit 提交动作），即产生了脏读，
大部分业务场景都不允许脏读出现，但是此隔离级别下数据库的并发是最好的。

READ COMMITTED（读提交）

SET @@session.transaction_isolation = 'READ-COMMITTED';

---------------------------终端一------------------------------------
1) 步骤一
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set age = 11  where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

2) 步骤二
mysql> commit;
Query OK, 0 rows affected (0.10 sec)

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

---------------------------终端二------------------------------------

1) 步骤一
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

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

2) 步骤二
mysql> select * from test;
+----+-----+
| id | age |
+----+-----+
|  1 |  11 |
+----+-----+
1 row in set (0.01 sec)
---------------------------结果------------------------------------

mysql 终端 2 在开启了一个事务之后，在第一次读取 test 表（此时 mysql 终端 1 的事务还未提交）
时 age 为 21，在第二次读取 test 表（此时 mysql 终端 1 的事务已经提交）时 age 已经变为 11，
说明在此隔离级别下已经读取到已提交的事务。

REPEATABLE READ（可重复读）Mysql默认

SET @@session.transaction_isolation = 'REPEATABLE-READ';

---------------------------终端一------------------------------------
1) 步骤一
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

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

mysql> update test set age = 12  where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

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

---------------------------终端二------------------------------------
1) 步骤一
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

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

---------------------------结果------------------------------------

mysql 终端 2 在开启了一个事务之后，在第一次读取 test 表（此时 mysql 终端 1 的事务还未提交）
时 age 为 11，在第二次读取 test 表（此时 mysql 终端 1 的事务已经提交）时 age 仍然为 11，
说明在此隔离级别下MySQL 称之为幻读

SERIALIZABLE（序列化）

---------------------------终端一------------------------------------
mysql> begin ;
Query OK, 0 rows affected (0.00 sec)

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

---------------------------终端二------------------------------------
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set age = 10  where id = 1;

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

---------------------------结果------------------------------------

终端一执行查询语句后处于锁定的状态，只有commit或者rollback之后，终端二的命令才可以执行，
否则会一直卡住，直到超时，其中超时参数是由 innodb_lock_wait_timeout 控制

2020.03.10 23:35 晚安