如何在資料庫中高效實現訂座功能？

提示：公眾號展示程式碼會自動折行，建議橫屏閱讀。

第一部分：SKIP LOCKED/NOWAIT訂座功能實現

訂座在現實生活中是一種很常見的場景，比較常見的有火車票席位選擇，電影院席位選擇等等。那麼如何實現訂座功能呢？應用程式可能有很多種不同的實現方式，當然，肯定離不開資料庫。這裡將介紹一種純資料庫的實現方式。

設想我們有一張座位表如下：

CREATE TABLE seats (
  seat_no INT PRIMARY KEY,
  booked ENUM('YES', 'NO') DEFAULT 'NO') ENGINE=InnoDB;

表中有100個席位，從0到99。例如我們要預定席位2，3，我們可以先開啟事務，鎖定席位：

START TRANSACTION;SELECT * FROM seats WHERE seat_no IN (2,3) AND booked = 'NO' FOR UPDATE;

SELECT… FOR UPDATE語句返回結果有如下三種情況：

• 1.返回成功，並且結果集包含2和3，那麼說明鎖定成功。我們可以之行下一步操作，等待支付完成，並更新席位狀態並提交事務，訂座完成。 UPDATE seats SET booked = 'YES' WHERE seat_no IN (2,3) COMMIT;
• 2.返回成功，但結果集為空，或者只包含2或者3，那麼說明鎖定失敗。
• 3.很長時間不返回直到返回超時。比如席位2或者3已經被另一事務鎖定，並且在等待支付完成或者發生其他情況，導致該事務一直未提交(commit)或者回滾(rollback)。返回超時預設需要等待50秒，我們可以通過修改innodb_lock_wait_timeout引數來配置合理的等待時間。超時之後返回的錯誤如下： ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

情況3對使用者來說，意味著卡死，完全不能接受。為什麼會發生等待？在InnoDB的鎖系統(lock system)中，席位2如果被一個事務上了X（寫鎖）鎖或者IX鎖(意向更新鎖)，那麼下一個事務要對席位2上X鎖或者IX鎖的事務，就要等待。這是由事務本身的特性(ACID)決定的。

那麼是否有一種方法避免等待以及後續可能發生的超時呢？MySQL 8.0 提供的新功能SKIP LOCKED/NOWAIT就可以。 SKIP LOCKED的意思是跳過那些已經被其他事務鎖定了的席位。使用如下SKIP LOCKED語句進行席位鎖定，那麼返回的結果集可能為空，2或3，2和3。當結果集不為空時，返回的席位即被鎖定成功。

SELECT * FROM seats WHERE seat_no IN (2,3) AND booked = 'NO'FOR UPDATE SKIP LOCKED;

NOWAIT的意思是如果碰到被其他事務鎖定的席位，不等待並直接返回錯誤。使用如下NOWAIT語句進行席位鎖定，那麼返回結果集2和3，要麼返回錯誤。

SELECT * FROM seats WHERE seat_no IN (2,3) AND booked = 'NO'FOR UPDATE NOWAIT;

如果返回錯誤，如下：

ERROR 3572 (HY000): Do not wait for lock.

如果成功鎖定兩個席位，通過如下語句查詢鎖系統的狀態：

SELECT thread_id, object_name, lock_type, lock_mode, lock_data, lock_status FROM performance_schema.data_locks;+-----------+-------------+-----------+-----------+-----------+-------------+| thread_id | object_name | lock_type | lock_mode | lock_data | lock_status |
+-----------+-------------+-----------+-----------+-----------+-------------+|        43 | seats       | TABLE     | IX        | NULL      | GRANTED     |
|        43 | seats       | RECORD    | X         | 2         | WAITING     |
|        42 | seats       | TABLE     | IX        | NULL      | GRANTED     |
|        42 | seats       | RECORD    | X         | 2         | GRANTED     |
|        42 | seats       | RECORD    | X         | 3         | GRANTED     |
+-----------+-------------+-----------+-----------+-----------+-------------+

SKIP LOCKED還可以很方便的用來進行隨機分配席位。例如我們只需要鎖定兩個空的席位就可以通過如下語句實現。

