連mysql鎖的機制都不了解，怎麼做架構師

引言

數據庫鎖定機制簡單來說就是數據庫為了保證數據的一致性而使各種共享資源在被併發訪問變得有序所設計的一種規則；對於任何一種數據庫來說都需要有相應的鎖定機制，Mysql也不例外。

mysql 事務

一般來說，事務是必須滿足4個條件（ACID）：原子性（Atomicity，或稱不可分割性）、一致性（Consistency）、隔離性（Isolation，又稱獨立性）、持久性（Durability）。

1. 原子性：一個事務（transaction）中的所有操作，要麼全部完成，要麼全部不完成，不會結束在中間某個環節。事務在執行過程中發生錯誤，會被回滾（Rollback）到事務開始前的狀態，就像這個事務從來沒有執行過一樣。
2. 一致性：在事務開始之前和事務結束以後，數據庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預設規則，這包含資料的精確度、串聯性以及後續數據庫可以自發性地完成預定的工作。
3. 隔離性：數據庫允許多個併發事務同時對其數據進行讀寫和修改的能力，隔離性可以防止多個事務併發執行時由於交叉執行而導致數據的不一致。事務隔離分為不同級別，包括讀未提交（Read uncommitted）、讀提交（read committed）、可重複讀（repeatable read）和串行化（Serializable）。
4. 持久性：事務處理結束後，對數據的修改就是永久的，即便系統故障也不會丟失。

MYSQL 事務處理主要有兩種方法：

1. 用 BEGIN, ROLLBACK, COMMIT來實現
2. 1)、BEGIN 開始一個事務
3. 2)、ROLLBACK 事務回滾
4. 3)、COMMIT 事務確認
5. 直接用 SET 來改變 MySQL 的自動提交模式:
6. 1)、SET AUTOCOMMIT=0 禁止自動提交
7. 2)、SET AUTOCOMMIT=1 開啟自動提交

事務隔離級別

mysql事務隔離級別分為如下4種：

(1)讀不提交(Read Uncommited，RU)

這種隔離級別下，事務間完全不隔離，會產生髒讀，可以讀取未提交的記錄，實際情況下不會使用。

(2)讀提交(Read commited，RC)

僅能讀取到已提交的記錄，這種隔離級別下，會存在幻讀現象，所謂幻讀是指在同一個事務中，多次執行同一個查詢，返回的記錄不完全相同的現象。幻讀產生的根本原因是，在RC隔離級別下，每條語句都會讀取已提交事務的更新，若兩次查詢之間有其他事務提交，則會導致兩次查詢結果不一致。雖然如此，讀提交隔離級別在生產環境中使用很廣泛。

(3)可重複讀(Repeatable Read, RR)

可重複讀隔離級別解決了不可重複讀的問題，但依然沒有解決幻讀的問題。那麼不可重複讀與幻讀有什麼區別呢？不可重複讀重點在修改，即讀取過的數據，兩次讀的值不一樣；而幻讀則側重於記錄數目變化【插入和刪除】。一般教科書上告訴我們只有到串行化隔離級別才解決幻讀問題，但mysql的innodb比較特殊，RR即解決了幻讀問題，主要通過GAP鎖實現。另外，不是所有的數據庫都實現了該隔離級別，後面會簡單介紹下mysql是如何實現可重複讀隔離級別的。

(4)串行化(Serializable)

在串行化隔離模式下，消除了髒讀，幻象，但事務併發度急劇下降，事務的隔離級別與事務的併發度成反比，隔離級別越高，事務的併發度越低。實際生產環境下，dba會在併發和滿足業務需求之間作權衡，選擇合適的隔離級別。

mysql 鎖類型

MySQL 各存儲引擎使用了三種類型（級別）的鎖定機制：行級鎖定，頁級鎖定和表級鎖定。

1、行級鎖定

鎖定對象的顆粒度很小，只對當前行進行鎖定，所以發生鎖定資源爭用的概率也最小，能夠給予應用程序儘可能大的併發處理能力；弊端就是獲取鎖釋放鎖更加頻繁，系統消耗更大，同時行級鎖定也最容易發生死鎖；

行級鎖定的主要是Innodb存儲引擎和NDB Cluster存儲引擎；

2、表級鎖定

一次會將整張表鎖定，該鎖定機制最大的特點是實現邏輯非常簡單，帶來的系統負面影響最小，而且可以避免死鎖問題；弊端就是鎖定資源爭用的概率最高，併發處理能力最低；

使用表級鎖定的主要是MyISAM，Memory，CSV等一些非事務性存儲引擎。

