国产无码综合区,色欲AV无码国产永久播放,无码天堂亚洲国产AV,国产日韩欧美女同一区二区

MySQL InnoDB加鎖規(guī)則分析

2年前作者：廢物大師兄分類：Toy博客閱讀(11)違法舉報(bào)

這篇具有很好參考價值的文章主要介紹了MySQL InnoDB加鎖規(guī)則分析。希望對大家有所幫助。如果存在錯誤或未考慮完全的地方，請大家不吝賜教，您也可以點(diǎn)擊"舉報(bào)違法"按鈕提交疑問。

1.? 基礎(chǔ)知識回顧

MySQL InnoDB加鎖規(guī)則分析

1、索引的有序性，索引本身就是有序的

2、InnoDB中間隙鎖的唯一目的是防止其他事務(wù)插入間隙。間隙鎖可以共存。一個事務(wù)取得的間隙鎖并不會阻止另一個事務(wù)取得同一間隙上的間隙鎖。共享和獨(dú)占間隔鎖之間沒有區(qū)別。它們彼此之間不沖突，并且執(zhí)行相同的功能。

3、MySQL默認(rèn)隔離級別是 REPEATABLE-READ

4、加鎖的對象是索引，加鎖的基本單位是next-key鎖，而行鎖和間隙鎖，是由next-key鎖退化而來的

5、記錄鎖，鎖的是索引，而非數(shù)據(jù)本身

6、間隙鎖是開區(qū)間，next-key鎖是前開后閉區(qū)間

7、意向鎖是表級鎖，它相當(dāng)于一個標(biāo)志，可以用來提高加鎖的效率

8、間隙鎖的目的是為了防止幻讀，在“讀已提交”隔離級別下允許幻讀，所以如果隔離級別是“讀已提交”，就不會用到間隙鎖，更不會用到next-key鎖。因此，只有“可重復(fù)讀”及以上隔離級別下，才會有next-key鎖

9、InnoDB中鎖住的是索引。對輔助索引加鎖時，輔助索引所對應(yīng)的主鍵索引也會被鎖住。

10、所謂“間隙”本質(zhì)是又間隙右邊的那條記錄決定的

?

接下來，具體看一下走不同的索引時的加鎖情況。本例中使用的MySQL版本為8.0.30

SELECT VERSION();
SHOW VARIABLES LIKE 'transaction_isolation';
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

MySQL InnoDB加鎖規(guī)則分析

MySQL InnoDB加鎖規(guī)則分析

測試表結(jié)構(gòu)及數(shù)據(jù)如下：

MySQL InnoDB加鎖規(guī)則分析

MySQL InnoDB加鎖規(guī)則分析

2.? 案例分析

LOCK_MODE不同值的含義：

X?：代表next-key鎖
X,GAP ：代表間隙鎖
X,REC_NOT_GAP ：代表記錄鎖

2.1.? 主鍵索引

情況一：等值查詢，存在

Session A

Session B

BEGIN;
SELECT * FROM t_user WHERE id = 10 FOR UPDATE;

?

?

INSERT INTO t_user (id, `name`, id_card_no, birthday, score) VALUES (9, '于禁', '1012', '2023-11-01', 1);

Affected rows: 1

MySQL InnoDB加鎖規(guī)則分析

首先對表加意向排它鎖，然后對主鍵加記錄鎖，可以看到只鎖住了id=10這個主鍵索引

情況二：等值查詢，不存在

Session A	Session B	Session C
BEGIN; SELECT * FROM t_user WHERE id = 5 FOR UPDATE;	?	?
?	INSERT INTO t_user (id, `name`, id_card_no, birthday, score) VALUES (6, '于禁', '1012', '2023-11-01', 1); 1205 - Lock wait timeout exceeded; try restarting transaction	?
?	?	UPDATE t_user SET score = score + 1 WHERE id = 10; Affected rows: 1

MySQL InnoDB加鎖規(guī)則分析

加鎖范圍： (-∞, 10)

注意，是開區(qū)間，10并沒有被鎖

情況三：范圍查找

