開(kāi)篇結(jié)論

加鎖的對(duì)象是索引，加鎖的基本單位是 next-key lock，它是由記錄鎖和間隙鎖組合而成的，next-key lock 是左開(kāi)右閉區(qū)間，而間隙鎖是左開(kāi)右開(kāi)區(qū)間。

在只使用記錄鎖或者間隙鎖就能避免幻讀現(xiàn)象的場(chǎng)景下， next-key lock 就會(huì)退化成記錄鎖或間隙鎖。

假設(shè)這個(gè)表，id 是主鍵索引（唯一索引），age 是普通索引（非唯一索引），name 是普通的列。數(shù)據(jù)如下：

唯一索引等值查詢

• 當(dāng)查詢的記錄是「存在」的，在索引樹(shù)上定位到這一條記錄后，將該記錄的索引中的 next-key lock 會(huì)退化成「記錄鎖」。
• 當(dāng)查詢的記錄是「不存在」的，在索引樹(shù)找到第一條大于該查詢記錄的記錄后，將該記錄的索引中的 next-key lock 會(huì)退化成「間隙鎖」。

記錄存在的情況

假設(shè)事務(wù) A 執(zhí)行了這條等值查詢語(yǔ)句，查詢的記錄是存在于表中的。

select * from user where id = 1 for update;

那么，事務(wù) A 會(huì)為 id 為 1 的這條記錄就會(huì)加上 X 型的記錄鎖。

接下來(lái)，如果有其他事務(wù)，對(duì) id 為 1 的記錄進(jìn)行更新或者刪除操作的話，這些操作都會(huì)被阻塞

為什么這里退化為了記錄鎖？
原因在于這里僅靠記錄鎖也能避免幻讀的問(wèn)題。

• 當(dāng)其他事務(wù)，對(duì) id 為 1 的記錄進(jìn)行更新或者刪除或插入id為1的數(shù)據(jù)的操作時(shí)，由于記錄鎖，這些操作都會(huì)被阻塞，也就不會(huì)出現(xiàn)前后兩次查詢的結(jié)果集不同，也就避免了幻讀的問(wèn)題。

記錄不存在的情況

假設(shè)事務(wù) A 執(zhí)行了這條等值查詢語(yǔ)句，查詢的記錄是不存在于表中的。

select * from user where id = 2 for update;

此時(shí)事務(wù) A 在 id = 5 記錄的主鍵索引上加的是間隙鎖，鎖住的范圍是 (1, 5)。

接下來(lái)，如果有其他事務(wù)插入 id 值為 2、3、4 這一些記錄的話，這些插入語(yǔ)句都會(huì)發(fā)生阻塞。

為什么這里退化為了間隙鎖？
原因在于這里僅靠間隙鎖也能避免幻讀的問(wèn)題。

• 如果這里加的是next-key lock，那就意味著其他事務(wù)無(wú)法更新/刪除 id = 5 這條記錄，但實(shí)際上即使更新/刪除 id = 5 這條記錄，也不會(huì)出現(xiàn)前后兩次查詢的結(jié)果集不同，查不到的還是查不到。

唯一索引范圍查詢

當(dāng)唯一索引進(jìn)行范圍查詢時(shí)，會(huì)對(duì)每一個(gè)掃描到的索引加 next-key 鎖，然后如果遇到下面這些情況，會(huì)退化成記錄鎖或者間隙鎖：

• 針對(duì)大于等于的范圍查詢，因?yàn)榇嬖诘戎挡樵兊臈l件，那么如果等值查詢的記錄是存在于表中，那么該記錄的索引中的 next-key 鎖會(huì)退化成記錄鎖。
• 針對(duì)小于或者小于等于的范圍查詢，要看條件值的記錄是否存在于表中：
  • 當(dāng)條件值的記錄不在表中，那么不管是小于還是小于等于條件的范圍查詢，掃描到終止范圍查詢的記錄時(shí)，該記錄的索引的 next-key 鎖會(huì)退化成間隙鎖，其他掃描到的記錄，都是在這些記錄的索引上加 next-key 鎖。
  • 當(dāng)條件值的記錄在表中，如果是小于條件的范圍查詢，掃描到終止范圍查詢的記錄時(shí)，該記錄的索引的 next-key 鎖會(huì)退化成間隙鎖，其他掃描到的記錄，都是在這些記錄的索引上加 next-key 鎖；如果小于等于條件的范圍查詢，掃描到終止范圍查詢的記錄時(shí)，該記錄的索引 next-key 鎖不會(huì)退化成間隙鎖。其他掃描到的記錄，都是在這些記錄的索引上加 next-key 鎖。

