溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

MySQL的InnoDB鎖機(jī)制介紹

發(fā)布時間：2021-09-17 15:48:15 來源：億速云 閱讀：125 作者：chen 欄目：MySQL數(shù)據(jù)庫

這篇文章主要介紹“MySQL的InnoDB鎖機(jī)制介紹”，在日常操作中，相信很多人在MySQL的InnoDB鎖機(jī)制介紹問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL的InnoDB鎖機(jī)制介紹”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！

一  背景
    MySQL鎖機(jī)制是一個極其復(fù)雜的實(shí)現(xiàn)，為數(shù)據(jù)庫并發(fā)訪問和數(shù)據(jù)一致提供保障。這里僅僅針對MySQL訪問數(shù)據(jù)的三種鎖做介紹，加深自己對鎖方面的掌握。
二 常見的鎖機(jī)制
我們知道對于InnoDB存儲引擎而言,MySQL 的行鎖機(jī)制是通過在索引上加鎖來鎖定要目標(biāo)數(shù)據(jù)行的。常見的有如下三種鎖類型，本文未聲明情況下都是在RR 事務(wù)隔離級別下的描述。
2.1 Record Locks 
  記錄鎖實(shí)際上是索引上的鎖，鎖定具體的一行或者多行記錄。當(dāng)表上沒有創(chuàng)建索引時，InnoDB會創(chuàng)建一個隱含的聚族索引，并且使用該索引鎖定數(shù)據(jù)。通常我們可以使用 show innodb status 看到行鎖相關(guān)的信息。
2.2 Gap Locks
 間隙鎖是鎖定具體的范圍，但是不包含行鎖本身。比如

  1. select * from  tab where id>10 and id<20；

