I Failed the Turing Test

Locking and MVCC in MySQL InnoDB 鎖機制與多版本並發控制

Posted on Author VintaPosted in Database

最近在研究 MySQL 5.6 / 5.7 InnoDB storage engine 的 Transaction、Locking 和 Multi-Version Concurrency Control (MVCC) 機制，基本上只討論 Isolation level 是 REPEATABLE READ 或 READ COMMITTED 的情況，這一篇是過程中記錄下來的筆記。

ref:
https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html

Transaction

Transaction Isolation Levels
https://vinta.ws/code/transaction-isolation-levels-in-databases.html

在 autocommit 啟用的情況下（默認），每一個 SQL 語句就是一個 transaction，但是你可以用 START TRANSACTION 或 BEGIN 來手動開啟一個 transaction，然後用 COMMIT 或 ROLLBACK 來結束。

ref:
https://dev.mysql.com/doc/refman/5.7/en/innodb-autocommit-commit-rollback.html

Pessimistic Locking 悲觀鎖 / Optimistic Locking 樂觀鎖

更準確一點的說法是 pessimistic concurrency control 和 optimistic concurrency control。

基本上悲觀鎖、樂觀鎖是一種方法論（或者說是策略），在不同的系統可能有不同的作法，不過這裡就只討論 MySQL。

悲觀鎖假設你在存取某個資料時，其他人同時修改同一份資料的機率很高，也就是對「資料會被修改」這件事持悲觀的態度，所以要在存取（無論讀寫）時就把該資料鎖住，不讓其他人有機會修改。悲觀鎖通常指的是資料庫本身提供的 locking 機制（在 application 層的鎖沒辦法保證不會被修改）。SELECT ... FOR UPDATE; 就是一種悲觀鎖。

樂觀鎖則是對「資料會被修改」這件事持樂觀的態度，通常會使用 version 版本號的方式來實作，在 InnoDB 裡就是指 Multi-Version Concurrency Control (MVCC)。

You can remember which is which by picturing it this way:

  • The pessimistic approach is a user thinking "I’m sure someone will try to update this row at the same time as I will, so I better ask the database server to not let anyone touch it until I’m done with it."
  • The optimistic approach is a user thinking "I’m sure it’s all good and there will be no conflicts, so I’m just going to remember this row to double-check later, but I’ll let the database server do whatever it wants with the row."

ref:
http://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking
https://blogs.msdn.microsoft.com/marcelolr/2010/07/16/optimistic-and-pessimistic-concurrency-a-simple-explanation/

Multi-Version Concurrency Control (MVCC)

在 InnoDB 裡是基本上是讀不加鎖，寫才加鎖，而且讀寫不互斥。

InnoDB 會把每個 row 的歷史版本儲存在 rollback segment 裡，rollback segment 的資料除了用在 transaction rollback 的 undo 操作外，會用被用在 consistent read，也就是 snapshot data。

ref:
https://dev.mysql.com/doc/refman/5.7/en/innodb-multi-versioning.html

在所有 InnoDB 的 tables 裡，每個 row 都會有三個隱藏的欄位 DB_ROW_ID、DB_TRX_ID 和 DB_ROLL_PTR：DB_TRX_ID 是建立這個版本的數據的那個 transaction id、DB_ROLL_PTR 就是 rollback data pointer 指向在 undo logs 裡的前一個版本的數據。

TODO:
多說一點細節
例如 SELECT、INSERT、UPDATE 和 DELETE 時的行為

Consistent (Nonlocking) Reads

所謂的 consistent read 是指 read 操作實際上是去讀 snapshot data（該 row 在不同時間點的數據、版本），所以 read 操作不會因為該 row 正在被其他 transaction 修改而被 block 住，snapshot data 就是當前數據的歷史版本。

如果 isolation level 是 REPEATABLE READ 或 READ COMMITTED 的話，SELECT 操作默認就是 consistent read，所以不會加上任何 lock。除非你顯式地加上 SELECT ... LOCK IN SHARE MODE 或 SELECT ... FOR UPDATE（稱為 locking reads）。

ref:
https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_consistent_read
https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_snapshot
https://blogs.oracle.com/mysqlinnodb/entry/repeatable_read_isolation_level_in

至於 INSERT、UPDATE 和 DELETE 操作，MySQL 則會自動對牽涉到的數據加上 lock。

ref:
https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

不同的 isolation level 在讀 snapshot data 的行為上是不同的。

REPEATABLE READ 在同一個 transaction 內，對同一個 row 的所有 consistent read 讀到的內容都會是第一個 consistent read 時建立的那個 snapshot data，即使其他 transaction 已經 commit 了新的資料，所以才叫 repeatable read。不過如果是在同一個 transaction 內所做的修改則還是會去讀那個修改過的內容，因為是去讀 snapshot。而且 MySQL 的 REPEATABLE READ 還額外地避免了 phantom read。

READ COMMITTED 在同一個 transaction 內，每次去讀同一個 row 都會建立並且讀最新的一份 snapshot data（當然只會讀已經 commit 的資料），先後的 read 可能會讀到不同的內容，所以才會有 non-repeatable read 的問題。

SERIALIZABLE 是讀加讀鎖，寫加寫鎖，而且讀寫互斥。

ref:
https://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html#isolevel_repeatable-read
https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html#isolevel_read-committed
https://blog.gslin.org/archives/2015/09/27/6007/%E7%B9%BC%E7%BA%8C%E5%AD%B8-isolation/