針對(duì) 大于或者大于等于 的范圍查詢

針對(duì) 大于 的范圍查詢的情況

假設(shè)事務(wù) A 執(zhí)行了這條范圍查詢語(yǔ)句：

select * from user where id > 15 for update;

此時(shí)，事務(wù) A 在主鍵索引上加了兩個(gè)next-key 鎖：

• 在 id = 20 這條記錄的主鍵索引上，加了范圍為 (15, 20] 的 next-key 鎖，意味著其他事務(wù)即無(wú)法更新或者刪除 id = 20 的記錄，同時(shí)無(wú)法插入 id 值為 16、17、18、19 的這一些新記錄。
• 在特殊記錄（ supremum pseudo-record）的主鍵索引上，加了范圍為 (20, +∞] 的 next-key 鎖，意味著其他事務(wù)無(wú)法插入 id 值大于 20 的這一些新記錄。

這里沒(méi)有next-key鎖都沒(méi)有退化。
原因在于需要保證 前后兩次查詢 id>15 的結(jié)果集相同。即需要保證 id>15不會(huì)出現(xiàn)新紀(jì)錄（間隙鎖），并且已存在的記錄不改變（記錄鎖）。

針對(duì) 大于等于 的范圍查詢的情況。

假設(shè)事務(wù) A 執(zhí)行了這條范圍查詢語(yǔ)句：

 select * from user where id >= 15 for update;

此時(shí)事務(wù) A 在主鍵索引上加了三個(gè) X 型 的鎖，分別是：

• 在 id = 15 這條記錄的主鍵索引上，加了記錄鎖，范圍是 id = 15 這一行記錄；意味著其他事務(wù)無(wú)法更新或者刪除 id = 15 的這一條記錄；
• 在 id = 20 這條記錄的主鍵索引上，加了 next-key 鎖，范圍是 (15, 20] 。意味著其他事務(wù)即無(wú)法更新或者刪除 id = 20 的記錄，同時(shí)無(wú)法插入 id 值為 16、17、18、19 的這一些新記錄。
• 在特殊記錄（ supremum pseudo-record）的主鍵索引上，加了 next-key 鎖，范圍是 (20, +∞] 。意味著其他事務(wù)無(wú)法插入 id 值大于 20 的這一些新記錄。

這里包含了一個(gè)等值查詢操作，退化為了記錄鎖。原因同 唯一索引等值查詢

針對(duì) 小于或者小于等于 的范圍查詢

針對(duì) 小于/小于等于 的范圍查詢時(shí)，查詢條件值的記錄 不存在 表中的情況

假設(shè)事務(wù) A 執(zhí)行了這條范圍查詢語(yǔ)句，注意查詢條件值的記錄（id 為 6）并不存在于表中。

select * from user where id < 6 for update;

此時(shí)，事務(wù) A 在主鍵索引上加了三個(gè) X 型的鎖：

• 在 id = 1 這條記錄的主鍵索引上，加了范圍為 (-∞, 1] 的 next-key 鎖，意味著其他事務(wù)無(wú)法更新或者刪除 id = 1 的這一條記錄，同時(shí)也無(wú)法插入 id 小于 1 的這一些新記錄。
• 在 id = 5 這條記錄的主鍵索引上，加了范圍為 (1, 5] 的 next-key 鎖，意味著其他事務(wù)無(wú)法更新或者刪除 id = 5 的這一條記錄，同時(shí)也無(wú)法插入 id 值為 2、3、4 的這一些新記錄。
• 在 id = 10 這條記錄的主鍵索引上，加了范圍為 (5, 10) 的間隙鎖，意味著其他事務(wù)無(wú)法插入 id 值為 6、7、8、9 的這一些新記錄。

