事务的四种隔离级别
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 晚安
网友评论