標籤

為了瞭解 MySQL Lock 使用時機 ，我幻想了一個訂票系統 | Piece of DevOps


每個 Database 最讓人好奇，但也最難一下子理解的，就是其中使用到的鎖（Lock）。在瞭解 MySQL 的鎖時，發現在 InnoDB 儲存引擎下（MySQL 預設使用），我們可以在 SELECT 查詢指令中，加入特定的關鍵字，來決定我們要使用什麼鎖來鎖定搜尋過的資料。關鍵字分別為：
  • LOCK IN SHARE MODE
  • FOR UPDATE
這兩種關鍵字分別代表兩種鎖，前者是代表 Shared Lock，後者則是 Exclusive Lock。在看過一些文章及官方文件後，大概明白為什麼可以在 SELECT 中，加上這兩把其中一把鎖的原因。原因就在於 InnoDB 在實作 Isolation Level 的 Repeatable Read 時，是以 Snapshot 的方式進行也就是 Multiversion concurrency control（MVCC）模式。此模式是在 Transaction 中，執行第一次的 SELECT 後，開始紀錄資料當下 Snapshot 版本，接下來只要在同 Transaction 內再次執行相同的 SELECT，透過指定當時的 Snapshot 版本，就可以獲得與第一次執行 SELECT 時的相同資料，進而達到 Repeatable Read 的效果。

在上述的情況下，雖然可以保持在同一個 Transaction 內看見的資料是一致的，但卻沒辦法保證資料不被修改，更無法察覺資料是否被修改（因為只會到看 Snapshot 的結果）。沒辦法保證資料不被修改跟無法察覺，會很糟嗎？為了要讓這兩件事感覺起來很糟，所以我幻想了一個訂票系統，以下的例子會讓你知道，可能可以糟到哪去。

三人購票一人免費的活動

慢速鐵路公司推出一個促銷活動。憑三張車票，就可以換一張免費車票。先不討論有誰會想搭慢速鐵路這個根本的問題。麻煩的一致性問題，就是那麼樸實無華。

車票使用的表（tickets）

用來紀錄現有的車票。因應接下來的劇情，加入三張票在表中。

tickets

# CREATE
CREATE TABLE tickets (
    id INT NOT NULL AUTO_INCREMENT,
    train_no INT DEFAULT 0,
    car INT DEFAULT 0,
    seat CHAR(5) DEFAULT '',
    price INT NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
);

# DESC
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int     | NO   | PRI | NULL    | auto_increment |
| train_no | int     | YES  |     | 0       |                |
| car      | int     | YES  |     | 0       |                |
| seat     | char(5) | YES  |     |         |                |
| price    | int     | NO   |     | 0       |                |
+----------+---------+------+-----+---------+----------------+

# INSERT 3 TICKETS
INSERT INTO tickets (train_no, car, seat, price) VALUES (123, 8, '1A', 590),
    (123, 8, '1B', 590),
    (123, 8, '1C', 590);

# RESULT
+----+----------+------+------+-------+
| id | train_no | car  | seat | price |
+----+----------+------+------+-------+
|  1 |      123 |    8 | 1A   |   590 |
|  2 |      123 |    8 | 1B   |   590 |
|  3 |      123 |    8 | 1C   |   590 |
+----+----------+------+------+-------+

沒辦法保證資料不被修改

在 MySQL 的 Repeatable Read 模式下，有四位年輕人來到了櫃檯詢問他們是否符合活動條件。其中三個人拿出訂票的 APP 讓櫃檯人員查驗，櫃檯人員對這三張票（也就是剛剛 INSERT 的三張票）進行查詢，確定這些票是否真實存在。確認完後，顯示在櫃檯前的電腦上，以便他們再次確認，此時  Transaction 也己經開始：
# session 1
mysql> BEGIN;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM tickets WHERE id in (1, 2, 3);
+----+----------+------+------+-------+
| id | train_no | car  | seat | price |
+----+----------+------+------+-------+
|  1 |      123 |    8 | 1A   |   590 |
|  2 |      123 |    8 | 1B   |   590 |
|  3 |      123 |    8 | 1C   |   590 |
+----+----------+------+------+-------+
3 rows in set (0.00 sec)
這時候，其中一位狡猾的年輕人在他的手機 APP 上，退掉其中一張票（因為他本來就沒有打算要搭乘那麼慢的東西）：
# session 2
mysql> DELETE FROM tickets WHERE id = 3;
Query OK, 1 row affected (0.01 sec)
在沒有 Lock 的情況下，Delete 毫無猶豫成功執行。

無法察覺資料是否被修改

現在年輕人的詭計已經快得逞了，這時候如果再執行一次 SELECT 能挽回的了什麼嗎？
# session 1
mysql> SELECT * FROM tickets WHERE id in (1, 2, 3);
+----+----------+------+------+-------+
| id | train_no | car  | seat | price |
+----+----------+------+------+-------+
|  1 |      123 |    8 | 1A   |   590 |
|  2 |      123 |    8 | 1B   |   590 |
|  3 |      123 |    8 | 1C   |   590 |
+----+----------+------+------+-------+
3 rows in set (0.00 sec)

mysql> INSERT INTO tickets (train_no, car, seat, price) VALUES (123, 8, '1D', 0);
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
看來什麼也挽回不了，系統也只能依邏輯送給他們一張免費的票。
# session 1
mysql> SELECT * FROM tickets;
+----+----------+------+------+-------+
| id | train_no | car  | seat | price |
+----+----------+------+------+-------+
|  1 |      123 |    8 | 1A   |   590 |
|  2 |      123 |    8 | 1B   |   590 |
|  4 |      123 |    8 | 1D   |     0 |
+----+----------+------+------+-------+
3 rows in set (0.00 sec)