針對(duì) 小于或者小于等于 的唯一索引范圍查詢，如果條件值的記錄不在表中，那么不管是「小于」還是「小于等于」的范圍查詢，掃描到終止范圍查詢的記錄時(shí)，該記錄中索引的 next-key 鎖會(huì)退化成間隙鎖，其他掃描的記錄，則是在這些記錄的索引上加 next-key 鎖。

針對(duì) 小于等于 的范圍查詢時(shí)，查詢條件值的記錄 存在 表中的情況。

假設(shè)事務(wù) A 執(zhí)行了這條范圍查詢語(yǔ)句，注意查詢條件值的記錄（id 為 5）存在于表中。

select * from user where id <= 5 for update;

此時(shí)，事務(wù) A 在主鍵索引上加了 2 個(gè) X 型的鎖：

• 在 id = 1 這條記錄的主鍵索引上，加了范圍為 (-∞, 1] 的 next-key 鎖。意味著其他事務(wù)即無(wú)法更新或者刪除 id = 1 的這一條記錄，同時(shí)也無(wú)法插入 id 小于 1 的這一些新記錄。
• 在 id = 5 這條記錄的主鍵索引上，加了范圍為 (1, 5] 的 next-key 鎖。意味著其他事務(wù)即無(wú)法更新或者刪除 id = 5 的這一條記錄，同時(shí)也無(wú)法插入 id 值為 2、3、4 的這一些新記錄。

為了避免幻讀，這里的鎖都不會(huì)退化

針對(duì) 小于 的范圍查詢時(shí)，查詢條件值的記錄 存在 表中的情況。

假設(shè)事務(wù) A 執(zhí)行了這條范圍查詢語(yǔ)句，注意查詢條件值的記錄（id 為 10）并不存在于表中。

select * from user where id < 10 for update;

此時(shí)，事務(wù) A 在主鍵索引上加了三個(gè) X 型的鎖：

• 在 id = 1 這條記錄的主鍵索引上，加了范圍為 (-∞, 1] 的 next-key 鎖，意味著其他事務(wù)無(wú)法更新或者刪除 id = 1 的這一條記錄，同時(shí)也無(wú)法插入 id 小于 1 的這一些新記錄。
• 在 id = 5 這條記錄的主鍵索引上，加了范圍為 (1, 5] 的 next-key 鎖，意味著其他事務(wù)無(wú)法更新或者刪除 id = 5 的這一條記錄，同時(shí)也無(wú)法插入 id 值為 2、3、4 的這一些新記錄。
• 在 id = 10 這條記錄的主鍵索引上，加了范圍為 (5, 10) 的間隙鎖，意味著其他事務(wù)無(wú)法插入 id 值為 6、7、8、9 的這一些新記錄。

這里加鎖與 “針對(duì) 小于/小于等于 的范圍查詢時(shí)，查詢條件值的記錄 不存在 表中的情況” 一致

非唯一索引等值查詢

當(dāng)使用非唯一索引進(jìn)行等值查詢的時(shí)候，因?yàn)榇嬖趦蓚€(gè)索引，一個(gè)是主鍵索引，一個(gè)是非唯一索引（二級(jí)索引），所以在加鎖時(shí)，同時(shí)會(huì)對(duì)這兩個(gè)索引都加鎖，但是對(duì)主鍵索引加鎖的時(shí)候，只有滿足查詢條件的記錄才會(huì)對(duì)它們的主鍵索引加鎖。

針對(duì)非唯一索引等值查詢時(shí)，查詢的記錄存不存在，加鎖的規(guī)則也會(huì)不同：

• 當(dāng)查詢的記錄「存在」時(shí)，由于不是唯一索引，所以肯定存在索引值相同的記錄，于是非唯一索引等值查詢的過(guò)程是一個(gè)掃描的過(guò)程，直到掃描到第一個(gè)不符合條件的二級(jí)索引記錄就停止掃描，然后在掃描的過(guò)程中，對(duì)掃描到的二級(jí)索引記錄加的是 next-key 鎖，而對(duì)于第一個(gè)不符合條件的二級(jí)索引記錄，該二級(jí)索引的 next-key 鎖會(huì)退化成間隙鎖。同時(shí)，在符合查詢條件的記錄的主鍵索引上加記錄鎖。
• 當(dāng)查詢的記錄「不存在」時(shí)，掃描到第一條不符合條件的二級(jí)索引記錄，該二級(jí)索引的 next-key 鎖會(huì)退化成間隙鎖。因?yàn)椴淮嬖跐M足查詢條件的記錄，所以不會(huì)對(duì)主鍵索引加鎖。