Session A	Session B	Session C
BEGIN; SELECT * FROM t_user WHERE id >= 10 AND id < 11 FOR UPDATE;	?	?
?	INSERT INTO t_user (id,`name`,id_card_no,birthday,score) VALUES (9,'典韋','1011','2022-12-19',1) Affected rows: 1	?
?	?	INSERT INTO t_user (id,`name`,id_card_no,birthday,score) VALUES (18,'徐晃','1018','2022-12-09',1); 1205 - Lock wait timeout exceeded; try restarting transaction

MySQL InnoDB加鎖規(guī)則分析

一個記錄鎖10，加一個間隙鎖(10, 20)，合起來就是[10, 20)

鎖定區(qū)間：[10, 20)

Session A	Session B	Session C
BEGIN; SELECT * FROM t_user WHERE id >= 10 AND id <= 20 FOR UPDATE;	?	?
?	UPDATE t_user SET score = score + 1 WHERE id = 20; 1205 - Lock wait timeout exceeded; try restarting transaction	?
?	?	INSERT INTO t_user (id,`name`,id_card_no,birthday,score) VALUES (21,'張遼','1021','2022-12-09',1); Affected rows: 1

id=10上加了記錄鎖，id=20上加了next-key鎖

next-key鎖是前開后閉區(qū)間，所以，最終鎖定區(qū)間為：[10,20]

如果這里不是id>=10，而是id>10的話，最終只會在id=20上加next-key鎖，這種情況下鎖定區(qū)間為：(10,20]

2.2.? 唯一索引（非主鍵）

情況一：等值查詢，存在

Session A

Session B

BEGIN;
SELECT * FROM t_user WHERE id_card_no = '1003' FOR UPDATE;

?

?

UPDATE t_user SET score = score + 1 WHERE id = 30;

1205 - Lock wait timeout exceeded; try restarting transaction

MySQL InnoDB加鎖規(guī)則分析

輔助索引 ('1003',30)加記錄鎖，同時，主鍵索引上id=30加記錄鎖

情況二：等值查詢，不存在

先看一眼現(xiàn)在的數(shù)據(jù)

MySQL InnoDB加鎖規(guī)則分析

Session A	Session B	Session C
BEGIN; SELECT * FROM t_user WHERE id_card_no = '1042' FOR UPDATE;	?	?
?	INSERT INTO t_user (id,`name`,id_card_no,birthday,score) VALUES (52,'許褚','1041','2023-01-01',1); 1205 - Lock wait timeout exceeded; try restarting transaction	?
?	?	UPDATE t_user SET score = score + 1 WHERE id_card_no = '1041'; Affected rows: 0

MySQL InnoDB加鎖規(guī)則分析

只在輔助索引idx_card上加了間隙鎖，鎖定范圍是：('1040', '1050')

索引是有序的，盡管索引字段類型是字符串類型，仍然是有序的

因?yàn)槭情g隙鎖，所以沒有鎖定1050，也就自然不會給id=50加記錄鎖

值得注意的是，在('1040', '1050')這個區(qū)間內(nèi)插入是不行的，但是更新是可以的

情況三：范圍查找

Session A	Session B	Session C
BEGIN; SELECT * FROM t_user WHERE id_card_no <= '1024' FOR UPDATE;	?	?
?	INSERT INTO t_user (id,`name`,id_card_no,birthday,score) VALUES (11,'潘鳳','1011','2023-01-01',1); 1205 - Lock wait timeout exceeded; try restarting transaction	?
?	?	INSERT INTO t_user (id,`name`,id_card_no,birthday,score) VALUES (11,'潘鳳','1031','2023-01-01',1); Affected rows: 1

MySQL InnoDB加鎖規(guī)則分析

主鍵索引上id=10和id=20都加了記錄鎖

輔助索引idx_card上加了Next-key鎖，鎖定范圍為：(-∞, '1010']、('1010', '1020']、('1020', '1030']

2.3.? 非唯一索引（普通索引）

情況一：等值查詢，存在

