在數(shù)據(jù)庫(kù)中如何高效的實(shí)現(xiàn)訂座功能

前言

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

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

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

設(shè)想我們有一張座位表如下：

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

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

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

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

1.返回成功，并且結(jié)果集包含2和3，那么說明鎖定成功。我們可以之行下一步操作，等待支付完成，并更新席位狀態(tài)并提交事務(wù)，訂座完成。 UPDATE seats SET booked = 'YES' WHERE seat_no IN (2,3) COMMIT;

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

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

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

那么是否有一種方法避免等待以及后續(xù)可能發(fā)生的超時(shí)呢？MySQL 8.0 提供的新功能SKIP LOCKED/NOWAIT就可以。 SKIP LOCKED的意思是跳過那些已經(jīng)被其他事務(wù)鎖定了的席位。使用如下SKIP LOCKED語句進(jìn)行席位鎖定，那么返回的結(jié)果集可能為空，2或3，2和3。當(dāng)結(jié)果集不為空時(shí)，返回的席位即被鎖定成功。

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

NOWAIT的意思是如果碰到被其他事務(wù)鎖定的席位，不等待并直接返回錯(cuò)誤。使用如下NOWAIT語句進(jìn)行席位鎖定，那么返回結(jié)果集2和3，要么返回錯(cuò)誤。

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

如果返回錯(cuò)誤，如下：

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

如果成功鎖定兩個(gè)席位，通過如下語句查詢鎖系統(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還可以很方便的用來進(jìn)行隨機(jī)分配席位。例如我們只需要鎖定兩個(gè)空的席位就可以通過如下語句實(shí)現(xiàn)。

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

SKIP LOCKED/NOWAIT功能只針對(duì)行鎖(record lock)，不包括表鎖(table lock)，元數(shù)據(jù)鎖(metadata lock/MDL)。因此，帶有SKIP LOCKED/NOWAIT的查詢語句依然可能會(huì)因?yàn)楸礞i或元數(shù)據(jù)庫(kù)鎖而阻塞。元數(shù)據(jù)鎖是MySQL Server層用來保護(hù)數(shù)據(jù)庫(kù)對(duì)象的并發(fā)訪問的一致性而創(chuàng)建的，數(shù)據(jù)庫(kù)對(duì)象不僅包括表，同時(shí)包括庫(kù)，函數(shù)，存儲(chǔ)過程，觸發(fā)器，事件等等。表和行鎖是InnoDB存儲(chǔ)引擎內(nèi)部為了保證事務(wù)的一致性而創(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中的代碼實(shí)現(xiàn)

在InnoDB中，實(shí)現(xiàn)SKIP LOCKED/NOWAIT具體實(shí)現(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.在查詢開始前，設(shè)置查詢模式 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ù)中，如果記錄已被鎖定，針對(duì)對(duì)不同查詢模式進(jìn)行相應(yīng)處理： 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.查詢中對(duì)上鎖結(jié)果進(jìn)行處理： row_search_mvcc(): case DB_SKIP_LOCKED: goto next_rec; 對(duì)DB_LOCK_NOWAIT的處理則是回滾當(dāng)前語句（statement），見函數(shù)row_mysql_handle_errors（）。

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

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

參考鏈接：

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

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問大家可以留言交流，謝謝大家對(duì)腳本之家的支持。