SELECT * FROM seats WHERE booked = 'NO' LIMIT 2 FOR UPDATE SKIP LOCKED;

SKIP LOCKED/NOWAIT功能只針對行鎖(record lock)，不包括表鎖(table lock)，後設資料鎖(metadata lock/MDL)。因此，帶有SKIP LOCKED/NOWAIT的查詢語句依然可能會因為表鎖或後設資料庫鎖而阻塞。後設資料鎖是MySQL Server層用來保護資料庫物件的併發訪問的一致性而建立的，資料庫物件不僅包括表，同時包括庫，函式，儲存過程，觸發器，事件等等。表和行鎖是InnoDB儲存引擎內部為了保證事務的一致性而建立的不同粒度的鎖。

另外，SKIP LOCKED/NOWAIT還可以配合FOR SHARE使用，並且可以與單表繫結。例如：

SELECT seat_noFROM seats JOIN seat_rows USING ( row_no )WHERE seat_no IN (2,3) AND seat_rows.row_no IN (12)AND booked = 'NO'FOR UPDATE OF seats SKIP LOCKEDFOR SHARE OF seat_rows NOWAIT;

第二部分：SKIP LOCKED/NOWAIT在InnoDB中的程式碼實現

在InnoDB中，實現SKIP LOCKED/NOWAIT具體實現如下：

• 1.增加新的查詢模式 enum select_mode { SELECT_ORDINARY = 0, /* default behaviour / SELECT_SKIP_LOCKED, / skip the row if row is locked / SELECT_NO_WAIT / return immediately if row is locked */ };
• 2.在查詢開始前，設定查詢模式 ha_innobase::store_lock(): /* Set select mode for SKIP LOCKED / NO_WAIT */ switch (lock_type) { case TL_READ_SHARED_SKIP_LOCKED: case TL_WRITE_SKIP_LOCKED: m_prebuilt->select_mode = SELECT_SKIP_LOCKED; break; case TL_READ_SHARED_NO_WAIT: case TL_WRITE_NO_WAIT: m_prebuilt->select_mode = SELECT_NO_WAIT; break; default: m_prebuilt->select_mode = SELECT_ORDINARY; break; }
• 3.上鎖函式中，如果記錄已被鎖定，針對對不同查詢模式進行相應處理： lock_rec_lock_slow(): if (wait_for != NULL) { switch (sel_mode) { case SELECT_SKIP_LOCKED: err = DB_SKIP_LOCKED; break; case SELECT_NO_WAIT: err = DB_LOCK_NOWAIT; break;
• 4.查詢中對上鎖結果進行處理： row_search_mvcc(): case DB_SKIP_LOCKED: goto next_rec; 對DB_LOCK_NOWAIT的處理則是回滾當前語句（statement），見函式row_mysql_handle_errors（）。
• 5.二級索引(secondary index)的處理 在InnoDB中，對錶中記錄的鎖定分兩種情況。第一種是查詢使用是聚集索引（cluster index），那麼直接對聚集索引的記錄上鎖；第二中是查詢使用的是二級索引，那麼首先對二級索引的記錄上鎖，然後根據二級索引的記錄，找到對應的聚集索引記錄進行上鎖。 所以，對於第一部分訂座的席位表中，如果存在二級索引，對於鎖定表中一條記錄而言，最終鎖定成功與否，還是以鎖定聚集索引記錄為準。

SKIP LOCKED/NOWAIT可以非常高效地實現訂座這個場景，作為InnoDB部分（WL#8919: InnoDB: Implement NOWAIT and SKIP LOCKED）的原作者，我也期待著大家來分享該功能更多的使用場景。

參考連結：

• 1.MySQL 8.0.1: Using SKIP LOCKED and NOWAIT to handle hot rows
• 2.WL#3597: Implement NOWAIT and SKIP LOCKED
• 3.WL#8919: InnoDB: Implement NOWAIT and SKIP LOCKED
• 4.WL#6657: PERFORMANCE_SCHEMA, DATA LOCKS

---