記錄不存在的情況

假設(shè)事務(wù) A 對(duì)非唯一索引（age）進(jìn)行了等值查詢，且表中不存在 age = 25 的記錄。

select * from user where age = 25 for update;

定位到第一條不符合查詢條件的二級(jí)索引記錄，即掃描到 age = 39，于是該二級(jí)索引的 next-key 鎖會(huì)退化成間隙鎖，范圍是 (22, 39)。

此時(shí)事務(wù) A 在 age = 39 記錄的二級(jí)索引上（INDEX_NAME: index_age ），加了范圍為 (22, 39) 的 X 型間隙鎖。

此時(shí)，如果有其他事務(wù)插入了 age 值為 23、24、25、26、....、38 這些新記錄，那么這些插入語(yǔ)句都會(huì)發(fā)生阻塞。

原因在于需要避免出現(xiàn)幻讀，所以需要加這個(gè)間隙鎖

但是對(duì)于插入 age = 39 記錄的語(yǔ)句，在一些情況是可以成功插入的，而一些情況則無(wú)法成功插入

當(dāng)有一個(gè)事務(wù)持有二級(jí)索引的間隙鎖 (22, 39) 時(shí)，什么情況下，可以讓其他事務(wù)的插入 age = 22 或者 age = 39 記錄的語(yǔ)句成功？又是什么情況下，插入 age = 22 或者 age = 39 記錄時(shí)的語(yǔ)句會(huì)被阻塞？

插入 age = 22的記錄

• 當(dāng)其他事務(wù)插入一條 age = 22，id = 3 的記錄的時(shí)候，在二級(jí)索引樹(shù)上定位到插入的位置，而該位置的下一條是 id = 10、age = 22 的記錄，該記錄的二級(jí)索引上沒(méi)有間隙鎖，所以這條插入語(yǔ)句可以執(zhí)行成功。
• 當(dāng)其他事務(wù)插入一條 age = 22，id = 12 的記錄的時(shí)候，在二級(jí)索引樹(shù)上定位到插入的位置，而該位置的下一條是 id = 20、age = 39 的記錄，正好該記錄的二級(jí)索引上有間隙鎖，所以這條插入語(yǔ)句會(huì)被阻塞，無(wú)法插入成功。

插入age = 39的記錄

• 當(dāng)其他事務(wù)插入一條 age = 39，id = 3 的記錄的時(shí)候，在二級(jí)索引樹(shù)上定位到插入的位置，而該位置的下一條是 id = 20、age = 39 的記錄，正好該記錄的二級(jí)索引上有間隙鎖，所以這條插入語(yǔ)句會(huì)被阻塞，無(wú)法插入成功。
• 當(dāng)其他事務(wù)插入一條 age = 39，id = 21 的記錄的時(shí)候，在二級(jí)索引樹(shù)上定位到插入的位置，而該位置的下一條記錄不存在，也就沒(méi)有間隙鎖了，所以這條插入語(yǔ)句可以插入成功。

當(dāng)有一個(gè)事務(wù)持有二級(jí)索引的間隙鎖 (22, 39) 時(shí)，插入 age = 22 或者 age = 39 記錄的語(yǔ)句是否可以執(zhí)行成功，關(guān)鍵還要考慮插入記錄的主鍵值，因?yàn)椤付?jí)索引值（age列）+主鍵值（id列）」才可以確定插入的位置，確定了插入位置后，就要看插入的位置的下一條記錄是否有間隙鎖，如果有間隙鎖，就會(huì)發(fā)生阻塞，如果沒(méi)有間隙鎖，則可以插入成功。

記錄存在的情況

假設(shè)事務(wù) A 對(duì)非唯一索引（age）進(jìn)行了等值查詢，且表中存在 age = 22 的記錄。

select * from user where age = 22 for update;