Session A	Session B	Session C
BEGIN; SELECT * FROM t_user WHERE birthday = '2023-12-01' FOR UPDATE;	?	?
?	UPDATE t_user SET score = score + 1 WHERE birthday = '2023-12-11'; Affected rows: 0	?
?	?	UPDATE t_user SET score = score + 1 WHERE birthday = '2023-12-09'; Affected rows: 0

MySQL InnoDB加鎖規(guī)則分析

主鍵索引id=10加記錄鎖

輔助索引idx_birthday上，'2023-12-01'上加Next-key鎖，'2023-12-12'上加間隙鎖

加鎖區(qū)間：(-∞, 2023-12-01]、(2023-12-01, 2023-12-12)、id=10

因?yàn)槭欠俏ㄒ凰饕?，所以?dāng)找到第一條birthday = '2023-12-01'的記錄時，不確定后面還有沒有這樣的記錄，所以必須繼續(xù)往后找，直到遇到一條不是2023-12-01的記錄未止。

間隙鎖阻止其它事務(wù)插入，但是不阻止更新

情況二：范圍查找

Session A	Session B	Session C
BEGIN; SELECT * FROM t_user WHERE birthday >= '2023-11-11' AND birthday <='2023-11-28' FOR UPDATE;	?	?
?	UPDATE t_user SET score = score + 1 WHERE birthday = '2023-11-29'; Affected rows: 0	?
?	?	INSERT INTO t_user (id,`name`,id_card_no,birthday,score) VALUES (13,'華雄','1033','2023-11-29',1); 1205 - Lock wait timeout exceeded; try restarting transaction

MySQL InnoDB加鎖規(guī)則分析

主鍵索引上加鎖范圍：id=30和id=40

輔助索引idx_birthday上加鎖范圍：(2023-01-01, 2023-11-12]、(2023-11-12, 2023-11-28]、(2023-11-28, 2023-11-30]

2.4.? 不走索引

Session A	Session B	Session C
BEGIN; SELECT * FROM t_user WHERE score = 2 FOR UPDATE;	?	?
?	UPDATE t_user SET score = score + 1 WHERE id = 33; Affected rows: 0	?
?	?	INSERT INTO t_user (id,`name`,id_card_no,birthday,score) VALUES (33,'顏良','1038','2023-12-20',1); Lock wait timeout exceeded; try restarting transaction

MySQL InnoDB加鎖規(guī)則分析

在所有記錄的主鍵上加next-key鎖

加鎖范圍：(-∞, 10]、(10, 20]、(20, 30]、(30, 40]、(40, 50]、(50, +∞)

3.? 總結(jié)

1、主鍵索引

等值查詢，命中，則被命中的主鍵索引加記錄鎖
等值查詢，未命中，則繼續(xù)向后（向右）查找，直到找到第一個不滿足的記錄，對該記錄加間隙鎖，即鎖住該記錄之前的間隙，以防止其它事務(wù)向其中插入數(shù)據(jù)
范圍查找，找到的（滿足條件的）記錄的主鍵加記錄鎖，掃描過的區(qū)間加間隙鎖

2、非主鍵唯一索引

與主鍵索引類似，唯一的區(qū)別是鎖住輔助索引記錄的同時會鎖住對應(yīng)的主鍵索引

3、非唯一索引

向右查找直到遇到一條不滿足條件的記錄，然后對掃描到的區(qū)間加間隙鎖，對掃描到的輔助索引記錄加記錄鎖，同時對與其對應(yīng)的主鍵加記錄鎖

4、不走索引

表中所有記錄的主鍵加next-key鎖

?

總結(jié)幾個規(guī)律：

命中的索引記錄會加記錄鎖，如果它是一個輔助索引，則對應(yīng)的主鍵索引也會被加上記錄鎖
沒有命中的記錄不會被加記錄鎖
非唯一索引上查找時，當(dāng)找到第一條滿足條件的索引記錄時，還會繼續(xù)向右查找，直到遇到一條不滿足條件的記錄（PS：幸虧索引是有序的，不然找到累死）
當(dāng)一條SQL沒有走索引時，那么將會在每一條聚集索引上加X鎖，這個類似于表鎖，但原理上和表鎖是完全不同的

建議：

