mysql的事务隔离级别
一、查看mysql事务隔离级别：
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+二、修改mysql事务隔离级别：
set [ global | session ] transaction isolation level Read
uncommitted | Read committed | Repeatable | Serializable;- 如果选择global，意思是此语句将应用于之后的所有session，而当前已经存在的session不受影响。
- 如果选择session，意思是此语句将应用于当前session内之后的所有事务。
- 如果什么都不写，意思是此语句将应用于当前session内的下一个还未开始的事务
三、事务隔离级别分类：
1.read uncomitted(读未提交)
事务A可以读到事务B中未提交的数据。
mysql> start transaction; 开始事务A
mysql> select * from actor limit 195,203; -- 开始时的数据
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 196 | BELA | WALKEN | 2006-02-15 04:34:33 |
| 197 | REESE | WEST | 2006-02-15 04:34:33 |
| 198 | MARY | KEITEL | 2006-02-15 04:34:33 |
| 199 | JULIA | FAWCETT | 2006-02-15 04:34:33 |
| 200 | xu | sss | 2020-12-15 14:37:24 |
| 201 | xia | aaa | 2020-12-15 15:05:51 |
+----------+------------+-----------+---------------------+
mysql> select * from actor limit 195,203;-- 事务A读到事务B
插入未提交的数据
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 196 | BELA | WALKEN | 2006-02-15 04:34:33 |
| 197 | REESE | WEST | 2006-02-15 04:34:33 |
| 198 | MARY | KEITEL | 2006-02-15 04:34:33 |
| 199 | JULIA | FAWCETT | 2006-02-15 04:34:33 |
| 200 | xu | sss | 2020-12-15 14:37:24 |
| 201 | xia | aaa | 2020-12-15 15:05:51 |
| 202 | zhang | xiaoqiang | 2020-12-15 06:06:06 |
+----------+------------+-----------+---------------------+
mysql> select * from actor limit 195,203;--事务A读到事务B更
新未提交的数据
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 196 | BELA | WALKEN | 2006-02-15 04:34:33 |
| 197 | REESE | WEST | 2006-02-15 04:34:33 |
| 198 | MARY | KEITEL | 2006-02-15 04:34:33 |
| 199 | JULIA | FAWCETT | 2006-02-15 04:34:33 |
| 200 | xu | sss | 2020-12-15 14:37:24 |
| 201 | xia | aaa | 2020-12-15 15:05:51 |
| 202 | xia | xiaoqiang | 2020-12-15 15:20:20 |
+----------+------------+-----------+---------------------+
mysql> select * from actor limit 195,203; --事务A失去事务B删
除未提交的数据
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 196 | BELA | WALKEN | 2006-02-15 04:34:33 |
| 197 | REESE | WEST | 2006-02-15 04:34:33 |
| 198 | MARY | KEITEL | 2006-02-15 04:34:33 |
| 199 | JULIA | FAWCETT | 2006-02-15 04:34:33 |
| 200 | xu | sss | 2020-12-15 14:37:24 |
| 201 | xia | aaa | 2020-12-15 15:05:51 |
+----------+------------+-----------+---------------------+
mysql> commit; -- 提交事务A事务B
mysql> start transaction; -- 开始事务B
mysql> insert into actor values('202','zhang','xiaoqiang'
,'2020-12-15 06:06:06'); -- 事务B插入数据
mysql> update actor set first_name='xia' where actor_id=202;
--事务B更新数据
mysql> delete from actor where actor_id =202;-- 事务B删除数据
mysql> commit; -- 提交事务B- 读未提交可以读到其他事务更新的数据，出现"脏读"。
2.read committed(读已提交)
读已提交读的是最新一个快照的数据。
事务A
mysql> start transaction; -- 开启事务A
mysql> select * from actor limit 5; -- 事务A开启时的数据
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+--------------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
| 2 | xiaoqiang | WAHLBERG | 2020-12-15 09:57:13 |
| 3 | ED | CHASE | 2006-02-15 04:34:33 |
| 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 |
| 5 | JOHNNY | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
mysql> select * from actor limit 5; -- 事务B update操作但未提交
时事务A查到的数据
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+--------------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
| 2 | xiaoqiang | WAHLBERG | 2020-12-15 09:57:13 |
| 3 | ED | CHASE | 2006-02-15 04:34:33 |
| 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 |
| 5 | JOHNNY | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
mysql> select * from actor limit 5; --事务B 提交后事务A的数据
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+--------------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
| 2 | peiqi | WAHLBERG | 2020-12-15 09:59:55 |
| 3 | ED | CHASE | 2006-02-15 04:34:33 |
| 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 |
| 5 | JOHNNY | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
mysql> commit; -- 提交事务A事务B
mysql> start transaction; --开启事务B
mysql> select * from actor limit 5; -- 事务B开启时的数据
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+--------------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
| 2 | xiaoqiang | WAHLBERG | 2020-12-15 09:57:13 |
| 3 | ED | CHASE | 2006-02-15 04:34:33 |
| 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 |
| 5 | JOHNNY | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
-- 事务B进行update操作
mysql> update actor set first_name='peiqi' where actor_id=2;
mysql> select * from actor limit 5; -- 事务B更新操作后查到的数据
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+--------------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
| 2 | peiqi | WAHLBERG | 2020-12-15 09:59:55 |
| 3 | ED | CHASE | 2006-02-15 04:34:33 |
| 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 |
| 5 | JOHNNY | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
mysql> commit; -- 提交事务B- read committed 事务隔离级别实现了避免脏读的情况，但事务B更新的数据对事务A产生了影响，使得事务A两次读的数据不一样，所以read commited事务级别不可重复读。
3.repeatable read (可重复读)
可重复读读的是事务开始时的数据
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+事务A
mysql> start transaction; -- 开启事务A
mysql> select * from actor limit 195,201; -- 开启事务A时的数据
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 196 | BELA | WALKEN | 2006-02-15 04:34:33 |
| 197 | REESE | WEST | 2006-02-15 04:34:33 |
| 198 | MARY | KEITEL | 2006-02-15 04:34:33 |
| 199 | JULIA | FAWCETT | 2006-02-15 04:34:33 |
| 200 | THORA | TEMPLE | 2006-02-15 04:34:33 |
| 8888 | JERRY | zhenhao | 2020-12-15 10:52:23 |
+----------+------------+-----------+---------------------+
mysql> select * from actor limit 195,201; -- 事务B delete操作
但为提交时的数据
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 196 | BELA | WALKEN | 2006-02-15 04:34:33 |
| 197 | REESE | WEST | 2006-02-15 04:34:33 |
| 198 | MARY | KEITEL | 2006-02-15 04:34:33 |
| 199 | JULIA | FAWCETT | 2006-02-15 04:34:33 |
| 200 | THORA | TEMPLE | 2006-02-15 04:34:33 |
| 8888 | JERRY | zhenhao | 2020-12-15 10:52:23 |
+----------+------------+-----------+---------------------+
mysql> select * from actor limit 195,201; -- 事务B提交后事务A查
到的数据
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 196 | BELA | WALKEN | 2006-02-15 04:34:33 |
| 197 | REESE | WEST | 2006-02-15 04:34:33 |
| 198 | MARY | KEITEL | 2006-02-15 04:34:33 |
| 199 | JULIA | FAWCETT | 2006-02-15 04:34:33 |
| 200 | THORA | TEMPLE | 2006-02-15 04:34:33 |
| 8888 | JERRY | zhenhao | 2020-12-15 10:52:23 |
+----------+------------+-----------+---------------------+
mysql> commit; -- 提交事务A
mysql> select * from actor limit 195,201; -- 提交事务A后查到的
数据
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 196 | BELA | WALKEN | 2006-02-15 04:34:33 |
| 197 | REESE | WEST | 2006-02-15 04:34:33 |
| 198 | MARY | KEITEL | 2006-02-15 04:34:33 |
| 199 | JULIA | FAWCETT | 2006-02-15 04:34:33 |
| 200 | THORA | TEMPLE | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+事务B
mysql> start transaction; -- 开启事务B
mysql> select * from actor limit 195,201; -- 开启事务B时的数据
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 196 | BELA | WALKEN | 2006-02-15 04:34:33 |
| 197 | REESE | WEST | 2006-02-15 04:34:33 |
| 198 | MARY | KEITEL | 2006-02-15 04:34:33 |
| 199 | JULIA | FAWCETT | 2006-02-15 04:34:33 |
| 200 | THORA | TEMPLE | 2006-02-15 04:34:33 |
| 8888 | JERRY | zhenhao | 2020-12-15 10:52:23 |
+----------+------------+-----------+---------------------+
mysql> delete from actor where actor_id =8888;
-- 删除actor_id=8888的记录
mysql> select * from actor limit 195,201; -- 更新操作后事务B查
到的数据
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 196 | BELA | WALKEN | 2006-02-15 04:34:33 |
| 197 | REESE | WEST | 2006-02-15 04:34:33 |
| 198 | MARY | KEITEL | 2006-02-15 04:34:33 |
| 199 | JULIA | FAWCETT | 2006-02-15 04:34:33 |
| 200 | THORA | TEMPLE | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
mysql> commit; -- 提交事务B- repeatable read 读得到的数据是事务开始时的数据。上述例子事务B删除操作提交后，事务A读到的依然是事务开始时的数据。
- 避免读脏数据的同时，保证了可重复读。
repeatable read 可能会出现幻读。
事务A
mysql> start transaction; -- 开始事务A
mysql> select * from actor limit 195,203; -- 事务B提交后事务A
查到的数据
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 196 | BELA | WALKEN | 2006-02-15 04:34:33 |
| 197 | REESE | WEST | 2006-02-15 04:34:33 |
| 198 | MARY | KEITEL | 2006-02-15 04:34:33 |
| 199 | JULIA | FAWCETT | 2006-02-15 04:34:33 |
| 200 | xu | sss | 2020-12-15 14:37:24 |
| 202 | fang | qian | 2020-12-15 14:39:34 |
| 203 | xu | sss | 2020-12-15 14:37:24 |
+----------+------------+-----------+---------------------+
mysql> update actor set last_name='new' where actor_id=202;
-- 事务A更新数据
mysql> select * from actor limit 195,203; -- 从此处可以看出，
事务A更新数据时，将事务B更新的数据更新进来了，出现了幻读。
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 196 | BELA | WALKEN | 2006-02-15 04:34:33 |
| 197 | REESE | WEST | 2006-02-15 04:34:33 |
| 198 | MARY | KEITEL | 2006-02-15 04:34:33 |
| 199 | JULIA | FAWCETT | 2006-02-15 04:34:33 |
| 200 | xu | sss | 2020-12-15 14:37:24 |
| 202 | xu | new | 2020-12-15 14:44:02 |
| 203 | xu | sss | 2020-12-15 14:37:24 |
+----------+------------+-----------+---------------------+
mysql> commit; -- 提交事务A事务B
mysql> start transaction; -- 开始事务B
mysql> update actor set first_name='xu' where actor_id > 19
-- 事务B更新数据
mysql> select * from actor limit 195,203;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 196 | BELA | WALKEN | 2006-02-15 04:34:33 |
| 197 | REESE | WEST | 2006-02-15 04:34:33 |
| 198 | MARY | KEITEL | 2006-02-15 04:34:33 |
| 199 | JULIA | FAWCETT | 2006-02-15 04:34:33 |
| 200 | xu | sss | 2020-12-15 14:37:24 |
| 202 | xu | qian | 2020-12-15 14:42:55 |
| 203 | xu | sss | 2020-12-15 14:37:24 |
+----------+------------+-----------+---------------------+
mysql> commit; -- 提交事务B以上是update时出现不可重复读的情况，delete和insert的情况就不在叙述了。
- 事务B对数据进行update、insert后，如果事务A更新的记录范围包括事务B更新的范围，则事务A将会读到事务B更新的数据。
- 事务B进行delete，删除的记录，如果事务A对其更新操作，结果是更新相当于失败，数据不变。
4.serializable (可串行化)
- 隔离级别最高，事务A读数据会给对象加一个共享锁（s锁），其他的事务只能给其加共享锁（s锁），不能加排他锁（x锁）。
- 事务A写数据会给对象加一个排它锁（x锁），其他的事务不能对其加锁。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