• 由于不是唯一索引，所以肯定存在值相同的記錄，于是非唯一索引等值查詢的過(guò)程是一個(gè)掃描的過(guò)程，最開(kāi)始要找的第一行是 age = 22，于是對(duì)該二級(jí)索引記錄加上范圍為 (21, 22] 的 next-key 鎖。同時(shí)，因?yàn)?age = 22 符合查詢條件，于是對(duì) age = 22 的記錄的主鍵索引加上記錄鎖，即對(duì) id = 10 這一行加記錄鎖。
• 接著繼續(xù)掃描，掃描到的第二行是 age = 39，該記錄是第一個(gè)不符合條件的二級(jí)索引記錄，所以該二級(jí)索引的 next-key 鎖會(huì)退化成間隙鎖，范圍是 (22, 39)。
• 停止查詢。

此時(shí)，事務(wù) A 對(duì)主鍵索引和二級(jí)索引都加了 X 型的鎖：

• 主鍵索引：
  • 在 id = 10 這條記錄的主鍵索引上，加了記錄鎖，意味著其他事務(wù)無(wú)法更新或者刪除 id = 10 的這一行記錄。
• 二級(jí)索引（非唯一索引）：
  • 在 age = 22 這條記錄的二級(jí)索引上，加了范圍為 (21, 22] 的 next-key 鎖，意味著其他事務(wù)無(wú)法更新或者刪除 age = 22 的這一些新記錄，不過(guò)對(duì)于插入 age = 20 和 age = 21 新記錄的語(yǔ)句，在一些情況是可以成功插入的，而一些情況則無(wú)法成功插入。
  • 在 age = 39 這條記錄的二級(jí)索引上，加了范圍 (22, 39) 的間隙鎖。意味著其他事務(wù)無(wú)法插入 age 值為 23、24、..... 、38 的這一些新記錄。不過(guò)對(duì)于插入 age = 22 和 age = 39 記錄的語(yǔ)句，在一些情況是可以成功插入的，而一些情況則無(wú)法成功插入。

插入age = 22 和 age = 21的情況

在 age = 22 這條記錄的二級(jí)索引上，加了范圍為 (21, 22] 的 next-key 鎖，意味著其他事務(wù)無(wú)法更新或者刪除 age = 22 的記錄，針對(duì)是否可以插入 age = 21 和 age = 22 的新記錄，分析如下：

• 是否可以插入 age = 21 的新記錄，還要看插入的新記錄的 id 值，如果插入 age = 21 新記錄的 id 值小于 5，那么就可以插入成功，因?yàn)榇藭r(shí)插入的位置的下一條記錄是 id = 5，age = 21 的記錄，該記錄的二級(jí)索引上沒(méi)有間隙鎖。如果插入 age = 21 新記錄的 id 值大于 5，那么就無(wú)法插入成功，因?yàn)榇藭r(shí)插入的位置的下一條記錄是 id = 10，age = 22 的記錄，該記錄的二級(jí)索引上有間隙鎖。
• 是否可以插入 age = 22 的新記錄，還要看插入的新記錄的 id 值，其他事務(wù)插入 age 值為 22 的新記錄時(shí)，如果插入的新記錄的 id 值小于 10，那么無(wú)法插入成功；如果插入的新記錄的 id 大于 10，還要看該新記錄插入的位置的下一條記錄是否有間隙鎖，如果沒(méi)有間隙鎖則可以插入成功，如果有間隙鎖，則無(wú)法插入成功（這里即使由于有間隙鎖，無(wú)法插入成功）。

插入age = 39 的情況

在 age = 39 這條記錄的二級(jí)索引上，加了范圍 (22, 39) 的間隙鎖。意味著其他事務(wù)無(wú)法插入 age 值為 23、24、..... 、38 的這一些新記錄，針對(duì)是否可以插入age = 39 的新記錄，分析如下：

• 是否可以插入 age = 39 的新記錄，還要看插入的新記錄的 id 值，其他事務(wù)插入 age 值為 39 的新記錄時(shí)，如果插入的新記錄的 id 值小于 20，那么插入語(yǔ)句會(huì)發(fā)生阻塞，如果插入的新記錄的 id 大于 20，則可以插入成功。

這里加的三個(gè)鎖，都是為了避免幻讀現(xiàn)象的發(fā)生。記錄鎖，避免了age = 22,id = 10這條記錄被更新或刪除；另外兩個(gè)二級(jí)索引鎖，都避免了被新插入age = 22的記錄

非唯一索引范圍查詢

