{"id":17846,"date":"2025-04-26T18:31:26","date_gmt":"2025-04-26T10:31:26","guid":{"rendered":"https:\/\/www.orczhou.com\/?p=17846"},"modified":"2025-04-27T19:17:13","modified_gmt":"2025-04-27T11:17:13","slug":"mysql-innodb-the-gap-lock","status":"publish","type":"post","link":"https:\/\/www.orczhou.com\/index.php\/2025\/04\/mysql-innodb-the-gap-lock\/","title":{"rendered":"MySQL\/InnoDB \u9501\u8bca\u65ad\uff1a\u95f4\u9699\u9501"},"content":{"rendered":"<div id=\"tocer-1\" class=\"tocer tocer-pos-flt-e\"><div class=\"tocer-toc\"><header class=\"tocer-header\"><h2 class=\"tocer-title\">\u76ee\u5f55<\/h2>\n<\/header>\n<ul class=\"tocer-list tocer-adv tocer-sp-os-bf tocer-st-ctrs-dec tocer-lvl1\"><li><div><a href=\"#tocer-heading-1\">\u4e3a\u4ec0\u4e48\u9700\u8981\u95f4\u9699\u9501<\/a><\/div><\/li>\n<li><div><a href=\"#tocer-heading-2\">InnoDB \u4e2d\u201c\u95f4\u9699\u9501\u201d\u7684\u8868\u793a<\/a><\/div><\/li>\n<li><div><a href=\"#tocer-heading-3\">\u9501\u7c7b\u578b\u4e0e\u52a0\u9501\u6a21\u5f0f<\/a><\/div><\/li>\n<li class=\"tocer-has-sub\"><div><a href=\"#tocer-heading-4\">UPDATE \u8bed\u53e5\u7684\u95f4\u9699\u9501<\/a><\/div>\n<ul class=\"tocer-sublist tocer-lvl2\"><li><div><a href=\"#tocer-heading-5\">\u73af\u5883\u8bf4\u660e<\/a><\/div><\/li>\n<li><div><a href=\"#tocer-heading-6\">\u573a\u666f\u6784\u9020<\/a><\/div><\/li>\n<li><div><a href=\"#tocer-heading-7\">\u89c2\u6d4b\u95f4\u9699\u9501<\/a><\/div><\/li>\n<li><div><a href=\"#tocer-heading-8\">\u6d4b\u8bd5\u9a8c\u8bc1<\/a><\/div><\/li>\n<\/ul>\n<\/li>\n<li><div><a href=\"#tocer-heading-9\">\u53c2\u8003\u94fe\u63a5<\/a><\/div><\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n<h3 class=\"wp-block-heading\">\u4e3a\u4ec0\u4e48\u9700\u8981\u95f4\u9699\u9501<\/h3>\n\n\n\n<p>\u5173\u4e8e\u4e3a\u4ec0\u4e48\u9700\u8981 \u201cGap Locks\u201d \u6216\u8005 \u201cNext-key Locks\u201d \uff0c\u5728MySQL\u7684\u6587\u6863\u201c<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.4\/en\/innodb-locking.html#innodb-gap-locks\">17.7.1 InnoDB Locking<\/a>\u201d\u7684\u5c0f\u8282\u4e2d\u6709\u8f83\u4e3a\u8be6\u7ec6\u7684\u4ecb\u7ecd\uff0c\u8fd9\u91cc\u4e0d\u518d\u8d58\u8ff0\u3002\u8fd9\u91cc\u4f7f\u7528\u4e00\u4e2a\u5177\u4f53\u7684\u793a\u4f8b\uff0c\u4ee5\u4fbf\u5f00\u53d1\u8005\u65b9\u4fbf\u6784\u9020\u4e0e\u89c2\u5bdf\u95f4\u9699\u9501\u3002<\/p>\n\n\n\n<p>\u5728<code>Repeatable-Read<\/code>\u9694\u79bb\u7ea7\u522b\u4e0b\uff0c\u5728\u6574\u4e2a\u4e8b\u52a1\u7684\u8fc7\u7a0b\u4e2d\uff0c\u6570\u636e\u9700\u8981\u4fdd\u6301\u4e00\u81f4\uff0c\u7ecf\u5e38\u9700\u8981\u4f7f\u7528\u95f4\u9699\u9501\u5bf9\u6570\u636e\u6216\u8d44\u6e90\u8fdb\u884c\u4fdd\u62a4\u3002\u4f8b\u5982\uff0c\u5728\u5982\u4e0b\u7684\u4e8b\u52a1\u4e2d\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">DROP TABLE IF EXISTS t1;\nCREATE TABLE t1 (\n  id int,\n  nick varchar(32),\n  age int,\n  KEY ind_n (nick),\n  PRIMARY KEY (id)\n);\n\nINSERT INTO t1 VALUES ( 1, \"a\", 27 );\nINSERT INTO t1 VALUES ( 11, \"k\" ,23 );\nINSERT INTO t1 VALUES ( 24, \"x\" ,22 );\n\nSTART TRANSACTION;\nSELECT * FROM t1 WHERE nick &gt;= \"k\" for update;\n+----+------+------+\n| id | nick | age  |\n+----+------+------+\n| 11 | k    |   23 |\n| 24 | x    |   22 |\n+----+------+------+\n2 rows in set (0.00 sec)  Warnings: 0<\/code><\/pre>\n\n\n\n<p>\u5728\u4e0a\u8ff0\u4e8b\u52a1\u4e2d\uff0c\u4e3a\u4e86\u5b9e\u73b0\u5c06 <code>SELECT<\/code> \u6d89\u53ca\u7684\u201c\u76f8\u5173\u8bb0\u5f55\u201d\u5168\u90e8\u52a0\u4e0a\u201c\u6392\u5b83\u9501\u201d\uff0c\u4ece\u800c\u963b\u6b62\u5176\u4ed6\u4e8b\u52a1\u5bf9\u8be5\u90e8\u5206\u6570\u636e\u8fdb\u884c\u4fee\u6539\u3002\u5373\uff0c\u5982\u679c\u6709\u4efb\u4f55\u5176\u4ed6\u7684\u4e8b\u52a1\uff0c\u5c1d\u8bd5\u4fee\u6539\u8be5\u4e8b\u52a1\u4e2d\u7684\u201c\u76f8\u5173\u8bb0\u5f55\u201d\uff0c\u90fd\u9700\u8981\u88ab\u963b\u585e\u3002\u8fd9\u91cc\u7684\u201c\u76f8\u5173\u8bb0\u5f55\u201d\uff0c\u5177\u4f53\u662f\u6307\uff1a<code>WHERE nick &gt;= \"k\"<\/code> \u67e5\u8be2\u626b\u63cf\u5230\u7684\u7d22\u5f15\u5165\u53e3\uff08\u4e8c\u7ea7\u7d22\u5f15\uff09\uff0c\u4ee5\u53ca\u5bf9\u5e94\u7684\u6570\u636e\uff08\u5373\u4e3b\u952e\u5165\u53e3\uff09\u3002<\/p>\n\n\n\n<p>\u8bd5\u60f3\uff0c\u5982\u679c\u6709\u5176\u4ed6\u4e8b\u52a1\u5c1d\u8bd5\u5199\u5165\u4e00\u6761 <code>nick = 'm'<\/code>\u7684\u8bb0\u5f55\uff0c\u90a3\u4e48\u4e0a\u8ff0\u7684<code>...for update<\/code>\u8bed\u53e5\u5219\u4e5f\u4f1a\u8fd4\u56de\u8be5\u8bb0\u5f55\u3002\u4e3a\u4e86\u963b\u6b62\u4e0a\u8ff0\u7684\u4e0d\u4e00\u81f4\uff0c\u4e0a\u8ff0\u4e8b\u52a1\u4e0d\u4ec5\u8981\u5bf9\u5355\u4e2a\u8bb0\u5f55\u6216\u7d22\u5f15\u5165\u53e3\u8fdb\u884c\u52a0\u9501\uff0c\u8fd8\u9700\u8981\u5bf9\u7d22\u5f15\u5165\u53e3\u4e4b\u95f4\u7684\u95f4\u9699\u8fdb\u884c\u52a0\u9501\u3002<\/p>\n\n\n\n<p>\u5177\u4f53\u7684\uff0c\u8be5\u6848\u4f8b\u4e2d\u8be6\u7ec6\u7684\u9501\u4fe1\u606f\u5982\u4e0b\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">mysql&gt; SELECT\n    ENGINE_TRANSACTION_ID AS TRX_ID,     OBJECT_NAME,     \n    INDEX_NAME, LOCK_MODE,     LOCK_STATUS,     \n    LOCK_DATA    FROM performance_schema.data_locks \n  WHERE LOCK_TYPE=\"RECORD\";\n+--------+-------------+------------+---------------+-------------+------------------------+\n| TRX_ID | OBJECT_NAME | INDEX_NAME | LOCK_MODE     | LOCK_STATUS | LOCK_DATA              |\n+--------+-------------+------------+---------------+-------------+------------------------+\n|  10703 | t1          | ind_n      | X             | GRANTED     | supremum pseudo-record |\n|  10703 | t1          | ind_n      | X             | GRANTED     | 'k', 11                |\n|  10703 | t1          | ind_n      | X             | GRANTED     | 'x', 24                |\n|  10703 | t1          | PRIMARY    | X,REC_NOT_GAP | GRANTED     | 11                     |\n|  10703 | t1          | PRIMARY    | X,REC_NOT_GAP | GRANTED     | 24                     |\n+--------+-------------+------------+---------------+-------------+------------------------+<\/code><\/pre>\n\n\n\n<p>\u4e0a\u8ff0\u7684\u9501\u4fe1\u606f\u663e\u793a\uff0c\u8be5\u4e8b\u52a1\u83b7\u5f97\u4e86\u7d22\u5f15 <code>(a,supremum)<\/code>\u95f4\u9699\u7684\u6392\u5b83\u9501\uff0c\u4ee5\u53ca\u5bf9\u5e94\u7684\u6570\u636e\u8bb0\u5f55\u9501\u3002<\/p>\n\n\n\n<p>\u5982\u679c\u8bf4\u4e0a\u8ff0\u6848\u4f8b\u4e2d\u7684 <code>SELECT ... FOR UPDATE<\/code>\u5728\u5b9e\u9645\u4e2d\u4e0d\u90a3\u4e48\u5e38\u89c1\u7684\u8bdd\uff0c\u7c7b\u4f3c\u7684\u76f4\u63a5\u4f7f\u7528<code>UPDATE<\/code>\u8bed\u53e5\u4e5f\u9700\u8981\u7c7b\u4f3c\u7684\u52a0\u9501\u4fe1\u606f\uff0c\u8fd9\u91cc\u4f7f\u7528<code>SELECT ... FOR UPDATE<\/code>\u5c55\u793a\u5219\u66f4\u4e3a\u76f4\u63a5\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">InnoDB \u4e2d\u201c\u95f4\u9699\u9501\u201d\u7684\u8868\u793a<\/h3>\n\n\n\n<p>\u7406\u89e3 InnoDB \u9501\u7684\u56f0\u96be\u6709\u5f88\u591a\uff0c\u5176\u4e2d\u4e00\u4e2a\u975e\u5e38\u5927\u7684\u56f0\u96be\u5c31\u662f\u7406\u89e3\u5728 InnoDB \u95f4\u9699\u9501\u7684\u8868\u793a\uff0c\u56e0\u4e3a InnoDB \u4f7f\u7528\u4e86\u4e00\u4e2a\u6bd4\u8f83\u53cd\u76f4\u89c9\u7684\u6a21\u5f0f\u53bb\u5b9e\u73b0\u95f4\u9699\u9501\u3002\u5177\u4f53\u7684\uff1aInnoDB \u5728\u8868\u793a\u95f4\u9699\u9501\u7684\u65f6\u5019\uff0c\u5e76\u6ca1\u6709\u628a <code>GAP<\/code>\u6216<code>Next-Key<\/code>\u5f53\u505a\u4e00\u4e2a\u201c\u8d44\u6e90\u201d\u3002\u800c\u662f\u4f9d\u65e7\u628a<code>Record<\/code>\u5f53\u505a\u8d44\u6e90\uff0c\u7136\u540e\uff0c\u4ee5\u201c\u9501\u7c7b\u578b\u7684\u201d\uff08<code>Lock Mode<\/code>\uff09\u4e0d\u540c\u7684\u53d6\u503c\u8868\u793a\u8bb0\u5f55\u9501\u6216\u95f4\u9699\u9501\u3002\u4f8b\u5982\uff0c<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>Lock Mode<\/code>\u53d6\u503c\u4e3a<code>S,GAP<\/code>\u65f6\uff0c\u5219\u8868\u793a\u95f4\u9699\u9501\uff0c\u9501\u7c7b\u578b\u4e3a<code>S<\/code><\/li>\n\n\n\n<li><code>Lock Mode<\/code>\u53d6\u503c\u4e3a<code>S,REC_NOT_GAP<\/code>\u65f6\uff0c\u5219\u8868\u793a\u8bb0\u5f55\u9501\uff0c\u9501\u7c7b\u578b\u4e3a<code>S<\/code><\/li>\n<\/ul>\n\n\n\n<p>\u9501\u7684\u5bf9\u8c61\uff0c\u5747\u4e3a\u7d22\u5f15\u5165\u53e3\u6216\u4e3b\u952e\u5165\u53e3\u3002<\/p>\n\n\n\n<p>\u5173\u4e8e\u8be5\u5b9e\u73b0\uff0c\u5728<a href=\"https:\/\/dev.mysql.com\/blog-archive\/innodb-data-locking-part-2-locks\/\">InnoDB Data Locking &#8211; Part 2 &#8220;Locks&#8221;<\/a>\u4e2d\u6709\u7740\u8f83\u4e3a\u8be6\u7ec6\u7684\u63cf\u8ff0\uff1a<\/p>\n\n\n\n<blockquote class=\"wp-block-quote has-text-align-right is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"has-text-align-left\">\u201cEven without knowing too much about how databases like InnoDB operate, we can guess, that sometimes the operation involves just the record, sometimes the gap before a record, and at yet another times we need to access both, the record and a gap. One way to model that, would be to consider records and gaps to be two different kinds of resources which you can lock independently. Current InnoDB implementation takes a different approach: there is just one resource for each point, but there are multiple kinds of access right you can request for it, and the access right specifies if you need the row, the gap or both parts. One benefit of this is that it is optimized for the most common case where you need both.\u201d<\/p>\n<cite><a href=\"https:\/\/dev.mysql.com\/blog-archive\/innodb-data-locking-part-2-locks\/\">InnoDB Data Locking &#8211; Part 2 &#8220;Locks&#8221;<\/a><\/cite><\/blockquote>\n\n\n\n<h3 class=\"wp-block-heading\">\u9501\u7c7b\u578b\u4e0e\u52a0\u9501\u6a21\u5f0f<\/h3>\n\n\n\n<p>\u8fd9\u91cc\u4f7f\u7528\u5982\u4e0b\u8868\u683c\u8868\u793a\u4e86<code>InnoDB<\/code>\u5728\u8fdb\u884c\u52a0\u9501\u65f6\u5e38\u89c1\u7684\u52a0\u9501\u5bf9\u8c61\u4e0e\u7c7b\u578b\u3002\u8fd9\u91cc<code>D<sub>m<\/sub><\/code>\u8868\u793a\u5177\u4f53\u7684\u52a0\u9501\u6570\u636e\uff0c\u4f8b\u5982\u7d22\u5f15\u5165\u53e3\u6570\u636e\u6216\u4e3b\u952e\u6570\u636e\u3002\u8be6\u7ec6\u7684\u8868\u683c\u5982\u4e0b\uff1a<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td class=\"has-text-align-center\" data-align=\"center\"><code>LOCK_DATA<\/code><br>\u52a0\u9501\u76ee\u6807\/\u8d44\u6e90<\/td><td class=\"has-text-align-center\" data-align=\"center\"><code>LOCK_MODE<\/code><br>\u52a0\u9501\u6a21\u5f0f<\/td><td class=\"has-text-align-center\" data-align=\"center\">\u52a0\u9501\u5bf9\u8c61\/<br>\u8d44\u6e90<\/td><td class=\"has-text-align-center\" data-align=\"center\">\u52a0\u9501\u7c7b\u578b<\/td><\/tr><tr><td class=\"has-text-align-center\" data-align=\"center\"><code>D<sub>m<\/sub><\/code><\/td><td class=\"has-text-align-center\" data-align=\"center\"><code>S,REC_NOT_GAP<\/code><\/td><td class=\"has-text-align-center\" data-align=\"center\"><code><code>D<sub>m<\/sub><\/code><\/code><\/td><td class=\"has-text-align-center\" data-align=\"center\"><code>S<\/code><\/td><\/tr><tr><td class=\"has-text-align-center\" data-align=\"center\"><code>D<sub>m<\/sub><\/code><\/td><td class=\"has-text-align-center\" data-align=\"center\"><code>X,REC_NOT_GAP<\/code><\/td><td class=\"has-text-align-center\" data-align=\"center\"><code><code>D<sub>m<\/sub><\/code><\/code><\/td><td class=\"has-text-align-center\" data-align=\"center\"><code>X<\/code><\/td><\/tr><tr><td class=\"has-text-align-center\" data-align=\"center\"><code>D<sub>m<\/sub><\/code><\/td><td class=\"has-text-align-center\" data-align=\"center\"><code>S,GAP<\/code><\/td><td class=\"has-text-align-center\" data-align=\"center\"><code>(D<sub>(m-1)<\/sub>,<code>D<sub>m<\/sub><\/code>)<\/code><\/td><td class=\"has-text-align-center\" data-align=\"center\"><code>S<\/code><\/td><\/tr><tr><td class=\"has-text-align-center\" data-align=\"center\"><code>D<sub>m<\/sub><\/code><\/td><td class=\"has-text-align-center\" data-align=\"center\"><code>X,GAP<\/code><\/td><td class=\"has-text-align-center\" data-align=\"center\"><code>(D<sub>(m-1)<\/sub>,<code>D<sub>m<\/sub><\/code>)<\/code><\/td><td class=\"has-text-align-center\" data-align=\"center\"><code>X<\/code><\/td><\/tr><tr><td class=\"has-text-align-center\" data-align=\"center\"><code>D<sub>m<\/sub><\/code><\/td><td class=\"has-text-align-center\" data-align=\"center\"><code>S<\/code><\/td><td class=\"has-text-align-center\" data-align=\"center\"><code>(D<sub>(m-1)<\/sub>,<code>D<sub>m<\/sub><\/code>]<\/code><\/td><td class=\"has-text-align-center\" data-align=\"center\"><code>S<\/code><\/td><\/tr><tr><td class=\"has-text-align-center\" data-align=\"center\"><code>D<sub>m<\/sub><\/code><\/td><td class=\"has-text-align-center\" data-align=\"center\"><code>X<\/code><\/td><td class=\"has-text-align-center\" data-align=\"center\"><code>(D<sub>(m-1)<\/sub>,<code>D<sub>m<\/sub><\/code>]<\/code><\/td><td class=\"has-text-align-center\" data-align=\"center\"><code>X<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>\u901a\u5e38\u7684\uff0c<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u5728<code>performance_schema<\/code>\u4e2d\u770b\u5230\u7684<code>D<sub>m<\/sub><\/code>\u53d6\u503c\u53ef\u80fd\u662f\u8fd9\u6837\uff1a<code>0x000000000207 <\/code> <\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u5728<code>show engine innodb status\\G<\/code>\u4e2d\u770b\u5230\u7684<code>D<sub>m<\/sub><\/code>\u4f1a\u662f\uff1a<code>0: len 4; hex 00000001; asc     ;;<\/code><\/li>\n\n\n\n<li><code>D<sub>(m-1)<\/sub><\/code>\u5219\u8868\u793a\u5728\u7d22\u5f15\u4e2d\uff0c<code>D<sub>m<\/sub><\/code>\u524d\u9762\u7684\u4e00\u4e2a\u7d22\u5f15\u5165\u53e3\u6216\u4e3b\u952e\u53d6\u503c<\/li>\n\n\n\n<li><code>(...)<\/code>\u8868\u793a\u5f00\u533a\u95f4\uff1b<code>(...]<\/code>\u8868\u793a\u534a\u5f00\u534a\u95ed\u533a\u95f4<\/li>\n<\/ul>\n\n\n\n<p>\u5728 <code>show engine innodb status\\G<\/code> \u8f93\u51fa\u793a\u4f8b\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">RECORD LOCKS space id 27 page no 4 n bits 72 index PRIMARY of table `sysbenchdb`.`t1` trx id 10094 lock mode S locks rec but not gap waiting\nRecord lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0\n 0: len 4; hex 00000001; asc     ;;\n 1: len 6; hex 00000000276d; asc     'm;;\n 2: len 7; hex 810000008d0110; asc        ;;\n 3: len 1; hex 61; asc a;;\n 4: len 4; hex 8000000c; asc     ;;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">UPDATE \u8bed\u53e5\u7684\u95f4\u9699\u9501<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">\u73af\u5883\u8bf4\u660e<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">mysql&gt; show variables like '%iso%';\n+-----------------------+-----------------+\n| Variable_name         | Value           |\n+-----------------------+-----------------+\n| transaction_isolation | REPEATABLE-READ |\n+-----------------------+-----------------+\n1 row in set (0.01 sec)<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">\u573a\u666f\u6784\u9020<\/h4>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><code>Session A<\/code><\/td><td><code>Session B<\/code> <\/td><\/tr><tr><td>  <code>DROP TABLE IF EXISTS t1;<\/code><br>  <code>CREATE TABLE t1 ( <\/code><br>    <code>id int,<\/code><br>    <code>nick varchar(32),<\/code><br>    <code>age int,<\/code><br>    <code>KEY ind_n (nick),<\/code><br>    <code>PRIMARY KEY (id)<\/code><br><code>);<\/code><br><br><code>INSERT INTO t1 VALUES (  1, \"a\", 27 );<\/code><br><code>INSERT INTO t1 VALUES ( 11, \"k\" ,23 );<\/code><br><code>INSERT INTO t1 VALUES ( 24, \"x\" ,22 );<\/code><\/td><td><\/td><\/tr><tr><td><code>START TRANSACTION;<\/code><\/td><td><\/td><\/tr><tr><td><code>UPDATE t1 SET age = 127 WHERE nick = \"k\";<\/code><\/td><td><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">\u89c2\u6d4b\u95f4\u9699\u9501<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">mysql&gt; SELECT      \n  ENGINE_TRANSACTION_ID AS TRX_ID,     \n  OBJECT_NAME,  INDEX_NAME, LOCK_MODE,     \n  LOCK_STATUS,  LOCK_DATA\nFROM performance_schema.data_locks WHERE LOCK_TYPE=\"RECORD\";\n+--------+-------------+------------+---------------+-------------+-----------+\n| TRX_ID | OBJECT_NAME | INDEX_NAME | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |\n+--------+-------------+------------+---------------+-------------+-----------+\n|  10644 | t1          | ind_n      | X,GAP         | GRANTED     | 'x', 24   |\n|  10644 | t1          | ind_n      | X             | GRANTED     | 'k', 11   |\n|  10644 | t1          | PRIMARY    | X,REC_NOT_GAP | GRANTED     | 11        |\n+--------+-------------+------------+---------------+-------------+-----------+<\/code><\/pre>\n\n\n\n<p><\/p>\n\n\n\n<p>\u53ef\u4ee5\u770b\u5230\uff0c\u5728\u7d22\u5f15\u4e0a\uff08<code>KEY ind_n (nick)<\/code>\uff09\u7684\u9501\u8303\u56f4\u4e3a\uff1a<code>(a,x)<\/code>\u3002\u8fd9\u4e2a\u8303\u56f4\u7531\u4e24\u4e2a\u9501\u6784\u6210\uff0c\u5206\u522b\u662f\uff1a<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>X,GAP : 'x', 24<\/code> \u5bf9\u5e94\u7684\u8303\u56f4\u4e3a <code>(k,x)<\/code><\/li>\n\n\n\n<li><code>X     : 'k', 11<\/code> \u5bf9\u5e94\u7684\u8303\u56f4\u4e3a <code>(a,k]<\/code><\/li>\n<\/ul>\n\n\n\n<p>\u6545\uff0c\u4e0a\u8ff0\u4e24\u4e2a\u8303\u56f4\uff0c\u5171\u540c\u6784\u6210\u7684\u8303\u56f4\u4e3a\uff1a<code>(a,x)<\/code>,\u9501\u7c7b\u578b\u90fd\u662f\u6392\u5b83\u9501\uff08<code>X<\/code>\uff09\u3002<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">\u6d4b\u8bd5\u9a8c\u8bc1<\/h4>\n\n\n\n<p>\u53ef\u4ee5\u5c1d\u8bd5\u5199\u5165\u4e00\u4e2a\u5728 <code>(a,k)<\/code>\u6216<code>(k,x)<\/code>\u8303\u56f4\u4e3a\u7684\u8bb0\u5f55\uff0c\u89c2\u5bdf\u963b\u585e\u60c5\u51b5\u3002\u7ee7\u7eed\u4e0a\u8ff0\u7684\u4e24\u4e2a<code>Session<\/code>\uff0c\u5e76\u6267\u884c\u5982\u4e0b\u7684SQL \u8bed\u53e5\u8fdb\u884c\u89c2\u5bdf\uff1a<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><code>Session A<\/code><\/td><td><code>Session B<\/code><\/td><\/tr><tr><td><\/td><td><code>START TRANSACTION;<\/code><\/td><\/tr><tr><td><\/td><td><code>INSERT INTO t1 VALUES (2,\"c\",32);<\/code><br><code>-- blocking \/ waiting<\/code><br><code>ROLLBACK<\/code><\/td><\/tr><tr><td><\/td><td><code>START TRANSACTION;<\/code><\/td><\/tr><tr><td><\/td><td><code>INSERT INTO t1 VALUES (2,\"m\",32);<\/code><br><code>-- blocking \/ waiting<\/code><br><code>ROLLBACK<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\u53c2\u8003\u94fe\u63a5<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/pages.cs.wisc.edu\/~remzi\/OSTEP\/threads-locks.pdf\">Locks @ Operating Systems: Three Easy Pieces<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>\u4e3a\u4ec0\u4e48\u9700\u8981\u95f4\u9699\u9501 \u5173\u4e8e\u4e3a\u4ec0\u4e48\u9700\u8981 \u201cGap Locks\u201d \u6216\u8005 \u201cNext-key Locks\u201d \uff0c\u5728MySQL\u7684\u6587\u6863\u201c17.7.1 InnoDB Locking\u201d\u7684\u5c0f\u8282\u4e2d\u6709\u8f83\u4e3a\u8be6\u7ec6\u7684\u4ecb\u7ecd\uff0c\u8fd9\u91cc\u4e0d\u518d\u8d58\u8ff0\u3002\u8fd9\u91cc\u4f7f\u7528\u4e00\u4e2a\u5177\u4f53\u7684\u793a\u4f8b\uff0c\u4ee5\u4fbf\u5f00\u53d1\u8005\u65b9\u4fbf\u6784\u9020\u4e0e\u89c2\u5bdf\u95f4\u9699\u9501\u3002 \u5728Repeatable-Read\u9694\u79bb\u7ea7\u522b\u4e0b\uff0c\u5728\u6574\u4e2a\u4e8b\u52a1\u7684\u8fc7\u7a0b\u4e2d\uff0c\u6570\u636e\u9700\u8981\u4fdd\u6301\u4e00\u81f4\uff0c\u7ecf\u5e38\u9700\u8981\u4f7f\u7528\u95f4\u9699\u9501\u5bf9\u6570\u636e\u6216\u8d44\u6e90\u8fdb\u884c\u4fdd\u62a4\u3002\u4f8b\u5982\uff0c\u5728\u5982\u4e0b\u7684\u4e8b\u52a1\u4e2d\uff1a \u5728\u4e0a\u8ff0\u4e8b\u52a1\u4e2d\uff0c\u4e3a\u4e86\u5b9e\u73b0\u5c06 SELECT \u6d89\u53ca\u7684\u201c\u76f8\u5173\u8bb0\u5f55\u201d\u5168\u90e8\u52a0\u4e0a\u201c\u6392\u5b83\u9501\u201d\uff0c\u4ece\u800c\u963b\u6b62\u5176\u4ed6\u4e8b\u52a1\u5bf9\u8be5\u90e8\u5206\u6570\u636e\u8fdb\u884c\u4fee\u6539\u3002\u5373\uff0c\u5982\u679c\u6709\u4efb\u4f55\u5176\u4ed6\u7684\u4e8b\u52a1\uff0c\u5c1d\u8bd5\u4fee\u6539\u8be5\u4e8b\u52a1\u4e2d\u7684\u201c\u76f8\u5173\u8bb0\u5f55\u201d\uff0c\u90fd\u9700\u8981\u88ab\u963b\u585e\u3002\u8fd9\u91cc\u7684\u201c\u76f8\u5173\u8bb0\u5f55\u201d\uff0c\u5177\u4f53\u662f\u6307\uff1aWHERE nick &gt;= &#8220;k&#8221; \u67e5\u8be2\u626b\u63cf\u5230\u7684\u7d22\u5f15\u5165\u53e3\uff08\u4e8c\u7ea7\u7d22\u5f15\uff09\uff0c\u4ee5\u53ca\u5bf9\u5e94\u7684\u6570\u636e\uff08\u5373\u4e3b\u952e\u5165\u53e3\uff09\u3002 \u8bd5\u60f3\uff0c\u5982\u679c\u6709\u5176\u4ed6\u4e8b\u52a1\u5c1d\u8bd5\u5199\u5165\u4e00\u6761 nick = &#8216;m&#8217;\u7684\u8bb0\u5f55\uff0c\u90a3\u4e48\u4e0a\u8ff0\u7684&#8230;for update\u8bed\u53e5\u5219\u4e5f\u4f1a\u8fd4\u56de\u8be5\u8bb0\u5f55\u3002\u4e3a\u4e86\u963b\u6b62\u4e0a\u8ff0\u7684\u4e0d\u4e00\u81f4\uff0c\u4e0a\u8ff0\u4e8b\u52a1\u4e0d\u4ec5\u8981\u5bf9\u5355\u4e2a\u8bb0\u5f55\u6216\u7d22\u5f15\u5165\u53e3\u8fdb\u884c\u52a0\u9501\uff0c\u8fd8\u9700\u8981\u5bf9\u7d22\u5f15\u5165\u53e3\u4e4b\u95f4\u7684\u95f4\u9699\u8fdb\u884c\u52a0\u9501\u3002 \u5177\u4f53\u7684\uff0c\u8be5\u6848\u4f8b\u4e2d\u8be6\u7ec6\u7684\u9501\u4fe1\u606f\u5982\u4e0b\uff1a \u4e0a\u8ff0\u7684\u9501\u4fe1\u606f\u663e\u793a\uff0c\u8be5\u4e8b\u52a1\u83b7\u5f97\u4e86\u7d22\u5f15 (a,supremum)\u95f4\u9699\u7684\u6392\u5b83\u9501\uff0c\u4ee5\u53ca\u5bf9\u5e94\u7684\u6570\u636e\u8bb0\u5f55\u9501\u3002 \u5982\u679c\u8bf4\u4e0a\u8ff0\u6848\u4f8b\u4e2d\u7684 SELECT &#8230; FOR UPDATE\u5728\u5b9e\u9645\u4e2d\u4e0d\u90a3\u4e48\u5e38\u89c1\u7684\u8bdd\uff0c\u7c7b\u4f3c\u7684\u76f4\u63a5\u4f7f\u7528UPDATE\u8bed\u53e5\u4e5f\u9700\u8981\u7c7b\u4f3c\u7684\u52a0\u9501\u4fe1\u606f\uff0c\u8fd9\u91cc\u4f7f\u7528SELECT &#8230; FOR UPDATE\u5c55\u793a\u5219\u66f4\u4e3a\u76f4\u63a5\u3002 InnoDB \u4e2d\u201c\u95f4\u9699\u9501\u201d\u7684\u8868\u793a \u7406\u89e3 InnoDB \u9501\u7684\u56f0\u96be\u6709\u5f88\u591a\uff0c\u5176\u4e2d\u4e00\u4e2a\u975e\u5e38\u5927\u7684\u56f0\u96be\u5c31\u662f\u7406\u89e3\u5728 InnoDB \u95f4\u9699\u9501\u7684\u8868\u793a\uff0c\u56e0\u4e3a InnoDB \u4f7f\u7528\u4e86\u4e00\u4e2a\u6bd4\u8f83\u53cd\u76f4\u89c9\u7684\u6a21\u5f0f\u53bb\u5b9e\u73b0\u95f4\u9699\u9501\u3002\u5177\u4f53\u7684\uff1aInnoDB \u5728\u8868\u793a\u95f4\u9699\u9501\u7684\u65f6\u5019\uff0c\u5e76\u6ca1\u6709\u628a GAP\u6216Next-Key\u5f53\u505a\u4e00\u4e2a\u201c\u8d44\u6e90\u201d\u3002\u800c\u662f\u4f9d\u65e7\u628aRecord\u5f53\u505a\u8d44\u6e90\uff0c\u7136\u540e\uff0c\u4ee5\u201c\u9501\u7c7b\u578b\u7684\u201d\uff08Lock Mode\uff09\u4e0d\u540c\u7684\u53d6\u503c\u8868\u793a\u8bb0\u5f55\u9501\u6216\u95f4\u9699\u9501\u3002\u4f8b\u5982\uff0c \u9501\u7684\u5bf9\u8c61\uff0c\u5747\u4e3a\u7d22\u5f15\u5165\u53e3\u6216\u4e3b\u952e\u5165\u53e3\u3002 \u5173\u4e8e\u8be5\u5b9e\u73b0\uff0c\u5728InnoDB Data Locking &#8211; Part 2 &#8220;Locks&#8221;\u4e2d\u6709\u7740\u8f83\u4e3a\u8be6\u7ec6\u7684\u63cf\u8ff0\uff1a \u201cEven [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":18114,"comment_status":"open","ping_status":"closed","sticky":false,"template":"wp-custom-template-a-1440-px-width-template","format":"standard","meta":{"_eb_attr":"","inline_featured_image":false,"_tocer_settings":[],"footnotes":""},"categories":[137],"tags":[],"class_list":["post-17846","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-learning-more"],"_links":{"self":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/posts\/17846","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/comments?post=17846"}],"version-history":[{"count":32,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/posts\/17846\/revisions"}],"predecessor-version":[{"id":18116,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/posts\/17846\/revisions\/18116"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/media\/18114"}],"wp:attachment":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/media?parent=17846"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/categories?post=17846"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/tags?post=17846"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}