MySQL——深入数据库原理（事务及锁）

锁

行级锁

InnoDB 实现标准的行级锁定，其中有两种类型的锁：共享（S）锁和排他（X）锁。

共享 (S) 锁

允许持有该锁的事务读取行。

如果事务 T1 持有 r 行上的共享 (S) 锁，则来自某个不同事务 T2 对 r 行上的锁的请求将按如下方式处理：

• T2 对 S 锁的请求可以立即获得批准。结果，T1 和 T2 都持有 r 上的 S 锁。
• T2 对 X 锁的请求无法立即获得批准。

排他 (X) 锁

允许持有该锁的事务更新或删除行。

如果事务 T1 持有行 r 上的独占 (X) 锁，则某个不同事务 T2 对 r 上任一类型的锁的请求不能立即被授予。相反，事务 T2 必须等待事务 T1 释放其对行 r 的锁定

间隙锁

是对索引记录之间间隙的锁定，或者对第一个索引记录之前或最后一个索引记录之后的间隙的锁定。在可重复读的事务级别中解决幻读的问题。

例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE；防止其他事务将值 15 插入到列 t.c1 中，无论该列中是否已经存在任何此类值，因为范围中所有现有值之间的间隙已被锁定。

表级锁

意向锁

意向锁设置在表上，简化其他事务是否可以上锁，当某个操作需要拿某种锁的时候，先判断与表锁是否冲突，减少更细力度行上是否有冲突的锁。上锁之前先上意向锁，意向锁表明稍后要进行哪种类型的行锁

1. 共享意向锁（IS）： 表示事务打算在表中的各个行上设置共享锁。
2. 排他意向锁（IX）： 表示事务打算对表中的各个行上设置排它锁。

意向锁定协议如下：
在事务可以获取表中行的共享锁之前，它必须首先获取IS表上的锁或更强的锁。
在事务可以获取表中行的排他锁之前，它必须首先获取IX 表上的锁。

如果请求事务与现有锁兼容，则将锁授予该事务，但如果与现有锁冲突，则不会授予该锁。事务会等待，直到释放冲突的现有锁。如果锁请求与现有锁冲突并且由于会导致 死锁而无法被授予，则会发生错误。

意向锁不会阻止除全表请求之外的任何内容（例如，LOCK TABLES … WRITE）。意向锁的主要目的是表明有人正在锁定一行，或者将要锁定表中的一行
3. insert意向锁：insert操作设置间隙锁 （不重要）

自增锁

Lock Table/DDL

show engine innodb status;#判断当前数据库上边有哪些锁

事务

什么是事务？ 事务就是将一组SQL语句作为一个工作单元以原子的进行处理，要么都成功，要么都失败。

银行应用是解释事务必要性的经典例子。假设一个银行的数据库有两张表：支票表(checking)和储蓄表(savings)。现在要从用户Jane的支票账户转移200美元到她的储蓄账户，那么需要至少三个步骤：
1.确保支票账户的余额高于200美元。
2.从支票账户的余额中减去200美元。
3.在储蓄账户的余额中增加200美元。
以上三步操作必须打包在一个事务中，以保证一旦其中任何一步失败，都能够回滚所有的操作。
·
下图为可能的事务相关的SQL语句：

ACID 原则

单纯的事务概念并不能保证我们业务的正常工作。试想一下，基于上面的银行应用如果数据库在执行第4条语句时崩溃了，会发生什么？天知道，客户可能会损失200美元。再假如，在执行到第3条语句和第4条语句之间时，另外一个进程要删除支票账户的所有余额会发生什么？结果可能是银行不知不觉白白给了Jane 200美元。在这一系列操作中，有更多的失败可能性。连接可能会断开、会超时，甚至数据库服务器在操作执行过程中会崩溃。这就是为什么存在高度复杂且缓慢的两阶段提交系统的典型原因：为了应对各种失败场景
除非系统通过严格的ACID测试，否则空谈事务的概念是不够的。ACID代表原子性(atomicity)、一致性(consistency)、隔离性(isolation)和持久性(durability)。一个确保数据安全的事务处理系统，必须满足这些密切相关的标准。

1. 原子性 A

一个事务必须被视为一个不可分割的工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。

要么全部完成，要么全部不完成

2. 一致性 C

数据库总是从一个一致性状态转换到下一个一致性状态。在前面的例子中，一致性确保了，即使在执行第3、4条语句之间时系统崩溃，支票账户中也不会损失200美元。两个账户的总额总是合理的，如果事务最终没有提交，该事务所做的任何修改都不会被保存到数据库中。在处理一致性时，通常使用锁来保证。

事务前后数据完整性要一致

3. 隔离性 I

通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的，这就是隔离性带来的结果。在前面的例子中，当执行完第3条语句、第4条语句还未开始时，此时有另外一个账户汇总程序开始运行，其看到的支票账户的余额并没有被减去200美元。因此隔离性会带来相应的问题。

并发执行时，数据库为每个用户开启一个事务，各事物之间互相不可见，事物之间相互隔离

4. 持久性 D

一旦提交，事务所做的修改就会被永久保存到数据库中。此时即使系统崩溃，数据也不会丢失。持久性是一个有点模糊的概念，实际上持久性也分很多不同的级别。有些持久性策略能够提供非常强的安全保障，而有些则未必。而且不可能有100%的持久性保障（如果数据库本身就能做到真正的持久性，备份的策略就不会被提出）

事务一旦提交就不可逆