盡量控制事務(wù)大小，減少鎖定資源量和時間長度
即便在條件中使用了索引字段，但是否使用索引來檢索數(shù)據(jù)是由 MySQL 通過判斷不同執(zhí)行計(jì)劃的代價來決定的。如果 MySQL 認(rèn)為全表掃描效率更高，它就不會使用索引。因此，在分析鎖沖突時，可以查看執(zhí)行計(jì)劃（explain）以確認(rèn)是否真正使用了索引

最后，重要的事情說三遍：

加鎖的單位是next-key鎖
加鎖的單位是next-key鎖
加鎖的單位是next-key鎖

?

參考

https://www.cnblogs.com/harda/p/16820592.html

https://blog.csdn.net/qq_42604176/article/details/115431744

https://zhuanlan.zhihu.com/p/378306056

https://cloud.tencent.com/developer/article/1971381

https://cloud.tencent.com/developer/article/1844928文章來源地址http://www.zghlxwxcb.cn/news/detail-760306.html

到了這里，關(guān)于MySQL InnoDB加鎖規(guī)則分析的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！

本文來自互聯(lián)網(wǎng)用戶投稿，該文觀點(diǎn)僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務(wù)，不擁有所有權(quán)，不承擔(dān)相關(guān)法律責(zé)任。如若轉(zhuǎn)載，請注明出處：如若內(nèi)容造成侵權(quán)/違法違規(guī)/事實(shí)不符，請點(diǎn)擊違法舉報(bào)進(jìn)行投訴反饋，一經(jīng)查實(shí)，立即刪除！

分享到：

領(lǐng)支付寶紅包贊助服務(wù)器費(fèi)用

