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

歡迎大家前往騰訊雲+社群，擷取更多騰訊海量技術實踐幹貨哦~

本文由騰訊資料庫技術 發表于雲+社群專欄

提示：公衆号展示代碼會自動折行，建議橫屏閱讀。

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

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

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

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。當結果集不為空時，傳回的席位即被鎖定成功。

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

如果傳回錯誤，如下：

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

SKIP LOCKED還可以很友善的用來進行随機配置設定席位。例如我們隻需要鎖定兩個空的席位就可以通過如下語句實作。

SKIP LOCKED/NOWAIT功能隻針對行鎖(record lock)，不包括表鎖(table lock)，中繼資料鎖(metadata lock/MDL)。是以，帶有SKIP LOCKED/NOWAIT的查詢語句依然可能會因為表鎖或中繼資料庫鎖而阻塞。中繼資料鎖是MySQL Server層用來保護資料庫對象的并發通路的一緻性而建立的，資料庫對象不僅包括表，同時包括庫，函數，存儲過程，觸發器，事件等等。表和行鎖是InnoDB存儲引擎内部為了保證事務的一緻性而建立的不同粒度的鎖。

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

在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

騰訊資料庫技術團隊對内支援微信紅包，彩票、資料銀行等集團内部業務，對外為騰訊雲提供各種資料庫産品，如CDB、CTSDB、CKV、CMongo， 騰訊資料庫技術團隊專注于增強資料庫核心功能，提升資料庫性能，保證系統穩定性并解決使用者在生産過程中遇到的問題，并對生産環境中遇到的問題及知識進行分享。

更新MySQL主鍵 Linux排程原理介紹和應用（前篇） 如何備份你的MySQL資料庫 MySQL 8.0 版本功能變更介紹

此文已由作者授權騰訊雲+社群釋出，原文連結：https://cloud.tencent.com/developer/article/1163316?fromSource=waitui

歡迎大家前往騰訊雲+社群或關注雲加社群微信公衆号（QcloudCommunity），第一時間擷取更多海量技術實踐幹貨哦~

海量技術實踐經驗，盡在雲加社群！

https://cloud.tencent.com/developer