预备知识
基础概念
- 数据库四种事务隔离级别
- RU-未提交读(Read Uncommitted):可能读取到其他会话中未提交事务修改的数据, 可能会出现脏读
- RC-提交读(Read Committed):只能读取到已经提交的数据,Oracle等多数数据库默认都是该级别。
- RR-可重复读(Repeatable Read):可重复读。在同一个事务内的查询都是事务开始时刻一致的,InnoDB默认级别。在SQL标准中,该隔离级别消除了不可重复读,但是还存在幻读。
- S-串行读(Serializable):完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞。
-
脏读:(同时操作都没提交的读取)
脏读又称无效数据读出。一个事务读取另外一个事务还没有提交的数据叫脏读。 例如:事务T1修改了一行数据,但是还没有提交,这时候事务T2读取了被事务 T1修改后的数据,之后事务T1因为某种原因Rollback了,那么事务T2读取的数 据就是脏的。 解决办法:把数据库的事务隔离级别调整到READ_COMMITTED
-
不可重复读:(同时操作,事务一分别读取事务二操作时和提交后的数据,读取的记录内容不一致)
不可重复读是指在同一个事务内,两个相同的查询返回了不同的结果。 例如:事务T1读取某一数据,事务T2读取并修改了该数据,T1为了对读取值进 行检验而再次读取该数据,便得到了不同的结果。 解决办法:把数据库的事务 隔离级别调整到REPEATABLE_READ
-
幻读:(和可重复读类似,但是事务二的数据操作仅仅是插入和删除,不是修改数据,读取的记录数量前后不一致)
例如:系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等 级,但是系统管理员B就在这个时候插入(注意时插入或者删除,不是修改) )了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没 有改过来,就好像发生了幻觉一样。这就叫幻读。 解决办法:把数据库的事务隔离级别调整到SERIALIZABLE_READ
基础语句
- 查看当前会话隔离级别
select @@tx_isolation;
- 查看系统当前隔离级别
select @@global.tx_isolation;
- 设置当前会话隔离级别
set session transaction isolation level read uncommitted;
- 设置系统当前隔离级别
set global transaction isolation level repeatable read;
建立测试表
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) CHARACTER SET utf8mb4 NOT NULL,
`sex` tinyint(4) unsigned NOT NULL DEFAULT '1' COMMENT '性别1男2女 未知0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
读未提交-RU
会话1 开启事务插入数据,不提交
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED |
+------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user(name) values('cdb-1');
Query OK, 1 row affected (0.00 sec)
此时未提交事务,会话2查询,查到了会话1未提交的数据
mysql> select * from user;
+----+------------------+-----+
| id | name | sex |
+----+------------------+-----+
8 | 0123456789123456 | 1 |
| 9 | cdb-1 | 1 |
+----+------------------+-----+
8 rows in set (0.00 sec)
会话2 发声了脏读, 即会话2读取到了会话1未提交的数据,
设置隔离级别为读已提交可解决
读已提交
事务1
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction; # 操作一
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; # 操作三
+----+----------+
| id | name |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)
mysql> select * from user; # 操作五,操作四的修改并没有影响到事务一
+----+----------+
| id | name |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)
mysql> select * from user; # 操作七
+----+------+
| id | name |
+----+------+
| 10 | lisi |
+----+------+
1 row in set (0.00 sec)
mysql> commit; # 操作八
Query OK, 0 rows affected (0.00 sec)
事务2
mysql> start transaction; # 操作二
Query OK, 0 rows affected (0.00 sec)
mysql> update user set name='lisi' where id=10; # 操作四
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit; # 操作六
Query OK, 0 rows affected (0.08 sec)
虽然脏读的问题解决了,但是注意在事务一的操作七中,
事务二在操作六commit后会造成事务一在同一个transaction中
两次读取到的数据不同,这就是不可重复读问题,
使用第三个事务隔离级别repeatable read可以解决这个问题。
可重复读(mysql innoDB 默认隔离级别)
mysql> start tansactoin; # 操作一
mysql> select * from user; # 操作五
+----+----------+
| id | name |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)
mysql> commit; # 操作六
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; # 操作七
+----+------+
| id | name |
+----+------+
| 10 | lisi |
+----+------+
1 row in set (0.00 sec)
事务2
mysql> start tansactoin; # 操作二
mysql> update user set name='lisi' where id=10; # 操作三
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit; # 操作四
在事务一的操作五中我们并没有读取到事务二在操作三中的update,只有在commit之后才能读到更新后的数据。
mysql innoDB是否解决幻读
加字段年龄
ALTER TABLE `user`
ADD COLUMN `age` tinyint(1) UNSIGNED NOT NULL DEFAULT 0
事务1
先查询现有表数据
mysql> select * from user;
+----+------+-----+-----+
| id | name | sex | age |
+----+------+-----+-----+
| 1 | one | 1 | 12 |
| 12 | two | 1 | 10 |
+----+------+-----+-----+
2 rows in set (0.00 sec)
开始操作----
mysql> start transaction; 操作1
Query OK, 0 rows affected (0.00 sec)
mysql> update user set age=22; 操作5
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> commit; 操作6
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; 操作7
+----+------+-----+-----+
| id | name | sex | age |
+----+------+-----+-----+
| 1 | one | 1 | 22 |
| 12 | two | 1 | 22 |
| 13 | two | 1 | 22 |
+----+------+-----+-----+
3 rows in set (0.00 sec)
事务2
mysql> start transaction; 操作2
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user(name,age) values('two',10); 操作3
Query OK, 1 row affected (0.00 sec)
mysql> commit; 操作4
Query OK, 0 rows affected (0.00 sec)
操作7后发现更新了3条数据 , 因此 mysql innoDB 并未完全解决幻读
但是如果 操作3拆入 之前 执行 操作2更新 则 不会影响 事务2 新插入数据
串行化
事务1
mysql> set session transaction isolation level Serializable;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE |
+----------------+
1 row in set, 1 warning (0.00 sec)
mysql> start transaction; 操作1
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; 操作2
+----+-------+-----+-----+
| id | name | sex | age |
+----+-------+-----+-----+
| 1 | one | 1 | 30 |
| 12 | two | 1 | 30 |
| 13 | two | 1 | 30 |
| 14 | three | 1 | 3 |
+----+-------+-----+-----+
4 rows in set (0.00 sec)
mysql> commit; 操作4
Query OK, 0 rows affected (0.00 sec)
事务2
mysql> insert into user(name) values(4);
--mysql等待锁释放
---操作4完成后 出现成功提示
Query OK, 1 row affected (3.78 sec)
可以看出,事务1查询信息时,事务2 无法立即执行
可以看出,如果一个事务,使用了SERIALIZABLE——可串行化隔离级别时,
在这个事务没有被 提交之前 其他的线程,
只能等到当前操作完成之后,才能进行操作,这样会非常耗时,
而且,影响数据库 的性能,通常情况下,不会使用这种隔离级别
本博客基本完全按照
https://segmentfault.com/a/1190000010561284
进行手动验证,并补充相关测试结果和基础知识
参考