鎖定顆粒度介於行級鎖定與表級鎖之間，每頁有多行數據，併發處理能力以及獲取鎖定所需要的資源開銷在兩者之間；

頁級鎖定主要是BerkeleyDB 存儲引擎；

兩段鎖協議(2PL)

兩段鎖協議規定所有的事務應遵守的規則：

1.在對任何數據進行讀、寫操作之前，首先要申請並獲得對該數據的封鎖；

2.在釋放一個封鎖之後，事務不再申請和獲得其它任何封鎖；

即事務的執行分為兩個階段：

第一階段是獲得封鎖的階段，稱為擴展階段；第二階段是釋放封鎖的階段，稱為收縮階段；

1begin;insert ... 加鎖1 update ... 加鎖2 commit; 事務提交時，釋放鎖1，鎖2

如果在加鎖2的時候，加鎖不成功，則進入等待狀態，直到加鎖成功才繼續執行；

如果有另外一個事務獲取鎖的時候順序剛好相反，是有可能導致死鎖的；為此有了一次性封鎖法，要求事務必須一次性將所有要使用的數據全部加鎖，否則就不能繼續執行；

鎖模式

Innodb 實現了兩種類型的行鎖：

1. 共享鎖（S）：允許一個事務去讀一行，阻止其他事務獲得相同數據集的排他鎖。
2. 排他鎖（X)：允許獲得排他鎖的事務更新數據，阻止其他事務取得相同數據集的共享讀鎖和排他寫鎖。

另外，為了允許行鎖和表鎖共存，實現多粒度鎖機制，InnoDB還有兩種內部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖。

1. 意向共享鎖（IS）：事務打算給數據行加行共享鎖，事務在給一個數據行加共享鎖前必須先取得該表的IS鎖。
2. 意向排他鎖（IX）：事務打算給數據行加行排他鎖，事務在給一個數據行加排他鎖前必須先取得該表的IX鎖。

如果一個事務請求的鎖模式與當前的鎖兼容，innodb 就將請求的鎖授予該事務；反之，如果兩者不兼容，該事務就要等待鎖釋放。

意向鎖是 innodb 自動加的，不需要用戶干預。對於 update、delete 和 insert 語句，innodb 會自動給涉及數據集加排它鎖（X）；對於普通 select 語句，innodb 不會加任何鎖。

事務可以通過以下語句顯式給記錄集加共享鎖或排它鎖。

共享鎖（S）：select * from table_name where … lock in share mode.

排它鎖（X）: select * from table_name where … for update.

用 select… in share mode 獲得共享鎖，主要用在需要數據依存關係時來確認某行記錄是否存在，並確保沒有人對這個記錄進行 update 或者 delete 操作。但是如果當前事務也需要對該記錄進行更新操作,則有可能造成死鎖，對於鎖定行記錄後需要進行更新操作的應用，應該使用 select… for update 方式獲得排他鎖。

事務隔離級別和鎖的關係

數據庫隔離級別：未提交讀(Read uncommitted)，已提交讀(Read committed)，可重複讀(Repeatable read)和可串行化(Serializable)；

1. 未提交讀(Read uncommitted)：可能讀取到其他會話中未提交事務修改的數據，會出現髒讀(Dirty Read)；
2. 已提交讀(Read committed)：只能讀取到已經提交的數據，會出現不可重複讀(NonRepeatable Read)；
3. 可重複讀(Repeatable read)：InnoDB默認級別，不會出現不可重複讀(NonRepeatable Read)，但是會出現幻讀(Phantom Read);
4. 可串行化(Serializable)：強制事務排序，使之不可能相互衝突，從而解決幻讀問題，使用表級共享鎖，讀寫相互都會阻塞；

常用的2種隔離級別是：已提交讀(Read committed)和可重複讀(Repeatable read)；mysql的默認隔離級別是可重複讀。

行鎖的實現方式

InnoDB行鎖是通過給索引上的索引項加鎖來實現的，這一點MySQL與Oracle不同，後者是通過在數據塊中對相應數據行加鎖來實現的。InnoDB這種行鎖實現特點意味著：只有通過索引條件檢索數據，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！在實際應用中，要特別注意InnoDB行鎖的這一特性，不然的話，可能導致大量的鎖衝突，從而影響併發性能。

由於MySQL的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會出現鎖衝突的

當表有多個索引的時候，不同的事務可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB都會使用行鎖來對數據加鎖。如果不同的索引碰巧都落到了同一個行上，那麼同樣會阻塞。

