{"id":10006987,"date":"2022-12-18T15:32:40","date_gmt":"2022-12-18T07:32:40","guid":{"rendered":"https:\/\/ityet.com\/?p=10006987"},"modified":"2022-12-18T15:32:40","modified_gmt":"2022-12-18T07:32:40","slug":"%e4%bb%80%e4%b9%88%e6%98%afmysql%e6%8f%92%e5%85%a5%e6%84%8f%e5%90%91%e9%94%81%ef%bc%9f","status":"publish","type":"post","link":"https:\/\/itmax.cn\/?p=10006987","title":{"rendered":"\u4ec0\u4e48\u662fMySQL\u63d2\u5165\u610f\u5411\u9501\uff1f"},"content":{"rendered":"

Insert Intention Lock\uff0c\u4e2d\u6587\u6211\u4eec\u4e5f\u79f0\u4e4b\u4e3a\u63d2\u5165\u610f\u5411\u9501\u3002<\/p>\n

\u8fd9\u4e2a\u53ef\u4ee5\u7b97\u662f\u5bf9\u6211\u4eec\u4e4b\u524d\u6240\u8bb2\u7684 Gap Lock \u7684\u4e00\u4e2a\u8865\u5145\uff0c\u5173\u4e8e Gap Lock\uff0c\u5982\u679c\u8fd8\u6709\u5c0f\u4f19\u4f34\u4e0d\u61c2\uff0c\u53ef\u4ee5\u53c2\u8003\uff1a\u8bb0\u5f55\u9501\u3001\u95f4\u9699\u9501\u4e0e Next-Key Locks<\/a>\u3002<\/p>\n

1. \u4e3a\u4ec0\u4e48\u9700\u8981\u63d2\u5165\u610f\u5411\u9501<\/h2>\n

\u6211\u4eec\u4e4b\u524d\u5df2\u7ecf\u6709 Gap Lock \u4e86\uff0cGap Lock \u53ef\u4ee5\u5e2e\u6211\u4eec\u5728\u4e00\u5b9a\u7a0b\u5ea6\u4e0a\u89e3\u51b3\u5e7b\u8bfb\u95ee\u9898\uff0c\u4f46\u662f\uff0c\u4e4b\u524d\u7684\u4f3c\u4e4e\u6709\u70b9\u95ee\u9898\u3002<\/p>\n

\u5047\u8bbe\u6211\u6709\u5982\u4e0b\u4e00\u5f20\u8868\uff1a<\/p>\n

CREATE TABLE `user` (\n  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,\n  `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,\n  `age` int(11) NOT NULL,\n  PRIMARY KEY (`id`),\n  KEY `age` (`age`)\n) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;<\/code><\/pre>\n
id \u662f\u4e3b\u952e\u81ea\u589e\uff1bage \u662f\u4e00\u4e2a\u666e\u901a\u7d22\u5f15\uff0c\u73b0\u5728\u8868\u4e2d\u6709\u5982\u4e0b\u6570\u636e\uff1a<\/p>\n
\"\"<\/p>\n
\u5047\u8bbe\u6211\u60f3\u6267\u884c\u5982\u4e0b\u7684\u63d2\u5165 SQL\uff1a<\/p>\n
begin;\ninsert into user(username,age) values('wangwu',95);<\/code><\/pre>\n
\u6ce8\u610f\uff0c\u8fd9\u4e2a SQL \u6267\u884c\u4e86\u4f46\u662f\u4e8b\u52a1\u8fd8\u6ca1\u6709\u63d0\u4ea4\u3002<\/p>\n
\u6309\u7167\u6211\u4eec\u4e4b\u524d\u5b66\u4e60\u7684\u5173\u4e8e Gap Lock \u7684\u77e5\u8bc6\u5206\u6790\u4e00\u4e0b\uff0c\u6b64\u65f6\u95f4\u9699\u9501\u7684\u8303\u56f4\u662f (89,99)\uff0c\u610f\u601d\u662f\u8fd9\u4e2a\u8303\u56f4\u7684 age \u90fd\u4e0d\u53ef\u4ee5\u63d2\u5165\u3002<\/p>\n
\u5982\u679c\u662f\u8fd9\u6837\u7684\u8bdd\uff0c\u5c0f\u4f19\u4f34\u4eec\u4f1a\u53d1\u73b0\u6570\u636e\u63d2\u5165\u7684\u6548\u7387\u53ef\u5c31\u592a\u4f4e\u4e86\uff0c\u5f88\u5bb9\u6613\u53d1\u751f\u9501\u51b2\u7a81\uff0c\u90a3\u4e48\u600e\u4e48\u529e\uff1f<\/p>\n
\u6211\u4eec\u4eca\u5929\u8981\u4ecb\u7ecd\u7684\u63d2\u5165\u610f\u5411\u9501\u5c31\u662f\u7528\u6765\u89e3\u51b3\u8fd9\u4e2a\u95ee\u9898\u7684\u3002<\/p>\n
2. \u4ec0\u4e48\u662f\u63d2\u5165\u610f\u5411\u9501<\/h2>\n
\u6211\u4eec\u6765\u770b\u770b MySQL \u5b98\u7f51\u7684\u4ecb\u7ecd\uff1a<\/p>\n
An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.<\/p><\/blockquote>\n
\u5927\u81f4\u7ffb\u8bd1\u4e0b\u4e00\u4e0b\u5c31\u662f\u8fd9\u6837\uff1a<\/p>\n
\u63d2\u5165\u610f\u5411\u9501\u662f\u4e00\u79cd\u5728 INSERT \u64cd\u4f5c\u4e4b\u524d\u8bbe\u7f6e\u7684\u4e00\u79cd\u95f4\u9699\u9501\uff0c\u63d2\u5165\u610f\u5411\u9501\u8868\u793a\u4e86\u4e00\u79cd\u63d2\u5165\u610f\u56fe\uff0c\u5373\u5f53\u591a\u4e2a\u4e0d\u540c\u7684\u4e8b\u52a1\uff0c\u540c\u65f6\u5f80\u540c\u4e00\u4e2a\u7d22\u5f15\u7684\u540c\u4e00\u4e2a\u95f4\u9699\u4e2d\u63d2\u5165\u6570\u636e\u7684\u65f6\u5019\uff0c\u5b83\u4eec\u4e92\u76f8\u4e4b\u95f4\u65e0\u9700\u7b49\u5f85\uff0c\u5373\u4e0d\u4f1a\u963b\u585e\uff08\u8981\u662f\u5355\u7eaf\u6309\u7167\u4e4b\u524d\u95f4\u9699\u9501\u7684\u7406\u8bba\uff0c\u5fc5\u987b\u8981\u7b49\u4e00\u4e2a\u95f4\u9699\u9501\u91ca\u653e\u4e86\uff0c\u4e0b\u4e00\u4e2a\u4e8b\u52a1\u624d\u53ef\u4ee5\u5f80\u76f8\u540c\u7684\u95f4\u9699\u5904\u63d2\u5165\u6570\u636e\uff09\u3002\u5047\u8bbe\u6709\u503c\u4e3a 4 \u548c 7 \u7684\u7d22\u5f15\u8bb0\u5f55\uff0c\u73b0\u5728\u6709\u4e24\u4e2a\u4e8b\u52a1\uff0c\u5206\u522b\u5c1d\u8bd5\u63d2\u5165\u503c\u4e3a 5 \u548c 6 \u7684\u8bb0\u5f55\uff0c\u5728\u83b7\u5f97\u63d2\u5165\u884c\u7684\u6392\u4ed6\u9501\u4e4b\u524d\uff0c\u6bcf\u4e2a\u4e8b\u52a1\u4f7f\u7528\u63d2\u5165\u610f\u5411\u9501\u9501\u5b9a 4 \u548c 7 \u4e4b\u95f4\u7684\u95f4\u9699\uff0c\u4f46\u662f\u8fd9\u4e24\u4e2a\u4e8b\u52a1\u4e0d\u4f1a\u76f8\u4e92\u963b\u585e\uff0c\u56e0\u4e3a\u884c\u662f\u4e0d\u51b2\u7a81\u7684\u3002<\/p>\n
\u8fd9\u5c31\u662f\u63d2\u5165\u610f\u5411\u9501\u3002<\/p>\n
3. \u5b9e\u8df5<\/h2>\n
\u5c0f\u4f19\u4f34\u4eec\u6ce8\u610f\uff0c\u677e\u54e5\u4e4b\u524d\u548c\u5927\u5bb6\u804a Gap Lock\uff0c\u8bf4\u8fc7\u8fd9\u4e2a\u662f\u53ef\u91cd\u590d\u8bfb\uff08REPEATABLE READ\uff09\u8fd9\u4e2a\u9694\u79bb\u7ea7\u522b\u4e0b\u7279\u6709\u7684\u4ea7\u7269\uff0c\u90a3\u4e48\u73b0\u5728 Insert Intention Lock \u662f\u4e00\u79cd\u7279\u6b8a\u7684 Gap Lock\uff0c\u5f53\u7136\u4e5f\u662f\u5728\u53ef\u91cd\u590d\u8bfb\u8fd9\u4e2a\u9694\u79bb\u7ea7\u522b\u4e0b\u751f\u6548\u3002<\/p>\n
\u63a5\u4e0b\u6765\u6211\u4eec\u901a\u8fc7\u4e24\u4e2a\u4e2a\u7b80\u5355\u7684\u6848\u4f8b\u6765\u6f14\u793a\u4e00\u4e0b\u63d2\u5165\u610f\u5411\u9501\u3002<\/p>\n
3.1 \u6848\u4f8b\u4e00<\/h3>\n
\u6211\u4eec\u7684\u8868\u7ed3\u6784\u4ee5\u53ca\u6570\u636e\u548c\u7b2c\u4e00\u5c0f\u8282\u4e00\u81f4\u3002<\/p>\n
\u9996\u5148\u6211\u4eec\u5728\u4f1a\u8bdd A \u4e2d\uff0c\u6267\u884c\u5982\u4e0b\u4ee3\u7801\uff1a<\/p>\n
\"\"<\/p>\n
\u73b0\u5728\u4f1a\u8bdd A \u4e2d\u7684\u4e8b\u52a1\u6ca1\u6709\u63d0\u4ea4\u3002<\/p>\n
\u63a5\u4e0b\u6765\u6211\u4eec\u5728\u4f1a\u8bdd B \u4e2d\uff0c\u4e5f\u6267\u884c\u4e00\u4e2a\u63d2\u5165\u64cd\u4f5c\uff1a<\/p>\n
\"\"<\/p>\n
\u6211\u4eec\u53d1\u73b0\u4f1a\u8bdd B \u4e5f\u53ef\u4ee5\u6b63\u5e38\u6267\u884c\uff0c\u6ca1\u6709\u53d1\u751f\u963b\u585e\u3002<\/p>\n
\u8fd9\u8bf4\u660e\uff0c\u4e24\u4e2a\u63d2\u5165\u610f\u5411\u9501\u4e4b\u95f4\u662f\u517c\u5bb9\u7684\uff0c\u53ef\u4ee5\u5171\u5b58\u7684\u3002<\/p>\n
3.2 \u6848\u4f8b\u4e8c<\/h3>\n
\u6211\u4eec\u518d\u6765\u770b\u4e00\u4e2a\u4e0d\u517c\u5bb9\u7684\u4f8b\u5b50\u3002<\/p>\n
\u9996\u5148\u5728\u4f1a\u8bdd A \u4e2d\u6267\u884c\u5982\u4e0b SQL \u67e5\u8be2 age \u5927\u4e8e 80 \u7684\u8bb0\u5f55\uff0c\u5e76\u6dfb\u52a0\u6392\u4ed6\u9501\uff1a<\/p>\n
\"\"<\/p>\n
\u63a5\u4e0b\u6765\u5728\u4f1a\u8bdd B \u4e2d\uff0c\u6267\u884c\u5982\u4e0b\u4ee3\u7801\u63d2\u5165\u4e00\u884c\u6570\u636e\uff1a<\/p>\n
\"\"<\/p>\n
\u5c0f\u4f19\u4f34\u4eec\u770b\u5230\uff0c\u8fd9\u4e2a\u64cd\u4f5c\u4f1a\u88ab\u963b\u585e\uff01\u963b\u585e\u7684\u539f\u56e0\u5728\u4e8e\uff0c\u63d2\u5165\u610f\u5411\u9501\u548c\u6392\u4ed6\u9501\u4e4b\u95f4\u662f\u4e92\u65a5\u7684\u3002<\/p>\n
\u8d81\u7740\u53d1\u751f\u963b\u585e\u7684\u8fd9\u4f1a\uff0c\u5728\u4f1a\u8bdd C \u4e2d\uff0c\u6211\u4eec\u901a\u8fc7\u5728\u524d\u9762\u6587\u7ae0\u4e2d\u6240\u4f7f\u7528\u7684 show engine innodb status\/G<\/code> \u6307\u4ee4\uff0c\u6765\u67e5\u770b\u4e0b\u52a0\u9501\u7684\u60c5\u51b5\uff0c\u91cd\u70b9\u770b TRANSACTION \u8282\u70b9\uff1a<\/p>\n
\"\"<\/p>\n
\u5728\u8f93\u51fa\u7684\u5185\u5bb9\u4e2d\uff0c\u7ea2\u8272\u6846\u9009\u4e2d\u7684\u5730\u65b9\uff0c\u6e05\u695a\u7684\u8868\u660e\u4e86\u63d2\u5165\u610f\u5411\u9501\u7684\u5b58\u5728\u3002<\/p>\n
4. \u5c0f\u7ed3<\/h2>\n
\u603b\u7ed3\u4e00\u4e0b\uff1a<\/p>\n
    \n
  1. \u63d2\u5165\u610f\u5411\u9501\u867d\u7136\u540d\u5b57\u4e2d\u6709\u610f\u5411\u4e8c\u5b57\uff0c\u4f46\u5b9e\u9645\u4e0a\u662f\u4e00\u4e2a\u7279\u6b8a\u7684\u95f4\u9699\u9501\u3002<\/li>\n
  2. \u63d2\u5165\u610f\u5411\u9501\u4e4b\u95f4\u4e0d\u4e92\u65a5\u3002<\/li>\n
  3. \u63d2\u5165\u610f\u5411\u9501\u548c\u6392\u4ed6\u9501\u4e4b\u95f4\u4e92\u65a5\u3002<\/li>\n<\/ol>\n
    \u597d\u5566\uff0c\u6709\u95ee\u9898\u6b22\u8fce\u7559\u8a00\u8ba8\u8bba\u3002<\/p>\n
    https:\/\/segmentfault.com\/a\/1190000043102058<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
    Insert Intention Lock\uff0c\u4e2d\u6587\u6211\u4eec\u4e5f\u79f0\u4e4b\u4e3a\u63d2\u5165\u610f\u5411\u9501\u3002 \u8fd9\u4e2a\u53ef\u4ee5\u7b97\u662f\u5bf9\u6211\u4eec\u4e4b\u524d\u6240\u8bb2\u7684 Gap […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4],"tags":[],"class_list":["post-10006987","post","type-post","status-publish","format-standard","hentry","category-share"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts\/10006987","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=10006987"}],"version-history":[{"count":0,"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts\/10006987\/revisions"}],"wp:attachment":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=10006987"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=10006987"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=10006987"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}