拯救世界的 Lock

終於要回歸正題了。在這種情況下，的確是 Lock 派上用場的時候。 我們只需要在 SELECT 的後面加上 LOCK IN SHARE MODE，搜尋到的資料都會加上 Shared Lock ，讓這些資料只能讀而不能改。現在讓我們重現剛剛的例子，這次使用 ID 為 5, 6, 7 的票進行：
# session 1
mysql> INSERT INTO tickets (train_no, car, seat, price) VALUES (123, 9, '1A', 590),
    (123, 9, '1B', 590),
    (123, 9, '1C', 590);

mysql> SELECT * FROM tickets;
+----+----------+------+------+-------+
| id | train_no | car  | seat | price |
+----+----------+------+------+-------+
|  1 |      123 |    8 | 1A   |   590 |
|  2 |      123 |    8 | 1B   |   590 |
|  4 |      123 |    8 | 1D   |     0 |
|  5 |      123 |    9 | 1A   |   590 |
|  6 |      123 |    9 | 1B   |   590 |
|  7 |      123 |    9 | 1C   |   590 |
+----+----------+------+------+-------+
6 rows in set (0.00 sec)
mysql> BEGIN;
mysql> SELECT * FROM tickets WHERE id in (5, 6, 7) LOCK IN SHARE MODE;
+----+----------+------+------+-------+
| id | train_no | car  | seat | price |
+----+----------+------+------+-------+
|  5 |      123 |    9 | 1A   |   590 |
|  6 |      123 |    9 | 1B   |   590 |
|  7 |      123 |    9 | 1C   |   590 |
+----+----------+------+------+-------+
3 rows in set (0.00 sec)
此時，可以透過搜尋表 INFORMATION_SCHEMA.INNODB_TRX，來確認是否鎖上。
# session 1
# Example for Without Lock
-- mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX \G
-- Empty set (0.00 sec)
# Has Locks
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX \G
*************************** 1. row ***************************
                    trx_id: 421953311689320
                 trx_state: RUNNING
               trx_started: 2020-04-05 12:35:35
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 8
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 3
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)
trx_rows_locked 值為 3，指的就是我們剛剛搜尋的票：5、6、7。確認已經被 Lock 後，就來 Delete 一筆看看吧。
# session 2
mysql> DELETE FROM tickets WHERE id = 7;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
此時 Session 2 的 DELETE 開始等待 ID 為 7 的票被釋放，因為需要釋放後才能進行刪除。但因為 Session 1 還在進行中（尚未 COMMIT）所以等到 Timeout 時限後，Session 2 產生 ERROR 1205，告知連線端，取消這次的刪除動作。

由以上結果來看，Lock 成功守住 Session 1 中的 Transaction，我們不再需要擔心有白目的人會來破壞我們的遊戲規則。

Lock 使用時機

剛剛是一個實際需要使用 Lock 的例子。但如果沒有特別討論 Lock 這件事，其實很難意識到哪些地方可能會需要使用 Lock 。為了讓自己在設計相關邏輯時不會忽略掉 Lock，把握以下這點原則（個人見解）應該是一個方法：
  • 如果在做決定（INSERT、UPDATE、DELELE）的過程中，不希望參考的資料（SELECT、WHERE）有異動的可能，則需要使用 Lock。

Shared Lock？Exclusive Lock？怎麼選？

在本篇所提的例子，使用的是 Shared Lock 來解決這個問題。原因是目前的情境下，在進行確認票是否存在時，使用者應該還是能搜尋到自己的票，所以使用 Shared Lock。但 Shared Lock 本身最麻煩的地方就在於可以共用 Lock。在共用的情況下，雖然讓其他的 Session 也能讀取被 Lock 的資料，但反而會讓鎖不易被釋放。例如：
  • 資料 A (Locked by Session 1)：Session 1 使用 Shared Lock 讀取資料 A。
  • 資料 A (Locked by Session 1 and 2)：Session 2 使用 Shared Lock讀取資料 A。
  • 資料 A (Locked by Session 1 and 2)：此時， Session 1 and 2 都不能修改資料 A。
Shared Lock 那麼麻煩，那麼買票的例子為什麼要使用 Shared Lock 呢？那是因為：
  • 預期票會被修改的情況，只有票的擁有者會進行。（預期內：只會影響到票的擁有者）
在這種情況下，我們本來就是要預防擁有者修改，正巧完全符合使用的情境。所以我們可以進一步歸納 Shared Lock 可以在以下情境使用：
  • 資料本身仍需被查閱，且所有會共用 Shared Lock 的情況，都需要在預期之內。
那什麼情況使用 Exclusive Lock 呢？基本上的原則是（個人見解）：
  • 資料沒有必要在 Lock 時，被查閱。
  • 有大量共用 Shared Lock 的情況（資料可能被咬住），或有太多不確定的使用情境。
大概就是跟 Shared Lock 相反的使用時機。

結論

需要 Lock 的地方總是讓人意想不到，且使用時機主要是取決於服務本身的需求，這也讓人更難決定要是否需要使用 Lock，以及該使用什麼 Lock 是好。另外，Lock 可以幫助服務邏輯不被破壞沒錯，但最有幫助的還是完整的程式邏輯，來避免不預期的使用流程。希望以上的個人見解，對讀者有參考價值。
標籤：

熱門文章