MySQL基礎(chǔ)（三十二）事務(wù)基礎(chǔ)知識
1.1 存儲引擎支持情況 SHOW ENGINES 命令來查看當(dāng)前 MySQL 支持的存儲引擎都有哪些，以及這些存儲引擎是否支持事務(wù)。能看出在 MySQL 中，只有InnoDB 是支持事務(wù)的。 1.2 基本概念事務(wù) ：一組邏輯操作單元，使數(shù)據(jù)從一種狀態(tài)變換到另一種狀態(tài)。事務(wù)處理的原則：保證所有事務(wù)都
2024年02月05日
瀏覽(21)
MySQL的基礎(chǔ)知識
數(shù)據(jù)：描述事物符號記錄。（包括數(shù)字、文字、圖形、圖像、聲音、檔案記錄等）以記錄形式統(tǒng)一的格式進(jìn)行存儲。程序的概念程序=指令+數(shù)據(jù) 狹義的數(shù)據(jù)和廣義數(shù)據(jù) 狹義的數(shù)據(jù)（文字和符號記錄）?? 結(jié)構(gòu)化數(shù)據(jù):文字、數(shù)字、符號記錄被稱為結(jié)構(gòu)化數(shù)據(jù)，有一定的結(jié)構(gòu)，
2024年04月24日
瀏覽(18)
【MySQL】基礎(chǔ)知識（二）
銜接上文 MySQL基礎(chǔ)知識(一)，我們已經(jīng)了解以下相關(guān)知識。一個數(shù)據(jù)庫服務(wù)器上，可以把很多有業(yè)務(wù)聯(lián)系上的表放到一起，構(gòu)成了一個邏輯上的“數(shù)據(jù)集合”，稱為數(shù)據(jù)庫。數(shù)據(jù)庫操作: 創(chuàng)建數(shù)據(jù)庫查看數(shù)據(jù)庫選中指定的數(shù)據(jù)庫刪除數(shù)據(jù)庫那么下面我們繼續(xù)來聊，MySQL的基
2024年02月10日
瀏覽(18)
MySQL 基礎(chǔ)知識（十）之 MySQL 架構(gòu)
目錄 1 MySQL 架構(gòu)說明 2 連接層 3 核心業(yè)務(wù)層 3.1 查詢緩存 3.2 解析器 3.3 優(yōu)化器 3.4 執(zhí)行器 4 存儲引擎層 5 參考文檔 1 MySQL 架構(gòu)說明下圖是 MySQL 5.7?及其之前版本的邏輯架構(gòu)示意圖 MySQL 架構(gòu)大致可分為以下三層：連接層：負(fù)責(zé)跟客戶端建立連接、獲取權(quán)限、維持和管理連接核
2024年02月20日
瀏覽(18)
MySQL 事務(wù)的基礎(chǔ)知識
事務(wù)是數(shù)據(jù)庫區(qū)別于文件系統(tǒng)的重要特性之一，當(dāng)我們有了事務(wù)就會讓數(shù)據(jù)庫中的數(shù)據(jù)始終保持一致性，同時我們還能通過事務(wù)的機(jī)制恢復(fù)到某個時間地點(diǎn)的數(shù)據(jù) ，這樣可以保證已提交到數(shù)據(jù)庫的修改不會因?yàn)橄到y(tǒng)崩潰而丟失。查詢當(dāng)前 MySQL 支持的存儲引擎 Engine Support
2024年02月04日
瀏覽(16)
MySQL數(shù)據(jù)庫基礎(chǔ)知識，基礎(chǔ)操作詳細(xì)整理
引入：淘寶網(wǎng)，京東、微信，抖音等都有各自的功能,那么當(dāng)我們退出系統(tǒng)的時候，下次再訪問時，為什么信息還存在？數(shù)據(jù)庫（Database）是按照數(shù)據(jù)結(jié)構(gòu)來組織、存儲和管理數(shù)據(jù)的倉庫。每個數(shù)據(jù)庫都有一個或多個不同的 API 用于創(chuàng)建，訪問，管理，搜索和復(fù)制所保存的數(shù)
2024年04月17日
瀏覽(29)
靜態(tài)時序分析第二章基礎(chǔ)知識
目錄 1. 邏輯門單元 2. 門單元的時序計(jì)算參數(shù) ??????? 2.1 信號轉(zhuǎn)換延時(transition delay) ???????? 2.2 邏輯門延時(logic gate delay) 3.? 時序單元相關(guān)約束 ??????? 3.1 建立時間(setup time) ??????? 3.2 保持時間(hold time) ??????? 3.3 恢復(fù)時間 ??????? 3.4 移除時間 ?????
2023年04月13日
瀏覽(28)
MySQL 基礎(chǔ)知識（九）之視圖
目錄 1 視圖的介紹 2 視圖算法 3 創(chuàng)建視圖 4 查看視圖結(jié)構(gòu) 5 修改視圖 6 刪除視圖 7 參考文檔 1 視圖的介紹 ?????????視圖是一張并不存儲數(shù)據(jù)的虛擬表，其本質(zhì)是根據(jù) SQL 語句動態(tài)查詢數(shù)據(jù)庫中的數(shù)據(jù)。數(shù)據(jù)庫中只存放了視圖的定義，通過 SQL 語句使用視圖時，會根據(jù)視圖
2024年02月19日
瀏覽(16)
【MySQL】數(shù)據(jù)庫MySQL基礎(chǔ)知識與操作
作者主頁：paper jie_博客本文作者：大家好，我是paper jie，感謝你閱讀本文，歡迎一建三連哦。本文錄入于《MySQL》專欄，本專欄是針對于大學(xué)生，編程小白精心打造的。筆者用重金(時間和精力)打造，將MySQL基礎(chǔ)知識一網(wǎng)打盡，希望可以幫到讀者們哦。其他專欄：《算法詳解
2024年02月05日
瀏覽(20)
【數(shù)據(jù)分析之道-基礎(chǔ)知識(二)】列表
? 作者簡介： i阿極，CSDN Python領(lǐng)域新星創(chuàng)作者，專注于分享python領(lǐng)域知識。 ? 本文錄入于《數(shù)據(jù)分析之道》，本專欄針對大學(xué)生、初級數(shù)據(jù)分析工程師精心打造，對python基礎(chǔ)知識點(diǎn)逐一擊破，不斷學(xué)習(xí)，提升自我。 ? 訂閱后，可以閱讀《數(shù)據(jù)分析之道》中全部文章內(nèi)容
2023年04月08日
瀏覽(68)

<u id="ognk4"></u>