mysql教程#欄目的介紹MySQL相關的交易、隔離等級及MVCC。
#
CREATE TABLE `user` ( `id` int(11) DEFAULT NULL, `name` varchar(12) DEFAULT NULL) ENGINE = InnoDB;insert into user values(1, '刺猬');复制代码
在上述的輪轉場景中，原子性就要求了這五個步驟或都執行，或都不執行，不可能存在我的帳戶扣款100元，而你的帳戶100元沒有到帳的情況。
1.2一致性（一致性）
同时，事务的一致性要求符合开发人员定义的约束，如金额大于0、身高大于0等。
在上述的转账场景中，一致性能够保证最终执行完整个转账操作后，我账户的扣款金额与你账户到账金额是一致的，同时如果我和你的账户余额不满足金额的约束（如小于0），整个事务会回滚。
事务的隔离性是指：在一次状态转换过程中不会受到其他状态转换的影响。
假设我和你都有100元，我发起两次转账，转账金额都是50元，下面使用伪代码来表示的操作步骤：
read my
my=my-50
read yours
yours=yours+50
如果未保证隔离性就可能发生下面的情况：
| 时刻 | 第一次转账 | 第二次转账 | 我的账户余额 | 你的账户余额 |
|---|---|---|---|---|
| 1 | read my(100) | my=100 | yours=100 | |
| 2 | read my(100) | my=100 | yours=100 | |
| 3 | my=my-50=100-50=50 | my=50 | yours=100 | |
| 4 | read yours(100) | my=my-50=100-50=50 | my=50 | yours=100 |
| 5 | yours=yours+50=100+50=150 | my=50 | yours=150 | |
| 6 | read yours(150) | my=50 | yours=150 | |
| 7 | yours=yours+50=150+50=200 | my=50 | yours=200 | |
| 7 | end | end | my=50 | yours=200 |
两次转账后，最终的结果是我的账户余额为50元，你的账户余额为200元，这显然是不对的。
而如果在保证事务隔离性的情况下，就不会发生上面的情况，损失的只是一定程度上的一致性。
事务的持久性是指：事务在提交以后，它所做的修改就会被永久保存到数据库。
在上述的转账场景中，持久性就保证了在转账成功之后，我的账户余额为0，你的账户余额为200。
在 MySQL 中，我们可以通过 begin 或 start transaction 来开启事务，通过 commit 来关闭事务，如果 SQL 语句中没有这两个命令，默认情况下每一条 SQL 都是一个独立的事务，在执行完成后自动提交。
比如：
update user set name='重塑' where id=1;复制代码
假设我只执行这一条更新语句，在我关闭 MySQL 客户端然后重新打开一个新的客户端后，可以看到 user 表中的 name 字段值全变成了「重塑」，这也印证了这条更新语句在执行后已被自动提交。
自动提交是 MySQL 的一个默认属性，可以通过 SHOW VARIABLES LIKE 'autocommit' 语句来查看，当它的值为 ON 时，就代表开启事务的自动提交。
mysql> SHOW VARIABLES LIKE 'autocommit'; +---------------+-------+| Variable_name | Value | +---------------+-------+| autocommit | ON | +---------------+-------+1 row in set (0.00 sec)复制代码
我们可以通过 SET autocommit = OFF 来关闭事务的自动提交。
然而，即便我们已经将 autocommit 变量的值改为 OFF 关闭事务自动提交了，在执行某些 SQL 语句的时候，MySQL 还是会将事务自动提交掉，这被称为隐式提交。
会触发隐式提交的 SQL 语句有：
create, drop, alter, truncate
create/drop user, grant, set password
| 时刻 | 事务A | 事务B |
|---|---|---|
| 1 | begin; | |
| 2 | update user set name='重塑' where id=1; | |
| 3 | select name from user where id=1;(N1) | |
| 4 | begin; | |
| 5 | select name from user where id=1;(N2) |
在事務B中有兩個查詢語句N1和N2，執行的結果是N1=刺猬，N2=重塑，由此可以證明。
交易的隔離等級規定了一個交易中所做的修改，在事務內和事務間的可見性。較低層級的隔離通常可以執行較高的並發，系統開銷也較低。
在SQL 標準中定義了四種交易的隔離級別，分別是讀取未提交（Read Uncommitted）、讀取已提交（Read Committed）、可重複讀取（Repeatable Read）、可串行化（ Serializable）。
為了詳細解釋這四個隔離等級及它們各自發生的現象，假設有兩個交易即將執行，執行內容如下表：
| 時刻 | 事務A | 事務B |
|---|---|---|
| #1 | begin; |
|
| 2 | #begin; | |
| select name from user where id=1; (N1) | ||
| select name from user where id=1;(N2) | ||
| commit; | ||
| #select name from user where id=1;(N3) |
3.1 讀取未提交（Read Uncommitted）
。 在上述場景中，若資料庫的隔離等級為讀取未提交，由於事務A可以讀取未提交事務B修改後的數據，即時刻3中事務B的修改對事務A可見，所以N1=重塑，N2=重塑，N3=重塑。
3.2 讀取已提交（Read Committed）
。 在上述場景中，若資料庫的隔離等級為讀取已提交，由於事務A只能讀取事務B提交後的數據，即時刻3中事務B的修改對事務A不可見，N2處的查詢在事務B提交之後，故對事務A可見。所以N1=刺猬，N2=重塑，N3=重塑。
3.3 可重複讀取（Repeatable Read）
可重複讀取是 MySQL 的預設交易隔離等級。在可重複讀取的隔離等級下，一個交易中多次查詢相同的記錄，結果總是一致的。 在上述場景中，若資料庫的隔離等級為可重複讀，由於查詢N1和N2在一個事務中，所以它們的值都是「刺蝟」，而N3是在事務A提交以後再進行的查詢，事務B的修改是可見的，所以N3=重塑。
3.4 可串列化（Serializable）
，交易不會並發執行，所以也就不會發生異常。 在上述場景中，若資料庫的隔離等級為可串行化，首先開啟事務A，在開啟事務B時被阻塞，直到事務A提交之後才會開啟事務B，所以N1=刺猬，N2=刺蝟。而N3處的查詢會在事務B提交之後才執行（事務B先被阻塞，執行順序在N3查詢語句之前），所以N3=重塑。
4. 隔離等級導致的問題
4.1 髒讀（Dirty Read）
髒讀（Dirty Read）是指一個交易可以讀取另一個未提交交易修改的資料。 看下面的案例，假設隔離等級為讀取未提交：
| #交易A | 。交易B | ||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| begin; | |||||||||||||||||||||||||||||
|
begin; |
|||||||||||||||||||||||||||||
|
update user set name='重塑' where id=1; |
|||||||||||||||||||||||||||||
| select name from user where id=1;(N1) | |
||||||||||||||||||||||||||||
|
rollback; |
|||||||||||||||||||||||||||||
| select name from user where id=1;(N2) | |||||||||||||||||||||||||||||
| commit; | |
| 交易A | 事務B | |
|---|---|---|
| 1 | #begin; | |
| 2 | begin; | |
| #3 | update user set name='重塑' where id=1; | |
| #4 | select name from user where id=1;(N1) | |
| #5 | #commit; | |
| 6 | select name from user where id=1;(N2) | |
| 7 | #commit; | |
8select name from user where id=1;(N3)
| 不可重複讀取會發生在讀取未提交、讀取已提交的隔離等級。 | 4.3 幻讀（Phantom Read） | 幻讀 |
|---|---|---|
| ，當先前的事務再次讀取這個範圍的記錄時，會讀到這條新記錄。 | 看下面的案例，假設此時隔離等級為可重複讀取： | |
| #交易A | #事務B | |
| begin; | ||
| select name from user;(N1) | ||
| # #begin; |
4 | |
| insert into user values(2, '五條人'); | ||
| commit; | ||
事務A有三次查詢，在N1和N2之間，事務B執行了一條 insert語句並提交，N3處的查詢使用的是 for update。
N1處的結果很顯然只有“刺猬”，N2處的結果由於事務A開啟在事務B之前，所以也是“刺猬”，而N3處的結果理論上在可重複讀的隔離級別中也應該只有「刺蝟」，但實際上N2的結果是「刺蝟」和「五條人」，這就發生了幻讀。
這就很奇怪了，不是說可重複讀的隔離等級能夠保證一個事務中多次查詢相同的記錄，結果總是一致的嗎？這種結果並不滿足可重複讀的定義。
事實上，在可重複讀的隔離等級下，如果使用的是目前讀取，那麼就可能發生幻讀現象。
當前讀和快照讀會在下文中介紹事務的實現原理及 MVCC 時討論，這裡先給一個結論。
幻讀會發生在讀取未提交、讀取已提交、可重複讀取的隔離等級。
這裡需要額外注意的是：幻讀和不可重複讀都是說在一個事務中的同一個查詢語句結果不同，但幻讀更著重於查詢到其他交易新插入的資料（insert）或其他交易刪除的資料（delete），而不可重複讀的範圍更廣，只要結果不同就可以認為是不可重複讀，但一般我們認為不可重複讀更著重於其他事務對資料的更新（update）。
透過上面的描述，我們已經知道四種隔離等級的概念以及它們分別會遇到的問題，事務的隔離等級越高，隔離性就越強，所遇到的問題也就越少。但同時，隔離等級越高，並發能力越弱。
下表是隔離等級的概念不同隔離等級會發生的問題情況的小結：
| #髒讀 | 不可重複讀取 | 幻讀 | 概念 | |
|---|---|---|---|---|
| 已提交 | ||||
| √ | √ | 交易中的修改，即使沒有提交，對其他交易也都是可見的讀未提交 | ||
| √ | √ | #交易中的修改只有在提交之後，才會對其他交易可見可重複讀取 | ||
| #√ | 一個交易中多次查詢相同的記錄，結果總是一致的可串行化 |
MVCC（Multi-Version Concurrency Control）即多版本并发控制，这是 MySQL 为了提高数据库并发性能而实现的。它可以在并发读写数据库时，保证不同事务的读-写操作并发执行，同时也能解决脏读、不可重复读、幻读等事务隔离问题。
在前文讨论幻读的时候提到过当前读的概念，正是由于当前读，才会在可重复读的隔离级别下也会发生幻读的情况。
在解释可重复读隔离级别下发生幻读的原因之前，首先介绍 MVCC 的实现原理。
首先我们需要知道，InnoDB 的数据页中每一行的数据是有隐藏字段的：
DB_ROW_ID: 隐式主键，若表结构中未定义主键，InnoDB 会自动生成该字段作为表的主键DB_TRX_ID: 事务ID，代表修改此行记录的最后一次事务IDDB_ROLL_PTR: 回滚指针，指向此行记录的上一个版本（上一个事务ID对应的记录）每一条修改语句都会相应地记录一条回滚语句（undo log），如果把每一条回滚语句视为一条数据表中的记录，那么通过事务ID和回滚指针就可以将对同一行的修改记录看作一个链表，链表上的每一个节点就是一个快照版本，这就是 MVCC 中多版本的意思。
举个例子，假设对 user 表中唯一的一行「刺猬」进行多次修改。
update user set name='重塑' where id=1;update user set name='木马' where id=1;update user set name='达达' where id=1;复制代码
那么这条记录的我所理解的MySQL之四：事務、隔離等級及MVCC就是：
在这个我所理解的MySQL之四：事務、隔離等級及MVCC中，头结点就是当前记录的最新版本。DB_TRX_ID 事务ID 字段是非常重要的属性，先 Mark 一下。
除此之外，在读已提交（RC，Read Committed）和可重复读（RR，Repeatable Read）的隔离级别中，事务在启动的时候会创建一个读视图（Read View），用它来记录当前系统的活跃事务信息，通过读视图来进行本事务之间的可见性判断。
在读视图中有两个重要的属性：
需要注意下一个事务I的值，并不是事务ID列表中的最大值+1，而是当前系统中已存在过的事务的最大值+1。例如当前数据库中活跃的事务有(1,2)，此时事务2提交，同时又开启了新事务，在生成的读视图中，下一个事务ID的值为3。
我们通过将我所理解的MySQL之四：事務、隔離等級及MVCC与读视图两者结合起来，来进行并发事务间可见性的判断，判断规则如下（假设现在要判断事务A是否可以访问到事务B的修改记录）：
当前事务ID小于事务A的最小事务ID的值，代表事务B是在事务A生成读视图之前就已经提交了的，所以事务B对于事务A来说是可见的。当前事务ID大于或等于事务A下一个事务ID的值，代表事务B是在事务A生成读视图之后才开启，所以事务B对于事务A来说是不可见的。当前事务ID在事务A的最小事务ID和下一个事务ID之间（左闭右开，[最小事务ID, 下一个事务ID)），需要分两种情况讨论：当前事务ID在事务A的事务ID列表中，代表创建事务A时事务B还是活跃的，未提交，所以事务B对于事务A来说是不可见的。当前事务ID不在事务A的事务ID列表中，代表创建事务A时事务B已经提交，所以事务B对于事务A来说是可见的。如果事务B对于事务A来说是不可见的，就需要顺着修改记录的我所理解的MySQL之四：事務、隔離等級及MVCC，从回滚指针开始往前遍历，直到找到第一个对于事务A来说是可见的事务ID，或者遍历完我所理解的MySQL之四：事務、隔離等級及MVCC也未找到（表示这条记录对事务A不可见）。
这就是 MVCC 的实现原理。
這裡需要注意的是讀取視圖的創建時機，在上面的論述中我們已經知道事務在啟動時會創建一個讀取視圖（Read View），而開啟一個事務有兩種方式，一是begin/start transaction，二是start transaction with consistent snapshot#，透過這兩種方式開啟事務，建立讀取視圖的時機也是不同的：
begin/start transaction 方式開啟事務，讀取視圖會在執行第一個快照讀語句時創建start transaction with consistent snapshot 方式開啟事務，同時便會建立讀取視圖為了詳細說明MVCC 的運行過程，以下舉個例子，假設目前存在有兩個交易（交易隔離等級為MySQL 預設的可重複讀取）：
這裡需要注意的是事務的啟動時機，在上面的論述中我們已經知道事務在啟動時會創建一個讀取視圖（Read View），而開啟一個事務有兩種方式，一是
begin/start transaction，二是start transaction with consistent snapshot，透過這兩種方式開啟事務，建立讀取視圖的時機也是不同的：
- #如果是以
begin/start transaction方式開啟事務，讀取視圖會在執行第一個快照讀取語句時建立- 如果以
start transaction with consistent snapshot方式開啟事務，同時便會建立讀取視圖
| #交易A | ##交易B|||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| start transaction with consistent snapshot; | |||||||||||||||||||||||||||||
| select name from user where id=1;(N1) | |||||||||||||||||||||||||||||
| select name from user where id=1;(N2) | |||||||||||||||||||||||||||||
| commit; |
| 「時刻」 | ##交易B | |
|---|---|---|
| begin; | ||
| select name from user;(N1) | ||
|
#insert into user values(2, '五條人'); |
||
|
commit; |
||
| select name from user;(N2) | ||
| select name from user for update;(N3) | ||
| commit; |
以上是我所理解的MySQL之四：事務、隔離等級及MVCC的詳細內容。更多資訊請關注PHP中文網其他相關文章！