非唯一索引范圍查詢，索引的 next-key lock 不會(huì)有退化為間隙鎖和記錄鎖的情況，也就是非唯一索引進(jìn)行范圍查詢時(shí)，對(duì)二級(jí)索引記錄加鎖都是加 next-key 鎖。

事務(wù) A 的這條范圍查詢語(yǔ)句：

select * from user where age >= 22  for update;

此時(shí)，事務(wù) A 對(duì)主鍵索引和二級(jí)索引都加了 X 型的鎖：

• 主鍵索引（id 列）：
  • 在 id = 10 這條記錄的主鍵索引上，加了記錄鎖，意味著其他事務(wù)無(wú)法更新或者刪除 id = 10 的這一行記錄。
  • 在 id = 20 這條記錄的主鍵索引上，加了記錄鎖，意味著其他事務(wù)無(wú)法更新或者刪除 id = 20 的這一行記錄。
• 二級(jí)索引（age 列）：
  • 在 age = 22 這條記錄的二級(jí)索引上，加了范圍為 (21, 22] 的 next-key 鎖，意味著其他事務(wù)無(wú)法更新或者刪除 age = 22 的這一些新記錄，不過(guò)對(duì)于是否可以插入 age = 21 和 age = 22 的新記錄，還需要看新記錄的 id 值，與前面一致，不再贅述。
  • 在 age = 39 這條記錄的二級(jí)索引上，加了范圍為 (22, 39] 的 next-key 鎖，意味著其他事務(wù)無(wú)法更新或者刪除 age = 39 的這一些記錄，也無(wú)法插入 age 值為 23、24、25、...、38 的這一些新記錄。不過(guò)對(duì)于是否可以插入age = 39 的新記錄，還需要看新記錄的 id 值，與前面一致，不再贅述。
  • 在特殊的記錄（supremum pseudo-record）的二級(jí)索引上，加了范圍為 (39, +∞] 的 next-key 鎖，意味著其他事務(wù)無(wú)法插入 age 值大于 39 的這些新記錄。

在 age >= 22 的范圍查詢中，明明查詢 age = 22 的記錄存在并且屬于等值查詢，為什么不會(huì)像唯一索引那樣，將 age = 22 記錄的二級(jí)索引上的 next-key 鎖退化為記錄鎖？
這是因?yàn)?age 字段是非唯一索引，不具有唯一性，所以如果只加記錄鎖（記錄鎖無(wú)法防止插入，只能防止刪除或者修改），就會(huì)導(dǎo)致其他事務(wù)插入一條 age = 22 的記錄，這樣前后兩次查詢的結(jié)果集就不相同了，出現(xiàn)了幻讀現(xiàn)象。

沒(méi)有加索引的查詢

如果鎖定讀查詢語(yǔ)句，沒(méi)有使用索引列作為查詢條件，或者查詢語(yǔ)句沒(méi)有走索引查詢，導(dǎo)致掃描是全表掃描。那么，每一條記錄的索引上都會(huì)加 next-key 鎖，這樣就相當(dāng)于鎖住的全表，這時(shí)如果其他事務(wù)對(duì)該表進(jìn)行增、刪、改操作的時(shí)候，都會(huì)被阻塞。

不只是鎖定讀查詢語(yǔ)句不加索引才會(huì)導(dǎo)致這種情況，update 和 delete 語(yǔ)句如果查詢條件不加索引，那么由于掃描的方式是全表掃描，于是就會(huì)對(duì)每一條記錄的索引上都會(huì)加 next-key 鎖，這樣就相當(dāng)于鎖住的全表。

因此，在線上在執(zhí)行 update、delete、select ... for update 等具有加鎖性質(zhì)的語(yǔ)句，一定要檢查語(yǔ)句是否走了索引，如果是全表掃描的話，會(huì)對(duì)每一個(gè)索引加 next-key 鎖，相當(dāng)于把整個(gè)表鎖住了，這是挺嚴(yán)重的問(wèn)題。當(dāng)然，帶了索引可能也是走的全表掃描，但是不帶索引肯定是全表掃描。

總結(jié)

到此這篇關(guān)于Mysql的行級(jí)鎖到底是怎么加的文章就介紹到這了,更多相關(guān)Mysql行級(jí)鎖怎么加內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論