mysql学习笔记-事务隔离级别

发表于   |     |   热度 ℃

事务是什么？
事务就是一组原子性的SQL查询，或者说一个独立的工作单元。如果数据库引擎能够
成功的对数据库应用该组查询的全部语句，那么执行该组查询。如果其中有任何一条语句因为崩溃
或者其它原因无法执行，那么所有的语句都不会执行。也就说，事务内的语句，要么全部执行成功，
要么全部执行失败。

事务

事务是什么？

事务就是一组原子性的SQL查询，或者说一个独立的工作单元。如果数据库引擎能够
成功的对数据库应用该组查询的全部语句，那么执行该组查询。如果其中有任何一条语句因为崩溃
或者其它原因无法执行，那么所有的语句都不会执行。也就说，事务内的语句，要么全部执行成功，
要么全部执行失败。

事务的特性ACID

银行的例子是解释事务最好的例子，假设一个银行的数据库有两张表：支票(checking)和储蓄(savings)
表。现在要从用户小墨鱼的支票账户转移200元到小美的储蓄账户，那么至少需要三个步骤：

1、检查支票账户的余额高于200元
2、丛支票账户余额中减去200元
3、在储蓄账户账户中增加200元

上述三个步骤必须打包在一个事务中，任何一个步骤失败，则必须回滚所有的步骤。

我么可以使用

1
start transaction

开启一个事务，然后使用COMMIT提交事务将修改的数据持久保留，要么使用ROLLBACK撤销
所有的修改。事务SQL的样本如下:

1
2
3
4
start transaction;
select balance from checking where customer_id = 111;
update checking set balance = balance - 200 where customer_id = 111;
update savings set balance = balance + 200 where customer_id = 111;

单纯的事务概念并不是故事的全部。试想一下，如果执行到第四条语句的时候服务器崩溃了，会发生什么?
天知道用户会损失200元。再假如，在执行到第三条第四条语句之间时，另外一个进程要删除支票账户
上的所有余额，那么结果可能就是银行在不知道这个逻辑的情况下白送200元。

除非系统通过严格的ACID测试，否则空谈事务的概念是不够的。
ACID表示原子性(Atomicity)、一致性(consistency)、隔离性(isolation)和持久性(durability)
一个运行良好的事务处理系统，必须具备这些标准的特征。

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

一致性:
数据库总是从一个一致性的状态转换到另一个一致性状态。在前面的例子中，一致性确保了，即使
执行第三、四条语句之间系统崩溃，支票的账户中也不会有损失，因为事务最终没有提交，所以
事务中所做的修改也不会保存到数据库中。

隔离性:
通常来说，一个事务所做的修改在最终提交以前，对其它事务是不可见的。在前面的例子中，当执行
完第三条语句、第四条语句还未开始时，此时有另外一个账户汇总程序开始运行，则其看到的支票账户的
余额并没有被减去200元。

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

一个实现了ACID的数据库，相比没有实现ACID的数据库，通常会需要更强的CPU处理能力、更大的
内存和更多的磁盘空间。

隔离级别

隔离性其实比想象的要复杂，在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所
做的修改，哪些在事务内和事务间可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，
系统的开销也比较低。

每种存储引擎实现的隔离级别不尽相同。如果熟悉其它的数据库产品，可能会发现某些特性和你期望的
会有一些不同，可以根据所选择的引擎查阅相关的手册。

下面简单的介绍一下四种隔离级别。

READ UNCOMMITTED(未提交读)
在READ UNCOMMITTED级别，事务中的修改，即使没有提交，对其它事务也是可见的。
事务可以读取为提交的数据，这也被称为“脏读”。这个级别会导致很多问题，从性能上
来说，READ UNCOMMITTED不会比其它的级别好太多，但缺乏其它级别的很多好处，除非
真的有非常必要的理由，在实际应用中一般很少使用。

READ COMMITTED(提交读)
大多数数据库的默认隔离级别都是READ COMMITTED（但mysql不是）。READ COMMITTED满足
前面提到的隔离性的简单定义：一个事务开始时，只能“看见”已经提交的事务所做的改变。
换句话说，一个事务从开始直到提交之前，所做的任何修改对其它事务是不可见的。这个级别
有时候也叫“不可重复读”，因为两次执行同样的查询，可能会得到不一样的结果。

REPEATABLE READ(可重复读)
REPEATABLE READ解决了脏读的问题，该级别保证了在同一个事务中多次读取同样记录的结果是
一致的。但是理论上，可重复读隔离级别还是无法解决另外一个幻读的问题。所谓的幻读，指的是
当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务
再次读取该范围的记录时，会产生幻行。InnoDB和XtraDB存储引擎通过多版本并发控制(MVCC)
解决了幻读的问题。可重复读是mysql默认事务隔离级别。