即便在條件中使用了索引字段，但是否使用索引來檢索數據是由MySQL通過判斷不同執行計劃的代價來決定的，如果MySQL認為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，這種情況下InnoDB將使用表鎖，而不是行鎖。因此，在分析鎖衝突時，別忘了檢查SQL的執行計劃，以確認是否真正使用了索引。

間隙鎖

當我們用範圍條件而不是相等條件檢索數據，並請求共享或排他鎖時，InnoDB會給符合條件的已有數據記錄的索引項加鎖；對於鍵值在條件範圍內但並不存在的記錄，叫做“間隙（GAP)”，InnoDB也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖（Next-Key鎖）。

舉例來說，假如emp表中只有101條記錄，其empid的值分別是 1,2,…,100,101，下面的SQL：

Select * from emp where empid > 100 for update;

是一個範圍條件的檢索，InnoDB不僅會對符合條件的empid值為101的記錄加鎖，也會對empid大於101（這些記錄並不存在）的“間隙”加鎖。

InnoDB使用間隙鎖的目的，一方面是為了防止幻讀，以滿足相關隔離級別的要求，對於上面的例子，要是不使用間隙鎖，如果其他事務插入了empid大於100的任何記錄，那麼本事務如果再次執行上述語句，就會發生幻讀；另外一方面，是為了滿足其恢復和複製的需要

還要特別說明的是，InnoDB除了通過範圍條件加鎖時使用間隙鎖外，如果使用相等條件請求給一個不存在的記錄加鎖，InnoDB也會使用間隙鎖！

MySQL的恢復機制是通過BINLOG記錄來執行IUD操作來同步Slave的，這就要求：在一個事務未提交前，其他併發事務不能插入滿足其鎖定條件的任何記錄，也就是不允許出現幻讀，這已經超過了ISO/ANSI SQL92“可重複讀”隔離級別的要求，實際上是要求事務要串行化。這也是許多情況下，InnoDB要用到間隙鎖的原因，比如在用範圍條件更新記錄時，無論在Read Commited或是Repeatable Read隔離級別下，InnoDB都要使用間隙鎖，但這並不是隔離級別要求的。

INSERT…SELECT…和 CREATE TABLE…SELECT…語句，可能會阻止對源表的併發更新，造成對源表鎖的等待。如果查詢比較複雜的話，會造成嚴重的性能問題，我們在應用中應儘量避免使用。實際上，MySQL將這種SQL叫作不確定（non-deterministic）的SQL，不推薦使用。

測試mysql隔離級別

已提交讀

接下來我們測試下mysql的已提交讀，我們先看下mysql的隔離級別：

1mysql> SELECT @@tx_isolation;

2+----------------+

3| @@tx_isolation |

4+----------------+

5| REPEATABLE-READ |

6+----------------+

設置隔離級別為已提交讀

1set session transaction isolation level read committed;

2Query OK, 0 rows affected (0.02 sec)

3

4mysql> SELECT @@tx_isolation;

5+----------------+

6| @@tx_isolation |

7+----------------+

8| READ-COMMITTED |

9+----------------+

101 row in set (0.01 sec)

準備測試數據:

1CREATE TABLE `test_lock` (

2 `id` int(11) NOT NULL AUTO_INCREMENT,

3 `name` varchar(255) NOT NULL,

4 `type` int(11) DEFAULT NULL,

5 PRIMARY KEY (`id`)

6) ENGINE=InnoDB DEFAULT CHARSET=utf8

7

8mysql> insert into test_lock values(null,'zhaohui',1);

9mysql> insert into test_lock values(null,'zhaohui2',2);

模擬多個事務交叉執行:

Session1執行查詢

1mysql> begin

2 -> ;

3Query OK, 0 rows affected (0.02 sec)

4

5mysql> select * from test_lock where id=1;

6+----+---------+------+

7| id | name | type |

8+----+---------+------+

9| 1 | zhaohui | 1 |

10+----+---------+------+

111 row in set (0.05 sec)

Session2更新數據

1 begin;

2Query OK, 0 rows affected (0.08 sec)

3

4mysql> update test_lock set name='zhaohui_new' where id=1;

5Query OK, 1 row affected (0.02 sec)

6Rows matched: 1 Changed: 1 Warnings: 0

7

8mysql> commit;

9Query OK, 0 rows affected (0.03 sec)

10

Session1執行查詢

1select * from test_lock where id=1;

2+----+-------------+------+

3| id | name | type |

4+----+-------------+------+

5| 1 | zhaohui_new | 1 |

6+----+-------------+------+

71 row in set (0.04 sec)