例如· 转账200
操作前 A ：800 B：200
操作后 A： 600 B：400
如果在操作前 （事务还没有提交）服务器断电，那么重启后，数据状态为
A：800 B：200
如果在操作后 (事务已经提交) 服务器断电，那么重启数据库以后 ，数据状态为 A： 600 B ： 400

隔离级别

隔离性在实际操作中比看起来复杂得多。ANSI SQL标准定义了4种隔离级别，每种存储引擎实现的隔离级别都不尽相同。这个通用标准的目标是定义在事务内外可见和不可见的更改的规则。较低的隔离级别通常允许更高的并发性，并且开销也更低。

1. READ UNCOMMITTED（未提交读）

在READ UNCOMMITTED级别，在事务中可以查看（获取）其他事务中还没有提交的修改。

以上面银行应用举例，如果当你在查看你的余额时期间（你这边网络比较慢），你妈妈给你转账200元，但由于某停电原因，转账动作并没有提交，ATM机并没有收取这200元，此时你读取了没提交的这个+200元的记录。这就叫读未提交

读未提交会有什么后果呢？

你读取了错误的+200的余额，导致你认为你有这些钱，当你在你女朋友面前给她买东西付款时，此时余额不足的大字明明白白清清楚楚的展示在你女朋友面前，尴尬了不哥们！！！哈哈哈哈嗝~~

专业术语来说，读未提交会产生脏读、不可重复读、幻读（不可重复读和幻读后续会讲解）。

脏读：读取并使用了失效的数据记录

2. READ COMMITTED（提交读）

大多数数据库系统的默认隔离级别是READ COMMITTED（但MySQL不是）。READ COMMITTED满足前面提到的隔离性的简单定义：一个事务可以看到其他事务在它开始之后提交的修改，但在该事务提交之前，其所做的任何修改对其他事务都是不可见的。

还是银行的例子来举例，你有两张建行的卡，建行一卡和建行二卡，建行一卡中有10元，建行二卡中有100万元，这两证卡都已经绑定在了建行APP上了。某一天，你女朋友想要确定一下你的潜（钱）力，想看看你的资产额度，你信誓旦旦的掏出手机，打开建行APP。（建行APP的资产计数是当时将两张银行卡的余额做相加实现的，也就是先查第一张卡，然后再查第二张卡，你查看余额的这个事务包含了查卡一和卡二两个步骤）在这个执行事务第一步拿到卡一的10元时，你妈妈突然把卡二的钱转到卡一，并且此你妈妈的这个事务已提交。当你的事务再去读卡二时读到的是0元，两卡相加10元。这就叫做读提交。

会导致什么后果呢？

你读取了第二张卡的余额并不是错误的，读取的是你妈妈那个事务规规矩矩提交了的，只是因为你读取了不同的快照导致的。10元余额再次清清楚楚明明白白呈现在你可爱的女朋友面前！！真是潜（钱）力满满！！分手！！当然你再次刷新，如果没有什么意外情况，余额会显示你真正的潜（钱）力。

专业术语上来说，读已提交会导致不可重复读和幻读。

不可重复读:两次读取的数据不一样。

3. REPEATABLE READ（可重复读）

REPEATABLE READ结合MVCC原理（下面会将讲解）解决了READ COMMITTED级别的不可重复读问题，保证了在同一个事务中多次读取相同行数据的结果是一样的。但是理论上，可重复读隔离级别还是无法解决另外一个幻读的问题。

为什么解决了不可重复读的问题？
基于MVCC原理，在事务开启之前，innodb会记录一个当前事务的ID，通过当前只能读取比自己事务id小于或等于的数据值的原则，相当于建立了一个视图，这样每次读取的数据都是不变的，从而解决了不可重复的的问题。

幻读：同一个事务中，读取的数据个数不一样；

REPEATABLE READ是MySQL默认的事务隔离级别，

4. SERIALIZABLE（可串行化）

SERIALIZABLE是最高的隔离级别。该级别通过强制事务按序执行，使不同事务之间不可能产生冲突，从而解决了前面说的幻读问题。简单来说，SERIALIZABLE会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。实际应用中很少用到这个隔离级别，除非需要严格确保数据安全且可以接受并发性能下降的结果。

也就是说无论什么事务，都要进行排队，可能造成系统卡顿问题

MVCC原理

MVCC的工作原理是使用数据在一个事务中看到的数据是一致的，他通过快照来实现的。这意味着，无论事务运行多长时间，只要这个事务没结束，都可以看到数据的一致视图，也意味着不同的事务可以在同一时间看到同一张表中的不同数据。

为了实现这个机制，innodb在表中添加了一些隐藏列,其中一列记录最新改动此条数据的事务的id，当出现一个早期事务读取此条记录时，发现自己的事务id比这条数据的事务ID要小，那么这个事务就知道这个值被改动过，从而去通过undolog日志去读取此条数据小于等于自己事务ID的数据值。通过这样一个机制相当于维护了一张专属于这个事务的视图，从始至终都不会发生不可重复读的情况。

也就是说，基于MVCC原理，innodb引擎在事务开始是会记录此此事务的事务id,在读取数据时，通过比较事务id来判断当前要读的数据是否发生过变动，说白了，当前事务只能读取小于等于自己事务ID的数据值。

小结

在事务并发控制的内容中，但是依赖事务、ACID原则、隔离原则等这些概念单独使用不能保证并发事务的执行正确的结果，需要各种概念原则相互贯通核融合。

无论天气如何，无论白天黑夜，我都想要改善每个此刻，也把它刻在我的拐杖上 ——《瓦尔登湖》