前言

本文主要給大家介紹了關于在數(shù)據(jù)庫中如何高效的實現(xiàn)訂座功能的相關內容，分享出來供大家參考學習，下面話不多說了，來一起看看詳細的介紹吧

第一部分：SKIP LOCKED/NOWAIT訂座功能實現(xiàn)

訂座在現(xiàn)實生活中是一種很常見的場景，比較常見的有火車票席位選擇，電影院席位選擇等等。那么如何實現(xiàn)訂座功能呢？應用程序可能有很多種不同的實現(xiàn)方式，當然，肯定離不開數(shù)據(jù)庫。這里將介紹一種純數(shù)據(jù)庫的實現(xiàn)方式。

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

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，那么說明鎖定成功。我們可以之行下一步操作，等待支付完成，并更新席位狀態(tài)并提交事務，訂座完成。 UPDATE seats SET booked = 'YES' WHERE seat_no IN (2,3) COMMIT;

2.返回成功，但結果集為空，或者只包含2或者3，那么說明鎖定失敗。

3.很長時間不返回直到返回超時。比如席位2或者3已經被另一事務鎖定，并且在等待支付完成或者發(fā)生其他情況，導致該事務一直未提交(commit)或者回滾(rollback)。返回超時默認需要等待50秒，我們可以通過修改innodb_lock_wait_timeout參數(shù)來配置合理的等待時間。超時之后返回的錯誤如下： ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

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

那么是否有一種方法避免等待以及后續(xù)可能發(fā)生的超時呢？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.

如果成功鎖定兩個席位，通過如下語句查詢鎖系統(tǒng)的狀態(tài)：

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還可以很方便的用來進行隨機分配席位。例如我們只需要鎖定兩個空的席位就可以通過如下語句實現(xiàn)。

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

SKIP LOCKED/NOWAIT功能只針對行鎖(record lock)，不包括表鎖(table lock)，元數(shù)據(jù)鎖(metadata lock/MDL)。因此，帶有SKIP LOCKED/NOWAIT的查詢語句依然可能會因為表鎖或元數(shù)據(jù)庫鎖而阻塞。元數(shù)據(jù)鎖是MySQL Server層用來保護數(shù)據(jù)庫對象的并發(fā)訪問的一致性而創(chuàng)建的，數(shù)據(jù)庫對象不僅包括表，同時包括庫，函數(shù)，存儲過程，觸發(fā)器，事件等等。表和行鎖是InnoDB存儲引擎內部為了保證事務的一致性而創(chuàng)建的不同粒度的鎖。

另外，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中的代碼實現(xiàn)

在InnoDB中，實現(xiàn)SKIP LOCKED/NOWAIT具體實現(xiàn)如下：

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.上鎖函數(shù)中，如果記錄已被鎖定，針對對不同查詢模式進行相應處理： 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），見函數(shù)row_mysql_handle_errors（）。

5.二級索引(secondary index)的處理 在InnoDB中，對表中記錄的鎖定分兩種情況。第一種是查詢使用是聚集索引（cluster index），那么直接對聚集索引的記錄上鎖；第二中是查詢使用的是二級索引，那么首先對二級索引的記錄上鎖，然后根據(jù)二級索引的記錄，找到對應的聚集索引記錄進行上鎖。 所以，對于第一部分訂座的席位表中，如果存在二級索引，對于鎖定表中一條記錄而言，最終鎖定成功與否，還是以鎖定聚集索引記錄為準。

SKIP LOCKED/NOWAIT可以非常高效地實現(xiàn)訂座這個場景，作為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

總結

以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。

最新評論