Session1中出現了不可重複讀(NonRepeatable Read)，也就是在查詢的時候沒有鎖住相關的數據，導致出現了不可重複讀，但是寫入、修改和刪除數據還是加鎖了，如下所示：

Session1更新數據

1mysql> begin;

2Query OK, 0 rows affected (0.01 sec)

3

4mysql> update test_lock set name='zhaohui_new2' where id=1;

5Query OK, 1 row affected (0.01 sec)

6Rows matched: 1 Changed: 1 Warnings: 0

Session2更新數據

1mysql> begin

2 -> ;

3Query OK, 0 rows affected (0.05 sec)

4

5mysql> update test_lock set name='zhaohui_new3' where id=1;

6ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Session2更新在更新同一條數據的時候超時了，在更新數據的時候添加了排他鎖；

可重複讀

查看和設置隔離級別

1mysql> set session transaction isolation level repeatable read;

2Query OK, 0 rows affected (0.07 sec)

3

4mysql> SELECT @@tx_isolation;

5+-----------------+

6| @@tx_isolation |

7+-----------------+

8| REPEATABLE-READ |

9+-----------------+

101 row in set (0.05 sec)

模擬多個事務交叉執行:

Session1執行查詢

1mysql> begin;

2Query OK, 0 rows affected (0.03 sec)

3

4mysql> select * from test_lock where type=2;

5+----+----------+------+

6| id | name | type |

7+----+----------+------+

8| 2 | zhaohui2 | 2 |

9+----+----------+------+

101 row in set (0.02 sec)

Session2更新數據

1mysql> begin;

2Query OK, 0 rows affected (0.05 sec)

3

4mysql> update test_lock set name='zhaohui2_new' where type=2;

5Query OK, 1 row affected (0.03 sec)

6Rows matched: 1 Changed: 1 Warnings: 0

7

8mysql> commit;

9Query OK, 0 rows affected (0.06 sec)

Session1執行查詢

1mysql> select * from test_lock where type=2;

2+----+----------+------+

3| id | name | type |

4+----+----------+------+

5| 2 | zhaohui2 | 2 |

6+----+----------+------+

71 row in set (0.03 sec)

可以發現2次查詢的數據結果是一樣的，實現了可重複讀(Repeatable read)，再來看一下是否有幻讀的問題；

Session3插入數據

1mysql> begin;

2Query OK, 0 rows affected (0.01 sec)

3

4mysql> insert into test_lock(name,type) values('zhaohui3',2);

5Query OK, 1 row affected (0.02 sec)

6

7mysql> commit;

8Query OK, 0 rows affected (0.02 sec)

Session1執行查詢

1mysql> select * from test_lock where type=2;

2+----+----------+------+

3| id | name | type |

4+----+----------+------+

5| 2 | zhaohui2 | 2 |

6+----+----------+------+

71 row in set (0.01 sec)

這裡發現一個奇怪的現象,按理說應該會出現兩條記錄，但是mysql在重複讀的隔離級別下沒有出現幻讀的情況；

我們知道innodb默認是行鎖級別，如何通過悲觀鎖的方式去實現可重複讀和不出現幻讀的現象，對讀取的數據加共享鎖，對同樣的數據執行更新操作就只能等待，這樣就可以保證可重複讀，但是對於不出現幻讀的現象無法通過鎖定行數據來解決；

最終看到的現象是沒有幻讀的問題，同時如果對讀取的數據加共享鎖，更新相同數據應該會等待，上面的實例中並沒有出現等待，所以mysql內部應該還有其他鎖機制--MVCC機制；

共享鎖使用

Session1查詢數據

1mysql> begin;

2Query OK, 0 rows affected (0.03 sec)

3

4mysql> select * from test_lock where type=2 lock in share mode;

5+----+--------------+------+

6| id | name | type |

7+----+--------------+------+

8| 2 | zhaohui2_new | 2 |

9| 3 | zhaohui3 | 2 |

10+----+--------------+------+

112 rows in set (0.15 sec)

Session2查詢數據

1mysql> begin;

2Query OK, 0 rows affected (0.01 sec)

3

4mysql> select * from test_lock where type=2 lock in share mode;

5+----+--------------+------+

6| id | name | type |

7+----+--------------+------+

8| 2 | zhaohui2_new | 2 |

9| 3 | zhaohui3 | 2 |

10+----+--------------+------+

112 rows in set (0.05 sec)

Session3 更新數據

1mysql> begin;

2Query OK, 0 rows affected (0.02 sec)

3

4mysql> update test_lock set name='zhaohui3_new' where id=3;

5ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Session1和Session2使用了共享鎖，所以可以存在多個，並不衝突，但是Session3更新操作需要加上排他鎖，和共享鎖不能同時存在；

排他鎖使用

Session1查詢數據

1mysql> begin;

2Query OK, 0 rows affected (0.07 sec)

3

4mysql> select * from test_lock where type=2 for update;

5+----+--------------+------+

6| id | name | type |

7+----+--------------+------+

8| 2 | zhaohui2_new | 2 |

9| 3 | zhaohui3 | 2 |

10+----+--------------+------+

112 rows in set (15.02 sec)

Session2查詢數據

1mysql>begin;

2Query OK, 0 rows affected (0.07 sec)

3

4mysql> select * from test_lock where type=2 for update;

5ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

排他鎖只能有一個同時存在，如果session1獲取了鎖，那麼session2將會等待超時。

mysql MVCC

MVCC 簡介

MVCC (Multiversion Concurrency Control)，即多版本併發控制技術,它使得大部分支持行鎖的事務引擎，不再單純的使用行鎖來進行數據庫的併發控制，取而代之的是把數據庫的行鎖與行的多個版本結合起來，只需要很小的開銷,就可以實現非鎖定讀，從而大大提高數據庫系統的併發性能

MVCC 實現

MVCC是通過保存數據在某個時間點的快照來實現的. 不同存儲引擎的MVCC. 不同存儲引擎的MVCC實現是不同的,典型的有樂觀併發控制和悲觀併發控制.

InnoDB的MVCC,是通過在每行記錄後面保存兩個隱藏的列來實現的,這兩個列，分別保存了這個行的創建時間，一個保存的是行的刪除時間。這裡存儲的並不是實際的時間值,而是系統版本號(可以理解為事務的ID)，沒開始一個新的事務，系統版本號就會自動遞增，事務開始時刻的系統版本號會作為事務的ID.

innodb存儲的最基本row中包含一些額外的存儲信息 DATA_TRX_ID，DATA_ROLL_PTR，DB_ROW_ID，DELETE BIT

1. 6字節的DATA_TRX_ID 標記了最新更新這條行記錄的transaction id，每處理一個事務，其值自動+1
2. 7字節的DATA_ROLL_PTR 指向當前記錄項的rollback segment的undo log記錄，找之前版本的數據就是通過這個指針
3. 6字節的DB_ROW_ID，當由innodb自動產生聚集索引時，聚集索引包括這個DB_ROW_ID的值，否則聚集索引中不包括這個值.，這個用於索引當中
4. DELETE BIT位用於標識該記錄是否被刪除，這裡的不是真正的刪除數據，而是標誌出來的刪除。真正意義的刪除是在commit的時候

具體的執行過程

begin->用排他鎖鎖定該行->記錄redo log->記錄undo log->修改當前行的值，寫事務編號，回滾指針指向undo log中的修改前的行

上述過程確切地說是描述了UPDATE的事務過程，其實undo log分insert和update undo log，因為insert時，原始的數據並不存在，所以回滾時把insert undo log丟棄即可，而update undo log則必須遵守上述過程

下面分別以select、delete、 insert、 update語句來說明：

SELECT

Innodb檢查每行數據，確保他們符合兩個標準：

1、InnoDB只查找版本早於當前事務版本的數據行(也就是數據行的版本必須小於等於事務的版本)，這確保當前事務讀取的行都是事務之前已經存在的，或者是由當前事務創建或修改的行

2、行的刪除操作的版本一定是未定義的或者大於當前事務的版本號，確定了當前事務開始之前，行沒有被刪除

符合了以上兩點則返回查詢結果。

InnoDB為每個新增行記錄當前系統版本號作為創建ID。

DELETE

InnoDB為每個刪除行的記錄當前系統版本號作為行的刪除ID。

UPDATE

InnoDB複製了一行。這個新行的版本號使用了系統版本號。它也把系統版本號作為了刪除行的版本。

這裡簡單做下總結：

1. insert操作時 “創建時間”=DB_ROW_ID，這時，“刪除時間 ”是未定義的；
2. update時，複製新增行的“創建時間”=DB_ROW_ID，刪除時間未定義，舊數據行“創建時間”不變，刪除時間=該事務的DB_ROW_ID；
3. delete操作，相應數據行的“創建時間”不變，刪除時間=該事務的DB_ROW_ID；
4. select操作對兩者都不修改，只讀相應的數據

閱讀更多 科技伍小黑 的文章

關鍵字: 保證數據 共享資源 數據庫