SERIALIZABLE(可串行化)
SERIALIZABLE是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读问题。
简单来说，SERIALIZABLE会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁
争用的情况。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据一致性而且可以接受
没有并发的情况下，才考虑采用该级别。

其实，文章到这里已经就要结束的。
不过还要一些内容顺带一起写上去😏。。。

死锁

死锁指的是两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致
而行循环现象。当多个事务视图以不同顺序锁定资源时，就可能会产生死锁。
多个事务同事锁定同一个资源时，也会产生死锁。例如，设想下面两个事务同时处理StockPrice表：

事务1

1
2
3
4
start transaction;
update stockprice set close = 22 where stock_id = 4 and date = '2017-01-01'
update stockprice set close = 11 where stock_id = 3 and date = '2017-02-03'
commit;

事务2

1
2
3
4
start transaction;
update stockprice set high = 20.12 where stock_id = 3 and date = '2017-02-03'
update stockprice set high = 47.20 where stock_id = 4 and date = '2017-01-01'
commit;

如果凑巧，两个事务都执行了第一条UPDATE语句，更新了一行数据，同时也锁定了该行数据，接着
每个事务都尝试去执行第二条UPDATE语句，却发现改行已经被对方锁定，然后两个事务都等待对方
释放锁，同事又持有对方需要的锁，则陷入死循环。除非有外部因素介入才可能解除死锁。

InnoDB目前解决死锁的方法是，将持有最少行级排它锁的事务进行回滚。

锁定行为和顺序是和存储引擎有关的。同样的顺序执行语句，有些存储引擎会产生死锁，有些则不会。
死锁产生有双重原因：有些是因为真正的数据冲突，这种情况通常很难避免，但有些则完全是由存储引擎
的实现方式导致的。

死锁发生以后，只有部分或者完全回滚其中一个事务，才能打破死锁。对于事务型的系统，这是无法避免的，
所以应用程序在设计时必须考虑如何处理死锁。大多数情况下只需要重新执行因死锁的回滚事务。

多版本并发控制

MySQL的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，他们一般
都同时实现了多版本并发控制(MVCC)。不仅是MySQL，包括Oracle、PostgreSQL等其他数据库
也都实现了MVCC，但各自的实现机制不尽相同，因为MVCC没有一个统一的的实现标准。

可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁的操作，因此开销更低。
虽然实现机制有所不同，但大多实现了非阻塞的读操作，写操作也只锁定必要的行。

MVCC的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，
每个事务看到的数据都是一致的。根据事务开始的不同，每个事务对同一张表，同一时刻看到的数据
可能是不一样的(因为不同的时间点可能数据就已经产生了不同的快照版本，而每个事务在默认的RR隔离级别下只能看到事务开始时的数据快照)。

前面说到不同存储引擎的MVCC实现是不同的，典型的有乐观(optimistic)并发控制和
悲观(pessimistic)并发控制。下面通过InnoDB的简化版行为来说明MVCC是如何工作的。

InnoDB的MVCC，是通过每行记录后面保存两个隐藏的列来实现的。这两个列：一个是保存了行的创建时间，
一个保存行的过期时间(或删除时间)。当然存储的并不是实际的时间值，而是系统版本号。没开始一个
新的事务，系统版本号会自动增加。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的
每行记录的版本号进行比较。下面看一下在REPEATABLE READ隔离级别下，MVCC具体是如何操作的。

SELECT
InnoDB会根据以下两个条件检查每行记录：
a) InnoDB只查找版本早于当前事务版本的数据行(也就是，行的版本号小于或等于事务的系统版本号)
这样可以确保事务读取的行，要么是在事务开始之前已经存在的，要么是事务自身插入或者修改过的。

b) 行的删除版本要么未定义，要么大于当前事务版本号。这样可以确保事务读取到的行，在
   事务开始之前为被删除。

INSERT
InnoDB为新插入的每一行保存当前系统版本号作为行版本号。

DELETE
InnoDB为删除的每一行保存当前系统版本号作为删除标识。

UPDATE
InnoDB为插入一行新记录，保存当前系统版本号做为行版本号，同时保存当前系统版本号
到原来的行为作为删除标识。

保存着两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，
性能很好，并且也能保证只会读取到符合标准的行。不足之处就是每行记录都需要额外的存储空间，
需要做更多检查工作，以及一些额外的维护工作。

MVCC只在PEPEATABLE READ 和 READ COMMITTED两个隔离级别下工作。
其它两个隔离级别都和MVCC不兼容，因为READ UNCOMMITTED总是读取到最新的数据行，而不是
符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁。

参考

http://www.jianshu.com/p/8d735db9c2c0 【隔离级别实战】
http://tech.meituan.com/innodb-lock.html 【美团的博客质量向来都比较高】

人生苦短，我要打赏!