{"id":135729,"date":"2020-07-16T01:47:23","date_gmt":"2020-07-15T17:47:23","guid":{"rendered":"http:\/\/4563.org\/?p=135729"},"modified":"2020-07-16T01:47:23","modified_gmt":"2020-07-15T17:47:23","slug":"mysql-innodb-%e6%98%af%e5%90%a6%e5%9c%a8%e6%88%90%e5%8a%9f%e6%8f%92%e5%85%a5%e6%95%b0%e6%8d%ae%e4%b9%8b%e5%90%8e%e9%87%8a%e6%94%be%e4%ba%86%e6%8f%92%e5%85%a5%e6%84%8f%e5%90%91%e9%94%81%ef%bc%9f","status":"publish","type":"post","link":"http:\/\/4563.org\/?p=135729","title":{"rendered":"MySQL InnoDB \u662f\u5426\u5728\u6210\u529f\u63d2\u5165\u6570\u636e\u4e4b\u540e\u91ca\u653e\u4e86\u63d2\u5165\u610f\u5411\u9501\uff1f"},"content":{"rendered":"
\n
\n
\n

MySQL InnoDB \u662f\u5426\u5728\u6210\u529f\u63d2\u5165\u6570\u636e\u4e4b\u540e\u91ca\u653e\u4e86\u63d2\u5165\u610f\u5411\u9501\uff1f <\/h1>\n

<\/p>\n

\n
\u8cc7\u6df1\u5927\u4f6c : JasonLaw <\/span> <\/i> 17<\/span> <\/div>\n
<\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n
<\/p>\n

\u6267\u884c\u4ee5\u4e0b\u4ee3\u7801\u540e\uff0c<\/p>\n

create table t(id int primary key); insert into t values (5),(10);  -- session 1 start transaction; select * from t where id > 8 for share;  -- session 2 start transaction; insert into t values(6); -- is blocked <\/code><\/pre>\n
select * from performance_schema.data_locks<\/code>\u7684\u8f93\u51fa\u4e3a\uff1a<\/p>\n
+--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+------------------------+ | ENGINE | ENGINE_LOCK_ID                        | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE              | LOCK_STATUS | LOCK_DATA              | +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+------------------------+ | INNODB | 140043377180872:1063:140043381460688  |                  2132 |        49 |       56 | test          | t           | NULL           | NULL              | NULL       |       140043381460688 | TABLE     | IX                     | GRANTED     | NULL                   | | INNODB | 140043377180872:2:4:3:140043381457776 |                  2132 |        49 |       56 | test          | t           | NULL           | NULL              | PRIMARY    |       140043381457776 | RECORD    | X,GAP,INSERT_INTENTION | WAITING     | 10                     | | INNODB | 140043377180024:1063:140043381454544  |       421518353890680 |        48 |      105 | test          | t           | NULL           | NULL              | NULL       |       140043381454544 | TABLE     | IS                     | GRANTED     | NULL                   | | INNODB | 140043377180024:2:4:1:140043381451552 |       421518353890680 |        48 |      105 | test          | t           | NULL           | NULL              | PRIMARY    |       140043381451552 | RECORD    | S                      | GRANTED     | supremum pseudo-record | | INNODB | 140043377180024:2:4:3:140043381451552 |       421518353890680 |        48 |      105 | test          | t           | NULL           | NULL              | PRIMARY    |       140043381451552 | RECORD    | S                      | GRANTED     | 10                     | +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+------------------------+ <\/code><\/pre>\n
\u6211\u4eec\u53ef\u4ee5\u770b\u5230 session 2 \u6b63\u5728\u7b49\u5f85\u63d2\u5165\u610f\u5411\u9501\uff0c\u56e0\u4e3a\u8fd9\u4e2a\u63d2\u5165\u610f\u5411\u9501\u8ddf session 1 \u6b63\u5728\u62e5\u6709\u7684\u201c\u4e0b\u4e00\u952e\u9501(5, 10]\u201d\u51b2\u7a81\u3002\u8fd9\u8ddf\u6211\u9884\u60f3\u7684\u4e00\u6837\u3002\u4f46\u662f\u5982\u679c\u6211\u5c06 schedule \u4fee\u6539\u4e3a<\/p>\n
create table t(id int primary key); insert into t values (5),(10);  -- session 1 start transaction; insert into t values(6);  -- session 2 start transaction; select * from t where id > 8 for share; -- is not blocked <\/code><\/pre>\n
select * from performance_schema.data_locks<\/code>\u7684\u8f93\u51fa\u4e3a\uff1a<\/p>\n
+--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+ | ENGINE | ENGINE_LOCK_ID                        | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              | +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+ | INNODB | 140043377180024:1063:140043381454544  |                  2147 |        48 |      125 | test          | t           | NULL           | NULL              | NULL       |       140043381454544 | TABLE     | IX        | GRANTED     | NULL                   | | INNODB | 140043377180872:1063:140043381460688  |       421518353891528 |        49 |       86 | test          | t           | NULL           | NULL              | NULL       |       140043381460688 | TABLE     | IS        | GRANTED     | NULL                   | | INNODB | 140043377180872:2:4:1:140043381457776 |       421518353891528 |        49 |       86 | test          | t           | NULL           | NULL              | PRIMARY    |       140043381457776 | RECORD    | S         | GRANTED     | supremum pseudo-record | | INNODB | 140043377180872:2:4:3:140043381457776 |       421518353891528 |        49 |       86 | test          | t           | NULL           | NULL              | PRIMARY    |       140043381457776 | RECORD    | S         | GRANTED     | 10                     | +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+ <\/code><\/pre>\n
session 1 \u5e76\u6ca1\u6709\u62e5\u6709\u63d2\u5165\u610f\u5411\u9501\u3002\u90a3\u4e48\uff0c\u4e8b\u52a1\u662f\u5728\u6210\u529f\u63d2\u5165\u6570\u636e\u4e4b\u540e\u91ca\u653e\u4e86\u63d2\u5165\u610f\u5411\u9501\u5417\uff1f<\/p>\n<\/p><\/div>\n
 \u5927\u4f6c\u6709\u8a71\u8aaa<\/b> (4<\/span>)        <\/div>\n
 <\/div>\n<\/p><\/div>\n<\/p><\/div>\n
    \n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : izgnod <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 \u6709\u4e3b\u952e\u7d22\u5f15\u548c\u8f85\u52a9\u7d22\u5f15\u7684\u63d2\u5165\uff0c\u63d2\u5165\u524d\u83b7\u53d6\u8f85\u52a9\u7d22\u5f15\u7684\u63d2\u5165\u610f\u5411\u9501\uff0c\u63d2\u5165\u540e\u83b7\u53d6\u4e3b\u952e\u7684\u8bb0\u5f55\u9501\u3002\u4f60\u8fd9\u4e2a\u4e8b\u53ea\u6709\u4e3b\u952e\u6240\u4ee5\u53ea\u9700\u8981\u8bb0\u5f55\u9501\u3002                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u4e3b<\/span> \u8cc7\u6df1\u5927\u4f6c : JasonLaw <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 @izgnod #1 \u6211\u505a\u4e86\u4ee5\u4e0b\u5b9e\u9a8c\uff0c\u4e8b\u5b9e\u5e76\u4e0d\u662f\u4f60\u6240\u8bf4\u7684\u90a3\u6837\uff0c\u5e76\u4e0d\u662f\u201c\u63d2\u5165\u524d\u83b7\u53d6\u8f85\u52a9\u7d22\u5f15\u7684\u63d2\u5165\u610f\u5411\u9501\uff0c\u63d2\u5165\u540e\u83b7\u53d6\u4e3b\u952e\u7684\u8bb0\u5f55\u9501\u201d\u3002\u800c\u4e14\u95ee\u9898\u4e2d\u7684\u4e24\u4e2a schedules \u4e5f\u80fd\u8bc1\u660e\u4f60\u6240\u8bf4\u7684\u662f\u4e0d\u6b63\u786e\u7684\u3002<\/p>\n
    create table t2(id int primary key, value int, index ix_t2_value(value));
    insert into t2 values (5,10),(10,5);<\/p>\n
    — session 1
    start transaction;
    select * from t2 where id = 3 for share;<\/p>\n
    — session 2
    start transaction;
    insert into t2 values (2, 8); — \u7b49\u5f85\u83b7\u53d6 PRIMARY \u7d22\u5f15\u4e0a\u7684\u63d2\u5165\u610f\u5411\u9501(-\u221e , 5)<\/p>\n
    select * from performance_schema.data_locks \u7684\u8f93\u51fa\u4e3a\uff1a<\/p>\n
    +——–+—————————————+———————–+———–+———-+—————+————-+—————-+——————-+————+———————–+———–+————————+————-+———–+
    | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
    +——–+—————————————+———————–+———–+———-+—————+————-+—————-+——————-+————+———————–+———–+————————+————-+———–+
    | INNODB | 140311361761480:1063:140311280045776 | 2072 | 50 | 14 | test | t2 | NULL | NULL | NULL | 140311280045776 | TABLE | IX | GRANTED | NULL |
    | INNODB | 140311361761480:2:4:2:140311280042864 | 2072 | 50 | 14 | test | t2 | NULL | NULL | PRIMARY | 140311280042864 | RECORD | X,GAP,INSERT_INTENTION | WAITING | 5 |
    | INNODB | 140311361760632:1063:140311280039632 | 421786338471288 | 48 | 22 | test | t2 | NULL | NULL | NULL | 140311280039632 | TABLE | IS | GRANTED | NULL |
    | INNODB | 140311361760632:2:4:2:140311280036640 | 421786338471288 | 48 | 22 | test | t2 | NULL | NULL | PRIMARY | 140311280036640 | RECORD | S,GAP | GRANTED | 5 |
    +——–+—————————————+———————–+———–+———-+—————+————-+—————-+——————-+————+———————–+———–+————————+————-+———–+<\/p>\n
    —<\/p>\n
    create table t2(id int primary key, value int, index ix_t2_value(value));
    insert into t2 values (5,10),(10,5);<\/p>\n
    — session 1
    start transaction;
    select * from t2 where value = 3 for share;<\/p>\n
    — session 2
    start transaction;
    insert into t2 values (8, 2); — \u7b49\u5f85\u83b7\u53d6 ix_t2_value \u7d22\u5f15\u4e0a\u7684\u63d2\u5165\u610f\u5411\u9501(-\u221e, (5, 10))<\/p>\n
    select * from performance_schema.data_locks \u7684\u8f93\u51fa\u4e3a\uff1a<\/p>\n
    +——–+—————————————+———————–+———–+———-+—————+————-+—————-+——————-+————-+———————–+———–+————————+————-+———–+
    | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
    +——–+—————————————+———————–+———–+———-+—————+————-+—————-+——————-+————-+———————–+———–+————————+————-+———–+
    | INNODB | 140311361761480:1063:140311280045776 | 2073 | 50 | 18 | test | t2 | NULL | NULL | NULL | 140311280045776 | TABLE | IX | GRANTED | NULL |
    | INNODB | 140311361761480:2:5:3:140311280042864 | 2073 | 50 | 18 | test | t2 | NULL | NULL | ix_t2_value | 140311280042864 | RECORD | X,GAP,INSERT_INTENTION | WAITING | 5, 10 |
    | INNODB | 140311361760632:1063:140311280039632 | 421786338471288 | 48 | 26 | test | t2 | NULL | NULL | NULL | 140311280039632 | TABLE | IS | GRANTED | NULL |
    | INNODB | 140311361760632:2:5:3:140311280036640 | 421786338471288 | 48 | 26 | test | t2 | NULL | NULL | ix_t2_value | 140311280036640 | RECORD | S,GAP | GRANTED | 5, 10 |
    +——–+—————————————+———————–+———–+———-+—————+————-+—————-+——————-+————-+———————–+———–+————————+————-+———–+                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : izgnod <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 @JasonLaw \u4e4b\u524d\u9047\u5230\u8fc7\u7c7b\u4f3c\u95ee\u9898\uff0c\u6211\u56de\u7b54\u7684\u4e5f\u6709\u4e9b\u6a21\u7cca\uff0c\u665a\u70b9\u6211\u8bd5\u4e00\u4e0b\uff0c\u5e0c\u671b\u5e2e\u5230\u4f60                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u4e3b<\/span> \u8cc7\u6df1\u5927\u4f6c : JasonLaw <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 @izgnod #3 \u597d\u7684\uff0c\u671f\u5f85\u4f60\u7684\u56de\u590d\u3002                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n","protected":false},"excerpt":{"rendered":"
    MySQL InnoDB \u662f\u5426\u5728\u6210…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[],"tags":[],"_links":{"self":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts\/135729"}],"collection":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=135729"}],"version-history":[{"count":0,"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts\/135729\/revisions"}],"wp:attachment":[{"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=135729"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=135729"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=135729"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}