雖然 MySQL 默認的 isolation level 是 REPEATABLE READ，但是如果你的專案其實不太需要 repeatable reads 的話，用 READ COMMITTED 可能更合適，至少 lock 會比較少。不過 REPEATABLE READ 和 READ COMMITTED 在效能上的差異似乎也沒有想像中那麼大就是了。

ref:
https://www.percona.com/blog/2015/01/14/mysql-performance-implications-of-innodb-isolation-modes/
https://smalldatum.blogspot.tw/2016/11/sysbench-innodb-transaction-isolation.html

Snapshot Read / Current Read

也有一種說法是把「讀」分成 snapshot read 和 current read，不過這種說法似乎只有在中國程序員寫的文章裡流傳，至少我沒有在官方的文件裡找到這兩個名詞。

snapshot read，就是去讀 snapshot data：

  • SELECT

current read，就是去讀最新版本的數據：

  • SELECT ... LOCK IN SHARE MODE
  • SELECT ... FOR UPDATE
  • INSERT
  • UPDATE
  • DELETE

ref:
http://hedengcheng.com/?p=771
http://tech.meituan.com/innodb-lock.html

Lock Modes

Shared Locks 共享鎖 / Exclusive Locks 排他鎖

shared lock (S lock)，也稱為「共享鎖」、「讀鎖」。被加上讀鎖的數據，其他 transaction 可以讀，也可以再加上讀鎖，但是不能寫（因為不能加上寫鎖）。

可以用 SELECT ... LOCK IN SHARE MODE; 顯式地加上 shared lock。MySQL 會對被 SELECT 到的每個 row 加上 shared lock，當然前提是這些 row 沒有被加上 exclusive lock。

ref:
https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_shared_lock

exclusive lock (X lock)，也稱為「排他鎖」、「寫鎖」。被加上寫鎖的數據，其他 transaction 可以讀（因為讀是讀 snapshot data），但是不能再加上任何鎖，所以不能寫（因為不能加上寫鎖），只有加上寫鎖的那個 transaction 才能寫。

可以用 SELECT ... FOR UPDATE; 顯式地加上 exclusive lock，MySQL 會對被 SELECT 到的每個 row 加上 exclusive lock。

ref:
https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_exclusive_lock

Intention Locks 意向鎖

分成 intention shared locks (IS) 和 intention exclusive locks (IX)，用來表示某個 transaction 打算對某個 table 裡的某個 row 加上 shared lock 或 exclusive lock。更明確地說，某個 transaction 必須先取得 table 的 intention lock，它才能對該 table 裡的 row 加上 shared lock 或 exclusive lock。

intention locks 是 MySQL 自動加的，你不需要也沒辦法自己加上這種鎖。

ref:
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

另外還有一種 table-level locks，可以透過 LOCK TABLE 顯式地加上：

LOCK TABLES t1 READ;
LOCK TABLES t1 WRITE;

ref:
https://dev.mysql.com/doc/refman/5.7/en/lock-tables.html

Online DDL (Data Definition Language)

ALTER TABLE 操作可能也會有 table-level locks，在 ALTER TABLE 的過程中只能讀，寫則會被 block 直到 ALTER TABLE 完成。不過這點在 MySQL 5.6 / 5.7 以後改善了不少，例如可以用 ALTER TABLE ... ALGORITHM=INPLACE LOCK=NONE; 來指定不同的行為。

ref:
https://dev.mysql.com/doc/refman/5.7/en/alter-table.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html

Summary of Online Status for DDL Operations
https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html

Lock Types

Locks Set by Different SQL Statements in InnoDB
https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

Record Locks

record lock 就是加在 index 上的 lock，InnoDB 裡的 row-level lock 實際上是 index 的 record lock。使用 READ COMMITTED 的時候，locking read（即 LOCK IN SHARE MODE 和 FOR UPDATE）、UPDATE 和 DELETE 的 lock 都是 record lock。

TODO:
如果該 table 沒有任何 index

READ COMMITTED 只有 record lock，沒有 gap lock 和 next-key lock，不過 foreign-key constraint 和 duplicate-key 檢查還是會用到 gap lock。

REPEATABLE READ 有 record lock、gap lock 和 next-key lock。

ref:
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-record-locks
https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html#isolevel_read-committed

Gap Locks

gap lock 是加在 index records 之間的 gap 上的 lock。

ref:
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-gap-locks

Next-Key Locks

next-key lock 是 record lock + gap lock，next-key lock 可以用來避免 phantom read。

ref:
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-next-key-locks
https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html

Insert Intention Locks

insert intention lock 是 gap lock 的一種，INSERT 之前會先加一個 insert intention lock，但是只要不是要 INSERT 到同一個位置上就不會 block 其他 transaction。

ref:
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-insert-intention-locks

AUTO-INC Locks

auto-inc lock 是一種 table-level lock，不同種類的 insert-like 語句會有不同的行為。可以透過 innodb_autoinc_lock_mode 設定來改變這些行為。

在 innodb_autoinc_lock_mode = 1（預設值）的情況下，simple inserts（就是那種在執行之前就知道最後會產生幾筆新資料的 inserts）不會有 table-level 的 AUTO-INC lock，只有在取得 auto-increment 值的那瞬間會有 lock。

ref:
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-auto-inc-locks
https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_mode