RR事務(wù)隔離級別下會鎖定10-20之間的記錄，不允許類似15這樣的值插入到表里，以便消除“幻讀”帶來的影響。間隙鎖的跨度可以是1條記錄(Record low就可以認(rèn)為是一個特殊的間隙鎖 ，多行，或者為空。當(dāng)訪問的字段是唯一鍵/主鍵時，間隙鎖會降級為Record lock。RR事務(wù)隔離級別下訪問一個空行 ，也會有間隙鎖，后續(xù)會舉例子說明。
我們可以通過將事務(wù)隔離級別調(diào)整為RC 模式或者設(shè)置innodb_locks_unsafe_for_binlog=1 (該參數(shù)已經(jīng)廢棄)來禁用Gap鎖。

2.3 Next-Key Locks
  是Record Lock+Gap Locks,鎖定一個范圍并且包含索引本身。例如索引值包含 2，4，9，14 四個值，其gap鎖的區(qū)間如下：
  （-∞,2]，(2,4]，(4,9]，(9,14]，(14,+∞）
本文著重從主鍵,唯一鍵、非唯一索引,不存在值訪問四個方面來闡述RR模式下鎖的表現(xiàn)。
三 測試案例
3.1 主鍵/唯一鍵 

  1. CREATE TABLE `lck_primarkey` (

  2.   `id` int(11) NOT NULL,

  3.    val int(11) not null default 0,

  4.   primary key (`id`),

  5.   key  idx_val(val)

  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  7. insert into lck_primarkey values(2,3),(4,5),(9,8),(14,13)

會話1 

  1. [session1] >select * from   lck_primarkey;

  2. +----+-----+

  3. | id | val |

  4. +----+-----+

  5. | 2 | 3 |

  6. | 4 | 5 |

  7. | 9 | 8 |

  8. | 14 | 13 |

  9. +----+-----+

  10. 4 rows in set (0.00 sec)

  11. [session1] >begin;

  12. Query OK, 0 rows affected (0.00 sec)

  13. [session1] >select * from lck_primarkey  where id=9 for update;

  14. +----+-----+

  15. | id | val |

  16. +----+-----+

  17. | 9 | 8 |

  18. +----+-----+

  19. 1 row in set (0.00 sec)

會話2 

  1. [session2] >begin;

  2. Query OK, 0 rows affected (0.00 sec)

  3. [session2] >insert into lck_primarkey values(7,6);

  4. Query OK, 1 row affected (0.00 sec)

  5. [session2] >insert into lck_primarkey values(5,5);

  6. Query OK, 1 row affected (0.00 sec)

  7. [session2] >insert into lck_primarkey values(13,13);

  8. Query OK, 1 row affected (0.00 sec)

  9. [session2] >insert into lck_primarkey values(10,9);

  10. Query OK, 1 row affected (0.00 sec)

分析
   從例子看，當(dāng)訪問表的where字段是主鍵或者唯一鍵的時候，session2中的插入操作并未被 session1 中的id=8 影響。官方表述

  1. “Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.) For example, if the id column has a unique index, the following statement uses only an index-record lock for the row having id value 100 and it does not matter whether other sessions insert rows in the preceding gap:

  2.    select * from tab where id=100 for update”

  3. 就是說當(dāng)語句通過主鍵或者唯一鍵訪問數(shù)據(jù)的時候，Innodb會使用Record lock鎖住記錄本身，而不是使用間隙鎖鎖定范圍。

需要注意以下兩種情況:
1 通過主鍵或則唯一索引訪問不存在的值，也會產(chǎn)生GAP鎖。

  1. [session1] >begin;

  2. Query OK, 0 rows affected (0.00 sec)

  3. [session1] >select * from lck_primarkey  where id=7 for update;

  4. Empty set (0.00 sec)

  5. [session2] >insert into lck_primarkey values(8,13);

  6. ^CCtrl-C -- sending "KILL QUERY 303042481" to server ...

  7. Ctrl-C -- query aborted.

  8. ERROR 1317 (70100): Query execution was interrupted

  9. [session2] >insert into lck_primarkey values(5,13);

  10. ^CCtrl-C -- sending "KILL QUERY 303042481" to server ...

  11. Ctrl-C -- query aborted.

  12. ERROR 1317 (70100): Query execution was interrupted

  13. [session2] >insert into lck_primarkey values(3,13);

  14. Query OK, 1 row affected (0.00 sec)

  15. [session2] >insert into lck_primarkey values(10,13);

  16. Query OK, 1 row affected (0.00 sec)

2 通過唯一索引中的一部分字段來訪問數(shù)據(jù)，比如unique key(a,b,c) ，select * from tab where a=x and b=y; 讀者朋友可以自己做這個例子。

3.2 非唯一鍵

  1. CREATE TABLE `lck_secondkey` (

  2.   `id` int(11) NOT NULL,

  3.    KEY `idx_id` (`id`)

  4. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  5. insert into lck_secondkey values(2),(4),(9),(14)

會話1

  1. [session1] >begin ;

  2. Query OK, 0 rows affected (0.00 sec)

  3. [session1] >select * from lck_secondkey;

  4. +----+

  5. | id |

  6. +----+

  7. | 2 |

  8. | 3 |

  9. | 4 |

  10. | 9 |

  11. | 14 |

  12. +----+

  13. 5 rows in set (0.00 sec)

  14. [session1] >select * from lck_secondkey where id=9 for update;

  15. +----+

  16. | id |

  17. +----+

  18. | 9 |

  19. +----+

  20. 1 row in set (0.00 sec)

會話2

  1. [session2] >begin;

  2. Query OK, 0 rows affected (0.00 sec)

  3. [session2] >insert into lck_secondkey values(3);

  4. Query OK, 1 row affected (0.00 sec)

  5. [session2] >insert into lck_secondkey values(4);

  6. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...

  7. Ctrl-C -- query aborted.

  8. ERROR 1317 (70100): Query execution was interrupted

  9. [session2] >insert into lck_secondkey values(5);

  10. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...

  11. Ctrl-C -- query aborted.

  12. ERROR 1317 (70100): Query execution was interrupted

  13. [session2] >insert into lck_secondkey values(6);

  14. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...

  15. Ctrl-C -- query aborted.

  16. ERROR 1317 (70100): Query execution was interrupted

  17. [session2] >insert into lck_secondkey values(7);

  18. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...

  19. Ctrl-C -- query aborted.

  20. ERROR 1317 (70100): Query execution was interrupted

  21. [session2] >insert into lck_secondkey values(8);

  22. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...

  23. Ctrl-C -- query aborted.

  24. ERROR 1317 (70100): Query execution was interrupted

  25. [session2] >insert into lck_secondkey values(9);

  26. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...

  27. Ctrl-C -- query aborted.

  28. ERROR 1317 (70100): Query execution was interrupted

  29. [session2] >insert into lck_secondkey values(10);

  30. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...

  31. Ctrl-C -- query aborted.

  32. ERROR 1317 (70100): Query execution was interrupted

  33. [session2] >insert into lck_secondkey values(11);

  34. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...

  35. Ctrl-C -- query aborted.

  36. ERROR 1317 (70100): Query execution was interrupted

  37. [session2] >insert into lck_secondkey values(12);

  38. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...

  39. Ctrl-C -- query aborted.

  40. ERROR 1317 (70100): Query execution was interrupted

  41. [session2] >insert into lck_secondkey values(13);

  42. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...

  43. Ctrl-C -- query aborted.

  44. ERROR 1317 (70100): Query execution was interrupted

  45. [session2] >insert into lck_secondkey values(14);

  46. Query OK, 1 row affected (0.00 sec)

分析
  事務(wù)1 對id=9進(jìn)行for update 訪問,session2 插入[4,13]的值都是失敗的。根據(jù)MySQL的鎖原理，Innodb 范圍索引或者表是通過Next-key locks 算法，RR事務(wù)隔離級別下，通過非唯一索引訪問數(shù)據(jù)行并不是鎖定唯一的行，而是一個范圍。從例子上可以看出來MySQL對 [4,9] 和(9,14]之間的記錄加上了鎖，防止其他事務(wù)對4-14范圍中的值進(jìn)行修改。可能有讀者對其中 id=4 不能修改，但是id=14的值去可以插入有疑問？可以看接下來的例子

  1. [session1] >select * from  lck_primarkey;

  2. +----+-----+

  3. | id | val |

  4. +----+-----+

  5. | 2 | 3 |

  6. | 4 | 5 |

  7. | 9 | 8 |

  8. | 14 | 13 |

  9. +----+-----+

  10. 4 rows in set (0.00 sec)

  11. [session1] >begin;

  12. Query OK, 0 rows affected (0.00 sec)

  13. [session1] >select * from  lck_primarkey  where  val=8 for update;

  14. +----+-----+

  15. | id | val |

  16. +----+-----+

  17. | 9 | 8 |

  18. +----+-----+

  19. 1 row in set (0.00 sec)

會話2

  1. [session2] >begin;

  2. Query OK, 0 rows affected (0.00 sec)

  3. [session2] >insert into lck_primarkey values(3,5);

  4. Query OK, 1 row affected (0.00 sec)

  5. [session2] >insert into lck_primarkey values(15,13);

  6. Query OK, 1 row affected (0.00 sec)

  7. [session2] >select * from lck_primarkey;

  8. +----+-----+

  9. | id | val |

  10. +----+-----+

  11. | 2 | 3 |

  12. | 3 | 5 |

  13. | 4 | 5 |

  14. | 9 | 8 |

  15. | 14 | 13 |

  16. | 15 | 13 |

  17. +----+-----+

  18. 6 rows in set (0.00 sec)

  19. [session2] >insert into lck_primarkey values(16,12);

  20. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...

  21. Ctrl-C -- query aborted.

  22. ERROR 1317 (70100): Query execution was interrupted

  23. [session2] >insert into lck_primarkey values(16,6);

  24. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...

  25. Ctrl-C -- query aborted.

  26. ERROR 1317 (70100): Query execution was interrupted

  27. [session2] >insert into lck_primarkey values(16,5);

  28. ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

  29. [session2] >

  30. [session2] >insert into lck_primarkey values(1,5);

  31. Query OK, 1 row affected (0.00 sec)

分析
   因?yàn)閟ession1 對非唯一鍵val=8 加上了gap鎖 [4,5] -[14,13],非此區(qū)間的記錄都可以插入表中。記錄(1,5),(15,13)不在此gap鎖區(qū)間，記錄(16,12),(16,6),(16,5)中的val值在被鎖的范圍內(nèi)，故不能插入。

到此，關(guān)于“MySQL的InnoDB鎖機(jī)制介紹”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！

向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手