老王私房钱之MYSQL事务及隔离级别
一、理论
1.事务简介
事务可由一条非常简单的SQL语句组成，也可以由一组复杂的SQL语句组成。事务是访问并更新数据库中的各种数据项的一个程序执行单元。事务中的操作要么都做修改，要么都不做MySQL包含诸如InnoDB存储引擎严格遵循ACID模型，因此数据不会损坏，结果不会因软件崩溃和硬件故障等异常情况而失真。当您依靠符合ACID的功能时，无需重新发明一致性检查和崩溃恢复机制。如果您有其他软件保护措施，超可靠的硬件或可以容忍少量数据丢失或不一致的应用程序，则可以调整MySQL设置，以权衡一些ACID可靠性以获得更高的性能或吞吐量。
2.事务的基本要素（ACID）
A(Atomicity),原子性：
是指整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作都执行成功，才算整个事务成功，事务中任何一个SQL语句执行失败，已经执行成功的SQL语句必须撤销，数据库应退回到执行事务前的状态
C(consistency),一致性：
一致性指事务从一种状态转变为下一种一致的状态。在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏
I(isolation),隔离性：
要求每个读写事务的对象对其他事务的操作对象能相互分离，即该事务提交前对其他事务不可见，通常使用锁来实现
D(durability),持久性：
事务一旦提交，其结果是永久性的。即使发生宕机等故障，数据库也能将数据恢复
3.事务的实现
事务隔离性是由锁来实现。原子性、一致性、持久性通过数据库的redo log和undo log来完成。redo log用来保证事务的原子性和持久性。undo log用来保证事务的一致性。
4.事务的隔离级别
（1）Read Uncommitted（读取未提交内容）
在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。
（2）Read Committed（读取提交内容）
这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。
（3）Repeatable Read（可重读）
这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，
幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。
InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。
（4）Serializable（可串行化）
这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。
5.并发场景下不同隔离级别会产生的问题
脏读(Drity Read)：
某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
不可重复读(Non-repeatable read):
在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
幻读(Phantom Read):
在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。
二、实践
1.Read Uncommitted（读取未提交内容）
小白在小白银行做数据库管理员，这一天老王要从一张存有一万块钱的银行卡里给兄弟转账一千
session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from deposit;
+----+---------+----------+
| id | name | deposit |
+----+---------+----------+
| 1 | laowang | 10000.00 |
+----+---------+----------+
1 row in set (0.00 sec)
mysql> update deposit set deposit=deposit-1000 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
此时恰逢老王的老婆王太太正在查看银行余额
session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from deposit;
+----+---------+---------+
| id | name | deposit |
+----+---------+---------+
| 1 | laowang | 9000.00 |
+----+---------+---------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
这时老王心里想自己这张卡自己的老婆也有密码，不安全，还是用私房钱给兄弟转吧，于是取消了转账的操作
session1
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from deposit;
+----+---------+----------+
| id | name | deposit |
+----+---------+----------+
| 1 | laowang | 10000.00 |
+----+---------+----------+
1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
不过就是这样，老王回到家里还是被罚跪了一晚搓衣板，老王百思不得其解，自己的老婆是怎么知道自己要转账的，于是联系了做DBA的小白，小白多方查找资料后发现是隔离级别的问题（注：我这里为了不影响环境，用的是session级别），所产生的这种现象就叫做脏读.
mysql> show variables like 'transaction_isolation';
+-----------------------+------------------+
| Variable_name | Value |
+-----------------------+------------------+
| transaction_isolation | READ-UNCOMMITTED |
+-----------------------+------------------+
- 1
- 2
- 3
- 4
- 5
- 6
于是小白调整了隔离级别以解决这个事情
mysql> set session transaction_isolation='read-committed';
- 1
2.Read Committed（读取提交内容）
改了隔离级别之后，也就没有脏读的现象发生了，今天王太太的外甥结婚，让老王去小白银行给外甥转1000元的份子钱
session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from deposit;
+----+---------+----------+
| id | name | deposit |
+----+---------+----------+
| 1 | laowang | 10000.00 |
+----+---------+----------+
1 row in set (0.00 sec)
mysql> update deposit set deposit=deposit-1000 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
此时恰逢王太太又在查看银行余额
session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from deposit;
+----+---------+----------+
| id | name | deposit |
+----+---------+----------+
| 1 | laowang | 10000.00 |
+----+---------+----------+
1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
此时王太太知道老王还没有转钱，于是告诉老王 快点转钱，老王也很快把钱转了过去
session1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
- 1
- 2
而这时候王太太没有点击刷新按钮（注：这里的刷新代表commit;）,重新查询却发现金额已经变了
session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from deposit;
+----+---------+----------+
| id | name | deposit |
+----+---------+----------+
| 1 | laowang | 10000.00 |
+----+---------+----------+
1 row in set (0.00 sec)
# 下面的内容为老王转账提交之后的查询
mysql> select * from deposit;
+----+---------+---------+
| id | name | deposit |
+----+---------+---------+
| 1 | laowang | 9000.00 |
+----+---------+---------+
1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
王太太觉得这种用户体验很不好，于是找到了小白，小白经过多方查找发现这还是隔离级别的问题，王太太在同一个事务中两次查询结果的不同就叫做不可重复读 ，于是小白再次调整了隔离级别.
3.Repeatable Read（可重读）
mysql> set @@session.transaction_isolation='repeatable-read';
Query OK, 0 rows affected (0.00 sec)
- 1
- 2
这里为了模拟传统的可重复读，我们需要关闭间隙锁，在my.cnf中加入这么一项，然后重启数据库
innodb_locks_unsafe_for_binlog=1
- 1
这一天，王太太来到小白银行开通了一张银行卡并准备往除第一张卡之外的卡里转钱
session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from deposit where id>1 for update;
+----+---------+---------+
| id | name | deposit |
+----+---------+---------+
| 2 | laowang | 0.00 |
+----+---------+---------+
1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
就在这时，老王以迅雷不及掩耳盗铃之铃儿响叮当仁不让世界充满爱你没商量商量因我爱你之势迅速办了一张卡
session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into deposit values(3,'laowang',0);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
这时候王太太开始转钱了
session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from deposit where id>1 for update;
+----+---------+---------+
| id | name | deposit |
+----+---------+---------+
| 2 | laowang | 0.00 |
+----+---------+---------+
1 row in set (0.00 sec)
# 下面为老王办完卡之后的操作
mysql> update deposit set deposit=deposit+1000 where id>1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
王太太发现自己竟然往两个卡里转了钱，感觉很奇怪，逼问之下，老王承认了自己偷偷办了张卡，可怜的老王又要跪搓衣板了，不过这个问题还是要解决的，王太太找到小白之后，小白发现这是传统Repeatable Read（可重读）所造成的幻读问题，于是小白采用了带有Next-Key Lock算法的Mysql的可重复读，重新进行了上面的操作
session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from deposit where id>1 for update;
+----+---------+----------+
| id | name | deposit |
+----+---------+----------+
| 2 | laowang | 10000.00 |
+----+---------+----------+
1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into deposit values(3,'laowang',0);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- 1
- 2
- 3
- 4
- 5
可以发现,这种情况下，老王没有办法办新卡了，可怜的老王，获得私房钱的方法又少了一种
4.Serializable（可串行化）
少用到。
其他资料
（1）一文讲清楚MySQL事务隔离级别和实现原理
（2）事务的四种隔离级别-带栗子
文章来源: andyguo.blog.csdn.net，作者：山顶夕景，版权归原作者所有，如需转载，请联系作者。
原文链接：andyguo.blog.csdn.net/article/details/114414074
- 点赞
- 收藏
- 关注作者
评论（0）