{"id":10008339,"date":"2023-09-26T23:57:27","date_gmt":"2023-09-26T15:57:27","guid":{"rendered":"https:\/\/blog.itmax.cn\/?p=10008339"},"modified":"2023-09-26T23:57:27","modified_gmt":"2023-09-26T15:57:27","slug":"mysql%e4%ba%8b%e5%8a%a1%e6%ad%bb%e9%94%81%e9%97%ae%e9%a2%98%e6%8e%92%e6%9f%a5-%e4%ba%ac%e4%b8%9c%e4%ba%91%e6%8a%80%e6%9c%af%e5%9b%a2%e9%98%9f","status":"publish","type":"post","link":"https:\/\/itmax.cn\/?p=10008339","title":{"rendered":"MySQL\u4e8b\u52a1\u6b7b\u9501\u95ee\u9898\u6392\u67e5 | \u4eac\u4e1c\u4e91\u6280\u672f\u56e2\u961f"},"content":{"rendered":"<h2>\u4e00\u3001\u80cc\u666f<\/h2>\n<p>\u5728\u9884\u53d1\u73af\u5883\u4e2d\uff0c\u7531\u6d88\u606f\u9a71\u52a8\u6700\u7ec8\u89e6\u53d1\u6267\u884c\u4e8b\u52a1\u6765\u5199\u5e93\u5b58\uff0c\u4f46\u662f\u5bfc\u81f4MySQL\u53d1\u751f\u6b7b\u9501\uff0c\u5199\u5e93\u5b58\u5931\u8d25\u3002<\/p>\n<pre><code>com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: rpc error: code = Aborted desc = Deadlock found when trying to get lock; try restarting transaction (errno 1213) (sqlstate 40001) (CallerID: ): Sql: &quot;\/* uag::omni_stock_rw;xx.xx.xx.xx:xxxxx;xx.xx.xx.xx:xxxxx;xx.xx.xx.xx:xxxxx;enable *\/  insert into stock_info(tenant_id, sku_id, store_id, available_num, actual_good_num, order_num, created, modified, SAVE_VERSION, stock_id) values (:vtg1, :vtg2, :_store_id0, :vtg4, :vtg5, :vtg6, now(), now(), :vtg7, :__seq0) \/* vtgate:: keyspace_id:e267ed155be60efe *\/&quot;, BindVars: {__seq0: &quot;type:INT64 value:&quot;29332459&quot; &quot;_store_id0: &quot;type:INT64 value:&quot;50650235&quot; &quot;vtg1: &quot;type:INT64 value:&quot;71&quot; &quot;vtg2: &quot;type:INT64 value:&quot;113817631&quot; &quot;vtg3: &quot;type:INT64 value:&quot;50650235&quot; &quot;vtg4: &quot;type:FLOAT64 value:&quot;1000.000&quot; &quot;vtg5: &quot;type:FLOAT64 value:&quot;1000.000&quot; &quot;vtg6: &quot;type:INT64 value:&quot;0&quot; &quot;vtg7: &quot;type:INT64 value:&quot;20937611645&quot; &quot;}<\/code><\/pre>\n<p>\u521d\u6b65\u6392\u67e5\uff0c\u5728\u540c\u4e00\u65f6\u523b\u6709\u4e24\u6761\u8bf7\u6c42\u8fdb\u884c\u5199\u5e93\u5b58\u7684\u64cd\u4f5c\u3002<\/p>\n<p><img decoding=\"async\"   referrerpolicy=\"no-referrer\" src=\"https:\/\/segmentfault.com\/img\/remote\/1460000044261892\" alt=\"\" title=\"\"><\/p>\n<p>\u65f6\u95f4\u524d\u540e\u76f8\u5dee1s\uff0c\u4f46\u6700\u7ec8\u6267\u884c\u7ed3\u679c\u662f\uff0c\u8fd9\u4e24\u4e2a\u4e8b\u52a1\u76f8\u4e92\u6b7b\u9501\uff0c\u5747\u5931\u8d25\u3002<\/p>\n<p>\u4e8b\u52a1\u5b9a\u4e49\u975e\u5e38\u7b80\u5355\uff0c\u4f2a\u4ee3\u7801\u63cf\u8ff0\u5982\u4e0b\uff1a<\/p>\n<pre><code>start transaction\n\/\/ 1\u3001\u67e5\u8be2\u6570\u636e\ndata = select for update(tenantId, storeId, skuId);\nif (data == null) {\n    \/\/ \u63d2\u5165\u6570\u636e\n    insert(tenantId, storeId, skuId);\n} else {\n    \/\/ \u66f4\u65b0\u6570\u636e\n    update(tenantId, storeId, skuId);\n}\nend transaction<\/code><\/pre>\n<p>\u8be5\u6570\u636e\u5e93\u8868\u7684\u7d22\u5f15\u7ed3\u6784\u5982\u4e0b\uff1a<\/p>\n<table>\n<thead>\n<tr>\n<th><strong>\u7d22\u5f15\u7c7b\u578b<\/strong><\/th>\n<th><strong>\u7d22\u5f15\u7ec4\u6210\u5217<\/strong><\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>PRIMARY KEY<\/td>\n<td>(<code>stock_id<\/code>)<\/td>\n<\/tr>\n<tr>\n<td>UNIQUE KEY<\/td>\n<td>(<code>sku_id<\/code>,<code>store_id<\/code>)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u6240\u4f7f\u7528\u7684\u6570\u636e\u5e93\u5f15\u64ce\u4e3aInnodb\uff0c\u9694\u79bb\u7ea7\u522b\u4e3aRR[Repeatable Read]\u53ef\u91cd\u590d\u8bfb\u3002<\/p>\n<h2>\u4e8c\u3001\u5206\u6790\u601d\u8def<\/h2>\n<p>\u9996\u5148\u4e86\u89e3\u4e0bInnodb\u5f15\u64ce\u4e2d\u6709\u5173\u4e8e\u9501\u7684\u5185\u5bb9<\/p>\n<h3>2.1 Innodb\u4e2d\u7684\u9501<\/h3>\n<h4>2.1.1 \u884c\u7ea7\u9501<\/h4>\n<p>\u5728Innodb\u5f15\u64ce\u4e2d\uff0c\u884c\u7ea7\u9501\u7684\u5b9e\u73b0\u65b9\u5f0f\u6709\u4ee5\u4e0b\u4e09\u79cd\uff1a<\/p>\n<table>\n<thead>\n<tr>\n<th><strong>\u540d\u79f0<\/strong><\/th>\n<th><strong>\u63cf\u8ff0<\/strong><\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>Record Lock<\/td>\n<td>\u9501\u5b9a\u5355\u884c\u8bb0\u5f55\uff0c\u5728\u9694\u79bb\u7ea7\u522bRC\u548cRR\u4e0b\u5747\u652f\u6301\u3002<\/td>\n<\/tr>\n<tr>\n<td>Gap Lock<\/td>\n<td>\u95f4\u9699\u9501\uff0c\u9501\u5b9a\u7d22\u5f15\u8bb0\u5f55\u95f4\u9699\uff08\u4e0d\u5305\u542b\u67e5\u8be2\u7684\u8bb0\u5f55\uff09\uff0c\u9501\u5b9a\u533a\u95f4\u4e3a\u5de6\u5f00\u53f3\u5f00\uff0c\u4ec5\u5728RR\u9694\u79bb\u7ea7\u522b\u4e0b\u652f\u6301\u3002<\/td>\n<\/tr>\n<tr>\n<td>Next-Key Lock<\/td>\n<td>\u4e34\u952e\u9501\uff0c\u9501\u5b9a\u67e5\u8be2\u8bb0\u5f55\u6240\u5728\u884c\uff0c\u540c\u65f6\u9501\u5b9a\u524d\u9762\u7684\u533a\u95f4\uff0c\u6545\u533a\u95f4\u4e3a\u5de6\u5f00\u53f3\u95ed\uff0c\u4ec5\u5728RR\u9694\u79bb\u7ea7\u522b\u4e0b\u652f\u6301\u3002<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u540c\u65f6\uff0c\u5728Innodb\u4e2d\u5b9e\u73b0\u4e86\u6807\u51c6\u7684\u884c\u9501\uff0c\u6309\u7167\u9501\u5b9a\u7c7b\u578b\u53c8\u53ef\u5206\u4e3a\u4e24\u7c7b\uff1a<\/p>\n<table>\n<thead>\n<tr>\n<th><strong>\u540d\u79f0<\/strong><\/th>\n<th><strong>\u7b26\u53f7<\/strong><\/th>\n<th><strong>\u63cf\u8ff0<\/strong><\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>\u5171\u4eab\u9501<\/td>\n<td>S<\/td>\n<td>\u5141\u8bb8\u4e8b\u52a1\u8bfb\u4e00\u884c\u6570\u636e\uff0c\u963b\u6b62\u5176\u4ed6\u4e8b\u52a1\u83b7\u5f97\u76f8\u540c\u7684\u6570\u636e\u96c6\u7684\u6392\u4ed6\u9501\u3002<\/td>\n<\/tr>\n<tr>\n<td>\u6392\u4ed6\u9501<\/td>\n<td>X<\/td>\n<td>\u5141\u8bb8\u4e8b\u52a1\u5220\u9664\u6216\u66f4\u65b0\u4e00\u884c\u6570\u636e\uff0c\u963b\u6b62\u5176\u4ed6\u4e8b\u52a1\u83b7\u5f97\u76f8\u540c\u6570\u636e\u96c6\u7684\u5171\u4eab\u9501\u548c\u6392\u4ed6\u9501\u3002<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u7b80\u8a00\u4e4b\uff0c\u5f53\u67d0\u4e2a\u4e8b\u7269\u83b7\u53d6\u4e86\u5171\u4eab\u9501\u540e\uff0c\u5176\u4ed6\u4e8b\u7269\u53ea\u80fd\u83b7\u53d6\u5171\u4eab\u9501\uff0c\u82e5\u60f3\u83b7\u53d6\u6392\u4ed6\u9501\uff0c\u5fc5\u987b\u8981\u7b49\u5f85\u5171\u4eab\u9501\u91ca\u653e\uff1b\u82e5\u67d0\u4e2a\u4e8b\u7269\u83b7\u53d6\u4e86\u6392\u4ed6\u9501\uff0c\u5219\u5176\u4f59\u4e8b\u7269\u65e0\u8bba\u83b7\u53d6\u5171\u4eab\u9501\u8fd8\u662f\u6392\u4ed6\u9501\uff0c\u90fd\u9700\u8981\u7b49\u5f85\u6392\u4ed6\u9501\u91ca\u653e\u3002\u5982\u4e0b\u8868\u6240\u793a\uff1a<\/p>\n<table>\n<thead>\n<tr>\n<th><strong>\u5c06\u83b7\u53d6\u7684\u9501\uff08\u4e0b\uff09\/\u5df2\u83b7\u53d6\u7684\u9501\uff08\u53f3\uff09<\/strong><\/th>\n<th><strong>\u5171\u4eab\u9501S<\/strong><\/th>\n<th><strong>\u6392\u4ed6\u9501X<\/strong><\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>\u5171\u4eab\u9501S<\/td>\n<td>\u517c\u5bb9<\/td>\n<td>\u4e0d\u517c\u5bb9<\/td>\n<\/tr>\n<tr>\n<td>\u6392\u4ed6\u9501X<\/td>\n<td>\u4e0d\u517c\u5bb9<\/td>\n<td>\u4e0d\u517c\u5bb9<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4>2.1.2 RR\u9694\u79bb\u7ea7\u522b\u4e0b\u52a0\u9501\u793a\u4f8b<\/h4>\n<p>\u5047\u5982\u73b0\u5728\u6709\u8fd9\u6837\u4e00\u5f20\u8868user\uff0c\u4e0b\u9762\u5c06\u9488\u5bf9\u4e0d\u540c\u7684\u67e5\u8be2\u8bf7\u6c42\u9010\u4e00\u5206\u6790\u52a0\u9501\u60c5\u51b5\u3002user\u8868\u5b9a\u4e49\u5982\u4e0b\uff1a<\/p>\n<pre><code>CREATE TABLE `user` (\n  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '\u4e3b\u952eID',\n  `user_id` bigint(20) DEFAULT NULL COMMENT '\u7528\u6237id',\n  `mobile_num` bigint(20) NOT NULL COMMENT '\u624b\u673a\u53f7',\n  PRIMARY KEY (`id`),\n  UNIQUE KEY `IDX_USER_ID` (`user_id`),\n  KEY `IDX_MOBILE_NUM` (`mobile_num`)  \n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='\u7528\u6237\u4fe1\u606f\u8868'<\/code><\/pre>\n<p>\u5176\u4e2d\u4e3b\u952eid\u4e0euser_id\u4e3a\u552f\u4e00\u7d22\u5f15\uff0cuser_name\u4e3a\u666e\u901a\u7d22\u5f15\u3002<\/p>\n<p>\u5047\u8bbe\u8be5\u8868\u4e2d\u73b0\u6709\u6570\u636e\u5982\u4e0b\u6240\u793a\uff1a<\/p>\n<table>\n<thead>\n<tr>\n<th><strong>id<\/strong><\/th>\n<th><strong>user_id<\/strong><\/th>\n<th><strong>mobile_num<\/strong><\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>1<\/td>\n<td>1<\/td>\n<td>3<\/td>\n<\/tr>\n<tr>\n<td>5<\/td>\n<td>5<\/td>\n<td>6<\/td>\n<\/tr>\n<tr>\n<td>8<\/td>\n<td>8<\/td>\n<td>7<\/td>\n<\/tr>\n<tr>\n<td>9<\/td>\n<td>9<\/td>\n<td>9<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u4e0b\u9762\u5c06\u4f7f\u7528select &#8230; for update \u8bed\u53e5\u8fdb\u884c\u67e5\u8be2\uff0c\u5206\u522b\u9488\u5bf9\u552f\u4e00\u7d22\u5f15\u3001\u666e\u901a\u7d22\u5f15\u6765\u8fdb\u884c\u4e3e\u4f8b\u3002<\/p>\n<h5>1\u3001\u552f\u4e00\u7d22\u5f15\u7b49\u503c\u67e5\u8be2<\/h5>\n<pre><code>select * from user\nwhere id = 5 for update<\/code><\/pre>\n<pre><code>select * from user\nwhere user_id = 5 for update<\/code><\/pre>\n<p>\u5728\u8fd9\u4e24\u6761SQL\u4e2d\uff0cInnodb\u6267\u884c\u67e5\u8be2\u8fc7\u7a0b\u65f6\uff0c\u4f1a\u5982\u4f55\u52a0\u9501\u5462\uff1f<\/p>\n<p>\u6211\u4eec\u90fd\u77e5\u9053Innodb\u9ed8\u8ba4\u7684\u7d22\u5f15\u6570\u636e\u7ed3\u6784\u4e3aB+\u6811\uff0cB+\u6811\u7684\u53f6\u5b50\u7ed3\u70b9\u5305\u542b\u6307\u5411\u4e0b\u4e00\u4e2a\u53f6\u5b50\u7ed3\u70b9\u7684\u6307\u9488\u3002\u5728\u67e5\u8be2\u8fc7\u7a0b\u4e2d\uff0c\u4f1a\u6309\u7167B+\u6811\u7684\u641c\u7d22\u65b9\u5f0f\u6765\u8fdb\u884c\u67e5\u627e\uff0c\u5176\u5e95\u5c42\u539f\u7406\u7c7b\u4f3c\u4e8c\u5206\u67e5\u627e\u3002\u6545\u5728\u52a0\u9501\u8fc7\u7a0b\u4e2d\u4f1a\u6309\u7167\u4ee5\u4e0b\u4e24\u6761\u539f\u5219\u8fdb\u884c\u52a0\u9501\uff1a<\/p>\n<p>1.\u53ea\u4f1a\u5bf9\u6ee1\u8db3\u67e5\u8be2\u76ee\u6807\u9644\u8fd1\u7684\u533a\u95f4\u52a0\u9501\uff0c\u5e76\u4e0d\u662f\u5bf9\u641c\u7d22\u8def\u5f84\u4e2d\u7684\u6240\u6709\u533a\u95f4\u90fd\u52a0\u9501\u3002\u672c\u4f8b\u4e2d\u5bf9\u641c\u7d22id=5\u6216\u8005user_id=5\u65f6\uff0c\u6700\u7ec8\u53ef\u4ee5\u5b9a\u4f4d\u5230\u6ee1\u8db3\u8be5\u641c\u7d22\u6761\u4ef6\u7684\u533a\u57df(1,5]\u3002<\/p>\n<p>2.\u52a0\u9501\u65f6\uff0c\u4f1a\u4ee5Next key Lock\u4e3a\u52a0\u9501\u5355\u4f4d\u3002\u90a3\u6309\u71671\u6ee1\u8db3\u7684\u533a\u57df\u8fdb\u884c\u52a0Next key Lock\u9501\uff08\u5de6\u5f00\u53f3\u95ed\uff09\uff0c\u540c\u65f6\u56e0\u4e3aid=5\u6216\u8005user_id=5\u5b58\u5728\uff0c\u6240\u4ee5\u8be5Next key Lock\u4f1a\u9000\u5316\u4e3aRecord Lock\uff0c\u6545\u53ea\u5bf9id=5\u6216user_id=5\u8fd9\u4e2a\u7d22\u5f15\u884c\u52a0\u9501\u3002<\/p>\n<p>\u5982\u679c\u67e5\u8be2\u7684id\u4e0d\u5b58\u5728\uff0c\u4f8b\u5982\uff1a<\/p>\n<pre><code>select * from user\nwhere id = 6 for update<\/code><\/pre>\n<p>\u6309\u7167\u4e0a\u9762\u4e24\u6761\u539f\u5219\uff0c\u9996\u5148\u6309\u7167\u6ee1\u8db3\u67e5\u8be2\u76ee\u6807\u6761\u4ef6\u9644\u8fd1\u533a\u57df\u52a0\u9501\uff0c\u6240\u4ee5\u6700\u7ec8\u4f1a\u627e\u5230\u7684\u533a\u95f4\u4e3a(5,8]\u3002\u56e0\u4e3aid=6\u8fd9\u6761\u8bb0\u5f55\u5e76\u4e0d\u5b58\u5728\uff0c\u6240\u4ee5Next key Lock(5, 8]\u6700\u7ec8\u4f1a\u9000\u5316\u4e3aGap Lock\uff0c\u5373\u5bf9\u7d22\u5f15(5,8)\u52a0\u95f4\u9699\u9501\u3002<\/p>\n<h5>2\u3001\u552f\u4e00\u7d22\u5f15\u8303\u56f4\u67e5\u8be2<\/h5>\n<pre><code>select * from user\nwhere id &gt;= 4 and id &lt;8 for update<\/code><\/pre>\n<p>\u540c\u7406\uff0c\u5728\u8303\u56f4\u67e5\u8be2\u4e2d\uff0c\u4f1a\u9996\u5148\u5339\u914d\u5de6\u503cid=4\uff0c\u6b64\u65f6\u4f1a\u5bf9\u533a\u95f4(1,5]\u52a0Next key Lock\uff0c\u56e0\u4e3aid=4\u4e0d\u5b58\u5728\uff0c\u6240\u4ee5\u9501\u9000\u5316\u4e3a Gap Lock(1,5)\uff1b\u63a5\u7740\u4f1a\u5f80\u540e\u7ee7\u7eed\u67e5\u627eid=8\u7684\u8bb0\u5f55\uff0c\u76f4\u5230\u627e\u5230\u7b2c\u4e00\u4e2a\u4e0d\u6ee1\u8db3\u7684\u533a\u95f4\uff0c\u5373Next key Lock(8, 9]\uff0c\u56e0\u4e3a8\u4e0d\u5728\u8303\u56f4\u5185\uff0c\u6240\u4ee5\u9501\u9000\u5316\u4e3aGap Lock(8, 9)\u3002\u6545\u8be5\u8303\u56f4\u67e5\u8be2\u6700\u7ec8\u4f1a\u9501\u7684\u533a\u57df\u4e3a(1, 9)<\/p>\n<h5>3\u3001\u975e\u552f\u4e00\u7d22\u5f15\u7b49\u503c\u67e5\u8be2<\/h5>\n<p>\u5bf9\u975e\u552f\u4e00\u7d22\u5f15\u67e5\u8be2\u65f6\uff0c\u4e0e\u4e0a\u8ff0\u7684\u52a0\u9501\u65b9\u5f0f\u7a0d\u6709\u533a\u522b\u3002\u9664\u4e86\u8981\u5bf9\u5305\u542b\u67e5\u8be2\u503c\u533a\u95f4\u5185\u52a0Next key Lock\u4e4b\u5916\uff0c\u8fd8\u8981\u5bf9\u4e0d\u6ee1\u8db3\u67e5\u8be2\u6761\u4ef6\u7684\u4e0b\u4e00\u4e2a\u533a\u95f4\u52a0Gap Lock\uff0c\u4e5f\u5c31\u662f\u9700\u8981\u52a0\u4e24\u628a\u9501\u3002<\/p>\n<pre><code>select * from user\nwhere mobile_num = 6 for update<\/code><\/pre>\n<p>\u9700\u8981\u5bf9\u7d22\u5f15(3, 6]\u52a0Next key Lock\uff0c\u56e0\u4e3a\u6b64\u65f6\u662f\u975e\u552f\u4e00\u7d22\u5f15\uff0c\u90a3\u4e48\u4e5f\u5c31\u6709\u53ef\u80fd\u6709\u591a\u4e2a6\u5b58\u5728\uff0c\u6240\u4ee5\u6b64\u65f6\u4e0d\u4f1a\u9000\u5316\u4e3aRecord Lock\uff1b\u6b64\u5916\u8fd8\u8981\u5bf9\u4e0d\u6ee1\u8db3\u8be5\u67e5\u8be2\u6761\u4ef6\u7684\u4e0b\u4e00\u4e2a\u533a\u95f4\u52a0Gap Lock\uff0c\u4e5f\u5c31\u662f\u5bf9\u7d22\u5f15(6,7)\u52a0\u9501\u3002\u6545\u603b\u4f53\u6765\u770b\uff0c\u5bf9\u7d22\u5f15\u52a0\u4e86(3,6]Next key Lock\u548c(6, 7) Gap Lock\u3002<\/p>\n<p>\u82e5\u975e\u552f\u4e00\u7d22\u5f15\u4e0d\u547d\u4e2d\u65f6\uff0c\u5982\u4e0b\uff1a<\/p>\n<pre><code>select * from user \nwhere mobile_num = 8 for update<\/code><\/pre>\n<p>\u90a3\u4e48\u9700\u8981\u5bf9\u7d22\u5f15(7, 9]\u52a0Next key Lock\uff0c\u53c8\u56e0\u4e3a8\u4e0d\u5b58\u5728\uff0c\u6240\u4ee5\u9501\u9000\u5316\u4e3aGap Lock (7, 9)<\/p>\n<h5>4\u3001\u975e\u552f\u4e00\u7d22\u5f15\u8303\u56f4\u67e5\u8be2<\/h5>\n<pre><code>select * from user\nwhere mobile_num &gt;= 6 and mobile_num &lt; 8\nfor update <\/code><\/pre>\n<p>\u9996\u5148\u5148\u5339\u914dmobile_num=6\uff0c\u6b64\u65f6\u4f1a\u5bf9\u7d22\u5f15(3, 6]\u52a0Next Key Lock\uff0c\u867d\u7136\u6b64\u65f6\u975e\u552f\u4e00\u7d22\u5f15\u5b58\u5728\uff0c\u4f46\u662f\u4e0d\u4f1a\u9000\u5316\u4e3aRecord Lock\uff1b\u5176\u6b21\u518d\u770b\u540e\u534a\u90e8\u5206\u7684\u67e5\u8be2mobile_num=8\uff0c\u9700\u8981\u5bf9\u7d22\u5f15(7, 9]\u52a0Next key Lock\uff0c\u53c8\u56e0\u4e3a8\u4e0d\u5b58\u5728\uff0c\u6240\u4ee5\u9000\u5316\u4e3aGap Lock (7, 9)\u3002\u6700\u7ec8\uff0c\u9700\u8981\u5bf9\u7d22\u5f15\u884c\u52a0Next key Lock(3, 6] \u548c Gap Lock(7, 9)\u3002<\/p>\n<h4>2.1.3 \u610f\u5411\u9501\uff08Intention Locks\uff09<\/h4>\n<p>Innodb\u4e3a\u4e86\u652f\u6301\u591a\u7c92\u5ea6\u9501\u5b9a\uff0c\u5f15\u5165\u4e86\u610f\u5411\u9501\u3002\u610f\u5411\u9501\u662f\u4e00\u79cd\u8868\u7ea7\u9501\uff0c\u7528\u4e8e\u8868\u660e\u4e8b\u52a1\u5c06\u8981\u5bf9\u67d0\u5f20\u8868\u67d0\u884c\u6570\u636e\u64cd\u4f5c\u800c\u8fdb\u884c\u7684\u9501\u5b9a\u3002\u540c\u6837\uff0c\u610f\u5411\u9501\u4e5f\u5206\u4e3a\u7c7b\uff1a\u5171\u4eab\u610f\u5411\u9501\uff08IS\uff09\u548c\u6392\u4ed6\u610f\u5411\u9501\uff08IX\uff09\u3002<\/p>\n<table>\n<thead>\n<tr>\n<th><strong>\u540d\u79f0<\/strong><\/th>\n<th><strong>\u7b26\u53f7<\/strong><\/th>\n<th><strong>\u63cf\u8ff0<\/strong><\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>\u5171\u4eab\u610f\u5411\u9501<\/td>\n<td>IS<\/td>\n<td>\u8868\u660e\u4e8b\u52a1\u5c06\u8981\u5bf9\u8868\u7684\u4e2a\u522b\u884c\u8bbe\u7f6e\u5171\u4eab\u9501<\/td>\n<\/tr>\n<tr>\n<td>\u6392\u4ed6\u610f\u5411\u9501<\/td>\n<td>IX<\/td>\n<td>\u8868\u660e\u4e8b\u52a1\u5c06\u8981\u5bf9\u8868\u7684\u4e2a\u522b\u884c\u8bbe\u7f6e\u6392\u4ed6\u9501<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u4f8b\u5982select &#8230; lock in shared mode\u4f1a\u8bbe\u7f6e\u5171\u4eab\u610f\u5411\u9501IS\uff1bselect &#8230; for update\u4f1a\u8bbe\u7f6e\u6392\u4ed6\u610f\u5411\u9501IX<\/p>\n<p>\u8bbe\u7f6e\u610f\u5411\u9501\u65f6\u9700\u8981\u6309\u7167\u4ee5\u4e0b\u4e24\u6761\u539f\u5219\u8fdb\u884c\u8bbe\u7f6e\uff1a<\/p>\n<p>1.\u5f53\u4e8b\u52a1\u9700\u8981\u7533\u8bf7<strong>\u884c<\/strong>\u7684<strong>\u5171\u4eab\u9501S<\/strong>\u65f6\uff0c\u5fc5\u987b\u5148\u5bf9<strong>\u8868<\/strong>\u7533\u8bf7<strong>\u5171\u4eab\u610f\u5411IS\u9501<\/strong>\u6216\u66f4\u5f3a\u7684\u9501<\/p>\n<p>2.\u5f53\u4e8b\u52a1\u9700\u8981\u7533\u8bf7<strong>\u884c<\/strong>\u7684<strong>\u6392\u4ed6\u9501X<\/strong>\u65f6\uff0c\u5fc5\u987b\u5148\u5bf9<strong>\u8868<\/strong>\u7533\u8bf7<strong>\u6392\u4ed6\u610f\u5411IX\u9501<\/strong><\/p>\n<p>\u8868\u7ea7\u9501\u517c\u5bb9\u6027\u77e9\u9635\u5982\u4e0b\u8868\uff1a<\/p>\n<table>\n<thead>\n<tr>\n<th><strong>\u5c06\u83b7\u53d6\u7684\u9501\uff08\u4e0b\uff09\/\u5df2\u83b7\u53d6\u7684\u9501\uff08\u53f3\uff09<\/strong><\/th>\n<th><strong>X<\/strong><\/th>\n<th><strong>IX<\/strong><\/th>\n<th><strong>S<\/strong><\/th>\n<th><strong>IS<\/strong><\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>X<\/td>\n<td>\u51b2\u7a81<\/td>\n<td>\u51b2\u7a81<\/td>\n<td>\u51b2\u7a81<\/td>\n<td>\u51b2\u7a81<\/td>\n<\/tr>\n<tr>\n<td>IX<\/td>\n<td>\u51b2\u7a81<\/td>\n<td><strong>\u517c\u5bb9<\/strong><\/td>\n<td>\u51b2\u7a81<\/td>\n<td><strong>\u517c\u5bb9<\/strong><\/td>\n<\/tr>\n<tr>\n<td>S<\/td>\n<td>\u51b2\u7a81<\/td>\n<td>\u51b2\u7a81<\/td>\n<td><strong>\u517c\u5bb9<\/strong><\/td>\n<td><strong>\u517c\u5bb9<\/strong><\/td>\n<\/tr>\n<tr>\n<td>IS<\/td>\n<td>\u51b2\u7a81<\/td>\n<td><strong>\u517c\u5bb9<\/strong><\/td>\n<td><strong>\u517c\u5bb9<\/strong><\/td>\n<td><strong>\u517c\u5bb9<\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u5982\u679c\u8bf7\u6c42\u9501\u7684\u4e8b\u52a1\u4e0e\u73b0\u6709\u9501\u517c\u5bb9\uff0c\u5219\u4f1a\u5c06\u9501\u6388\u4e88\u8be5\u4e8b\u52a1\uff0c\u4f46\u5982\u679c\u4e0e\u73b0\u6709\u9501\u51b2\u7a81\uff0c\u5219\u4e0d\u4f1a\u6388\u4e88\u8be5\u4e8b\u52a1\u3002\u4e8b\u52a1\u7b49\u5f85\uff0c\u76f4\u5230\u51b2\u7a81\u7684\u73b0\u6709\u9501\u88ab\u91ca\u653e\u3002<\/p>\n<p>\u610f\u5411\u9501\u7684\u76ee\u7684\u5c31\u662f\u4e3a\u4e86\u8bf4\u660e\u4e8b\u52a1\u6b63\u5728\u5bf9\u8868\u7684\u4e00\u884c\u8fdb\u884c\u9501\u5b9a\uff0c\u6216\u5c06\u8981\u5bf9\u8868\u7684\u4e00\u884c\u8fdb\u884c\u9501\u5b9a\u3002\u5728\u610f\u5411\u9501\u6982\u5ff5\u4e2d\uff0c\u9664\u4e86\u5bf9\u5168\u8868\u52a0\u9501\u4f1a\u5bfc\u81f4\u610f\u5411\u9501\u963b\u585e\u5916\uff0c\u5176\u4f59\u60c5\u51b5\u610f\u5411\u9501\u5747\u4e0d\u4f1a\u963b\u585e\u4efb\u4f55\u8bf7\u6c42\uff01<\/p>\n<h4>2.1.4 \u63d2\u5165\u610f\u5411\u9501<\/h4>\n<p>\u63d2\u5165\u610f\u5411\u9501\u662f\u4e00\u79cd\u7279\u6b8a\u7684\u610f\u5411\u9501\uff0c\u540c\u65f6\u4e5f\u662f\u4e00\u79cd\u7279\u6b8a\u7684\u201cGap Lock\u201d\uff0c\u662f\u5728Insert\u64cd\u4f5c\u4e4b\u524d\u8bbe\u7f6e\u7684Gap Lock\u3002<\/p>\n<p>\u5982\u679c\u6b64\u65f6\u6709\u591a\u4e2a\u4e8b\u52a1\u6267\u884cinsert\u64cd\u4f5c\uff0c\u6070\u597d\u9700\u8981\u63d2\u5165\u7684\u4f4d\u7f6e\u90fd\u5728\u540c\u4e00\u4e2aGap Lock\u4e2d\uff0c\u4f46\u662f\u5e76\u4e0d\u662f\u5728Gap Lock\u7684\u540c\u4e00\u4e2a\u4f4d\u7f6e\u65f6\uff0c\u6b64\u65f6\u7684\u63d2\u5165\u610f\u5411\u9501\u5f7c\u6b64\u4e4b\u95f4\u4e0d\u4f1a\u963b\u585e\u3002<\/p>\n<h3>2.2 \u8fc7\u7a0b\u5206\u6790<\/h3>\n<p>\u56de\u5230\u672c\u6587\u7684\u95ee\u9898\u4e0a\u6765\uff0c\u672c\u6587\u4e2d\u6709\u4e24\u4e2a\u4e8b\u52a1\u6267\u884c\u540c\u6837\u7684\u52a8\u4f5c\uff0c\u5206\u522b\u4e3a\u5148\u6267\u884cselect &#8230; for update\u83b7\u53d6\u6392\u4ed6\u9501\uff0c\u5176\u6b21\u5224\u65ad\u82e5\u4e3a\u7a7a\uff0c\u5219\u6267\u884cinsert\u52a8\u4f5c\uff0c\u5426\u5219\u6267\u884cupdate\u52a8\u4f5c\u3002\u4f2a\u4ee3\u7801\u63cf\u8ff0\u5982\u4e0b\uff1a<\/p>\n<pre><code>start transaction\n\/\/ 1\u3001\u67e5\u8be2\u6570\u636e\ndata = select for update(tenantId, storeId, skuId);\nif (data == null) {\n    \/\/ \u63d2\u5165\u6570\u636e\n    insert(tenantId, storeId, skuId);\n} else {\n    \/\/ \u66f4\u65b0\u6570\u636e\n    update(tenantId, storeId, skuId);\n}\nend transaction<\/code><\/pre>\n<p>\u73b0\u5728\u5bf9\u8fd9\u4e24\u4e2a\u4e8b\u52a1\u6240\u6267\u884c\u7684\u52a8\u4f5c\u8fdb\u884c\u9010\u4e00\u5206\u6790\uff0c\u5982\u4e0b\u8868\u6240\u793a\uff1a<\/p>\n<table>\n<thead>\n<tr>\n<th>\u65f6\u95f4\u70b9<\/th>\n<th>\u4e8b\u52a1A<\/th>\n<th>\u4e8b\u52a1B<\/th>\n<th>\u6f5c\u5728\u52a8\u4f5c<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>1<\/td>\n<td>\u5f00\u59cb\u4e8b\u52a1<\/td>\n<td>\u5f00\u59cb\u4e8b\u52a1<\/td>\n<td><\/td>\n<\/tr>\n<tr>\n<td>2<\/td>\n<td>\u6267\u884cselect &#8230; for update\u64cd\u4f5c<\/td>\n<td><\/td>\n<td>\u4e8b\u52a1A\u7533\u8bf7\u5230IX \u4e8b\u52a1A\u7533\u8bf7\u5230X\uff0cGap Lock<\/td>\n<\/tr>\n<tr>\n<td>3<\/td>\n<td><\/td>\n<td>\u6267\u884cselect &#8230; for update\u64cd\u4f5c<\/td>\n<td>\u4e8b\u52a1B\u7533\u8bf7\u5230IX\uff0c\u4e0e\u4e8b\u52a1A\u7684IX\u4e0d\u51b2\u7a81\u3002 \u4e8b\u52a1B\u7533\u8bf7\u5230Gap Lock\uff0cGap Lock\u53ef\u5171\u5b58\u3002<\/td>\n<\/tr>\n<tr>\n<td>4<\/td>\n<td>\u6267\u884cinsert\u64cd\u4f5c<\/td>\n<td><\/td>\n<td>\u4e8b\u52a1A\u5148\u7533\u8bf7\u63d2\u5165\u610f\u5411\u9501IX\uff0c\u4e0e\u4e8b\u52a1B\u7684Gap Lock\u51b2\u7a81\uff0c\u7b49\u5f85\u4e8b\u52a1B\u7684Gap Lock\u91ca\u653e\u3002<\/td>\n<\/tr>\n<tr>\n<td>5<\/td>\n<td><\/td>\n<td>\u6267\u884cinsert\u64cd\u4f5c<\/td>\n<td>\u4e8b\u52a1B\u5148\u7533\u8bf7\u63d2\u5165\u610f\u5411\u9501IX\uff0c\u4e0e\u4e8b\u52a1A\u7684Gap Lock\u51b2\u7a81\uff0c\u7b49\u5f85\u4e8b\u52a1A\u7684Gap Lock\u91ca\u653e\u3002<\/td>\n<\/tr>\n<tr>\n<td>6<\/td>\n<td><\/td>\n<td><\/td>\n<td>\u6b7b\u9501\u68c0\u6d4b\u5668\u68c0\u6d4b\u5230\u6b7b\u9501<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u8be6\u7ec6\u5206\u6790\uff1a<\/p>\n<p>\u2022\u65f6\u95f4\u70b91\uff0c\u4e8b\u52a1A\u4e0e\u4e8b\u52a1B\u5f00\u59cb\u6267\u884c\u4e8b\u52a1<\/p>\n<p>\u2022\u65f6\u95f4\u70b92\uff0c\u4e8b\u52a1A\u6267\u884cselect &#8230; for update\u64cd\u4f5c\uff0c\u6267\u884c\u8be5\u64cd\u4f5c\u65f6\u9996\u5148\u9700\u8981\u7533\u8bf7\u610f\u5411\u6392\u4ed6\u9501IX\u4f5c\u7528\u4e8e\u8868\u4e0a\uff0c\u63a5\u7740\u7533\u8bf7\u5230\u4e86\u6392\u4ed6\u9501X\u4f5c\u7528\u4e8e\u533a\u95f4\uff0c\u56e0\u4e3a\u67e5\u8be2\u7684\u503c\u4e0d\u5b58\u5728\uff0c\u6545Next key Lock\u9000\u5316\u4e3aGap Lock\u3002<\/p>\n<p>\u2022\u65f6\u95f4\u70b93\uff0c\u4e8b\u52a1B\u6267\u884cselect &#8230; for update\u64cd\u4f5c\uff0c\u9996\u5148\u7533\u8bf7\u610f\u5411\u6392\u4ed6\u9501IX\uff0c\u6839\u636e<a target=\"_blank\" href=\"https:\/\/link.segmentfault.com\/?enc=rI1PT%2FyapBapLlwn%2Fo9Lzg%3D%3D.7K35mk42peZbMqccEGxVGl9use9ezfiYmn3c2C%2FU7%2B6PvcGxkytGwVB2owpJbZ7cRiKV7q4fl2lTCw0zsvEQhg%3D%3D\" rel=\"noopener noreferrer\">2.1.3\u8282\u8868\u7ea7\u9501\u517c\u5bb9\u77e9\u9635<\/a>\u53ef\u4ee5\u770b\u5230\uff0c\u610f\u5411\u9501\u4e4b\u95f4\u662f\u76f8\u4e92\u517c\u5bb9\u7684\uff0c\u6545\u7533\u8bf7IX\u6210\u529f\u3002\u7531\u4e8e\u67e5\u8be2\u503c\u4e0d\u5b58\u5728\uff0c\u6545\u53ef\u4ee5\u7533\u8bf7X\u7684Gap Lock\uff0c\u800cGap Lock\u4e4b\u95f4\u662f\u53ef\u4ee5\u5171\u5b58\u7684\uff0c\u4e0d\u8bba\u662f\u5171\u4eab\u8fd8\u662f\u6392\u4ed6\u3002\u8fd9\u4e00\u70b9\u53ef\u4ee5\u53c2\u8003<a target=\"_blank\" href=\"https:\/\/link.segmentfault.com\/?enc=vXSedYaOAuGen5C8EWtEog%3D%3D.xWwuFwSaSSRnFFeEAHGzGbgaDuHuIlHgHiO5kHuYif0oCcMT%2FVmrCaBD52QpTJUqs9uvLcOvIMtkzF0aiR2FJQ%3D%3D\" rel=\"noopener noreferrer\">Innodb\u5173\u4e8eGap Lock\u7684\u63cf\u8ff0<\/a>\uff0c\u5173\u952e\u63cf\u8ff0\u672c\u6587\u7c98\u8d34\u81f3\u6b64\uff1a<\/p>\n<pre><code>Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.<\/code><\/pre>\n<p>\u2022\u65f6\u95f4\u70b94\uff0c\u4e8b\u52a1A\u6267\u884cinsert\u64cd\u4f5c\u524d\uff0c\u9996\u5148\u4f1a\u7533\u8bf7\u63d2\u5165\u610f\u5411\u9501\uff0c\u4f46\u6b64\u65f6\u4e8b\u52a1B\u5df2\u7ecf\u62e5\u6709\u4e86\u63d2\u5165\u533a\u95f4\u7684\u6392\u4ed6\u9501\uff0c\u6839\u636e<a target=\"_blank\" href=\"https:\/\/link.segmentfault.com\/?enc=6zxrLCbVOoKsmko3UCMJQw%3D%3D.ePEprdxCiTvSICOWKj%2BrxoaDpJ4FW5tYe8mhh5aFvPUQ2IFJg2AXKvy17nzk43RQr%2BLMAuNCKNfz9k4BdmfzYA%3D%3D\" rel=\"noopener noreferrer\">2.1.3\u8282\u8868\u7ea7\u9501\u517c\u5bb9\u77e9\u9635<\/a>\u53ef\u77e5\uff0c\u5728\u5df2\u6709X\u9501\u60c5\u51b5\u4e0b\uff0c\u518d\u6b21\u7533\u8bf7IX\u9501\u662f\u51b2\u7a81\u7684\uff0c\u9700\u8981\u7b49\u5f85\u4e8b\u52a1B\u5bf9X Gap Lock\u91ca\u653e\u3002<\/p>\n<p>\u2022\u65f6\u95f4\u70b95\uff0c\u4e8b\u52a1B\u6267\u884cinsert\u64cd\u4f5c\u524d\uff0c\u4e5f\u4f1a\u9996\u5148\u7533\u8bf7\u63d2\u5165\u610f\u5411\u9501\uff0c\u6b64\u65f6\u4e8b\u52a1A\u4e5f\u5bf9\u63d2\u5165\u533a\u95f4\u62e5\u6709X Gap Lock\uff0c\u56e0\u6b64\u9700\u8981\u7b49\u5f85\u4e8b\u52a1A\u5bf9X\u9501\u8fdb\u884c\u91ca\u653e\u3002<\/p>\n<p>\u2022\u65f6\u95f4\u70b96\uff0c\u4e8b\u52a1A\u4e0e\u4e8b\u52a1B\u5747\u5728\u7b49\u5f85\u5bf9\u65b9\u91ca\u653eX\u9501\uff0c\u540e\u88abMySQL\u7684\u6b7b\u9501\u68c0\u6d4b\u5668\u68c0\u6d4b\u5230\u540e\uff0c\u62a5Dead Lock\u9519\u8bef\u3002<\/p>\n<p>\u601d\u8003\uff1a\u5047\u5982select &#8230; for update \u67e5\u8be2\u7684\u6570\u636e\u5b58\u5728\u65f6\uff0c\u4f1a\u662f\u4ec0\u4e48\u6837\u7684\u8fc7\u7a0b\u5462\uff1f\u8fc7\u7a0b\u5982\u4e0b\u8868\uff1a<\/p>\n<table>\n<thead>\n<tr>\n<th>\u65f6\u95f4\u70b9<\/th>\n<th>\u4e8b\u52a1A<\/th>\n<th>\u4e8b\u52a1B<\/th>\n<th>\u6f5c\u5728\u52a8\u4f5c<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>1<\/td>\n<td>\u5f00\u59cb\u4e8b\u52a1<\/td>\n<td>\u5f00\u59cb\u4e8b\u52a1<\/td>\n<td><\/td>\n<\/tr>\n<tr>\n<td>2<\/td>\n<td>\u6267\u884cselect &#8230; for update\u64cd\u4f5c<\/td>\n<td><\/td>\n<td>\u4e8b\u52a1A\u7533\u8bf7\u5230IX \u4e8b\u52a1A\u7533\u8bf7\u5230X\u884c\u9501\uff0c\u56e0\u6570\u636e\u5b58\u5728\u6545\u9501\u9000\u5316\u4e3aRecord Lock\u3002<\/td>\n<\/tr>\n<tr>\n<td>3<\/td>\n<td><\/td>\n<td>\u6267\u884cselect &#8230; for update\u64cd\u4f5c<\/td>\n<td>\u4e8b\u52a1B\u7533\u8bf7\u5230IX\uff0c\u4e0e\u4e8b\u52a1A\u7684IX\u4e0d\u51b2\u7a81\u3002 \u4e8b\u52a1B\u60f3\u7533\u8bf7\u76ee\u6807\u884c\u7684Record Lock\uff0c\u6b64\u65f6\u9700\u8981\u7b49\u5f85\u4e8b\u52a1A\u91ca\u653e\u8be5\u9501\u8d44\u6e90\u3002<\/td>\n<\/tr>\n<tr>\n<td>4<\/td>\n<td>\u6267\u884cupdate\u64cd\u4f5c<\/td>\n<td><\/td>\n<td>\u4e8b\u52a1A\u5148\u7533\u8bf7\u63d2\u5165\u610f\u5411\u9501IX\uff0c\u6b64\u65f6\u4e8b\u52a1B\u4ec5\u4ec5\u62e5\u6709IX\u9501\u8d44\u6e90\uff0c\u517c\u5bb9\uff0c\u4e0d\u51b2\u7a81\u3002\u7136\u540e\u4e8b\u52a1A\u62e5\u6709X\u7684Record Lock\uff0c\u6545\u6267\u884c\u66f4\u65b0\u3002<\/td>\n<\/tr>\n<tr>\n<td>5<\/td>\n<td>commit<\/td>\n<td><\/td>\n<td>\u4e8b\u52a1A\u63d0\u4ea4\uff0c\u91ca\u653eIX\u4e0eX\u9501\u8d44\u6e90\u3002<\/td>\n<\/tr>\n<tr>\n<td>6<\/td>\n<td><\/td>\n<td>\u6267\u884cselect &#8230; for update\u64cd\u4f5c<\/td>\n<td>\u4e8b\u52a1B\u4e8b\u52a1B\u6b64\u65f6\u83b7\u53d6\u5230X Record Lock\u3002<\/td>\n<\/tr>\n<tr>\n<td>7<\/td>\n<td><\/td>\n<td>\u6267\u884cupdate\u64cd\u4f5c<\/td>\n<td>\u4e8b\u52a1B\u62e5\u6709X Record Lock\u6267\u884c\u66f4\u65b0<\/td>\n<\/tr>\n<tr>\n<td>8<\/td>\n<td><\/td>\n<td>commit<\/td>\n<td>\u4e8b\u52a1B\u91ca\u653eIX\u4e0eX\u9501\u8d44\u6e90<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u4e5f\u5c31\u662f\u5f53\u67e5\u8be2\u6570\u636e\u5b58\u5728\u65f6\uff0c\u4e0d\u4f1a\u51fa\u73b0\u6b7b\u9501\u95ee\u9898\u3002<\/p>\n<h2>\u4e09\u3001\u89e3\u51b3\u65b9\u6cd5<\/h2>\n<p>1\u3001\u5728\u4e8b\u52a1\u5f00\u59cb\u4e4b\u524d\uff0c\u91c7\u7528CAS+\u5206\u5e03\u5f0f\u9501\u6765\u63a7\u5236\u5e76\u53d1\u5199\u8bf7\u6c42\u3002\u5206\u5e03\u5f0f\u9501key\u53ef\u4ee5\u8bbe\u7f6e\u4e3astore_skuId_version<\/p>\n<p>2\u3001\u4e8b\u52a1\u8fc7\u7a0b\u53ef\u4ee5\u6539\u5199\u4e3a\uff1a<\/p>\n<pre><code>start transaction\n\/\/ RR\u7ea7\u522b\u4e0b\uff0c\u8bfb\u89c6\u56fe\ndata = select from table(tenantId, storeId, skuId)\nif (data == null) {\n    \/\/ \u53ef\u80fd\u51fa\u73b0\u5199\u5e76\u53d1\n    insert\n} else {\n    data = select for update(tenantId, storeId, skuId)\n    update\n}\nend transaction<\/code><\/pre>\n<p>\u867d\u7136\u89e3\u51b3\u4e86\u63d2\u5165\u6570\u636e\u4e0d\u5b58\u5728\u65f6\u4f1a\u51fa\u73b0\u7684\u6b7b\u9501\u95ee\u9898\uff0c\u4f46\u662f\u53ef\u80fd\u5b58\u5728\u5e76\u53d1\u5199\u7684\u95ee\u9898\uff0c\u7b2c\u4e00\u4e2a\u4e8b\u52a1\u83b7\u5f97\u9501\u4f1a\u9996\u5148\u63d2\u5165\u6210\u529f\uff0c\u7b2c\u4e8c\u4e2a\u4e8b\u52a1\u7b49\u5f85\u7b2c\u4e00\u4e2a\u4e8b\u52a1\u63d0\u4ea4\u540e\uff0c\u63d2\u5165\u6570\u636e\uff0c\u56e0\u4e3a\u6570\u636e\u5b58\u5728\u4e86\u6240\u4ee5\u62a5\u9519\u56de\u6eda\u3002<\/p>\n<p>3\u3001\u8c03\u6574\u4e8b\u52a1\u9694\u79bb\u7ea7\u522b\u4e3aRC\uff0c\u5728RC\u4e0b\u6ca1\u6709next key lock\uff08\u6ce8\u610f\uff0c\u6b64\u5904\u5e76\u4e0d\u51c6\u786e\uff0cRC\u4f1a\u6709\u5c11\u90e8\u5206\u60c5\u51b5\u52a0Next key lock\uff09\uff0c\u6545\u6b64\u65f6\u4ec5\u4ec5\u4f1a\u6709record lock\uff0c\u6240\u4ee5\u4e8b\u52a12\u8fdb\u884cselect for update\u65f6\u9700\u8981\u7b49\u5f85\u4e8b\u52a11\u63d0\u4ea4\u3002<\/p>\n<p><strong>\u53c2\u8003\u6587\u732e<\/strong><\/p>\n<p>[1] Innodb\u9501\u5b98\u65b9\u6587\u6863\uff1a<a target=\"_blank\" href=\"https:\/\/link.segmentfault.com\/?enc=zfoKzsut%2BNvsJbDRixw1Jw%3D%3D.UxXap3GxzTDBuZWzlkUMk2h3S73fdGXQXcutsMy1CkVAMlP7hzv5ekiq5RyLUlEaTgmbCP3Wssc6TJJWT1tjfA%3D%3D\" rel=\"noopener noreferrer\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-locking.html<\/a><\/p>\n<p>[2] <a target=\"_blank\" href=\"https:\/\/link.segmentfault.com\/?enc=bTdHGrakzdn84UVi7KAxAQ%3D%3D.FjdRG8h3ALTAONF7T7BoLRaiJ8FU%2FL%2B2TlbwtHkp1HoEBwQdTg9iG2PZw2DIr1TlY0ZiiUMTJeTAAHIolaBXCw%3D%3D\" rel=\"noopener noreferrer\">https:\/\/blog.csdn.net\/qq_43684538\/article\/details\/131450395<\/a><\/p>\n<p>[3] <a target=\"_blank\" href=\"https:\/\/link.segmentfault.com\/?enc=riPajH4TD0pzdlgqjPjluA%3D%3D.Zpe9S%2FStETG1DVG76OaTiI%2F6D89%2B1Rts0KVBv2fDUEUr2M2CJMbg1y3V4kbOyCeN\" rel=\"noopener noreferrer\">https:\/\/www.jianshu.com\/p\/027afd6345d5<\/a><\/p>\n<p>[4] <a target=\"_blank\" href=\"https:\/\/link.segmentfault.com\/?enc=AguEoRV0GWgwYDfBCBJndQ%3D%3D.vNKX%2BNG%2BMgSdOtRh%2F%2B2kWnDYN40ZceZPDtnRpttA3ZYrncfWGh96Jesp5nuv2ili\" rel=\"noopener noreferrer\">https:\/\/www.cnblogs.com\/micrari\/p\/8029710.html<\/a><\/p>\n<p>\u82e5\u6709\u9519\u8bef\uff0c\u8fd8\u671b\u6279\u8bc4\u6307\u6b63<\/p>\n<blockquote>\n<p>\u4f5c\u8005\uff1a\u4eac\u4e1c\u96f6\u552e\u00a0 \u5218\u54f2<\/p>\n<p>\u6765\u6e90\uff1a\u4eac\u4e1c\u4e91\u5f00\u53d1\u8005\u793e\u533a \u8f6c\u8f7d\u8bf7\u6ce8\u660e\u6765\u6e90<\/p>\n<\/blockquote>\n<p><a href=\"https:\/\/segmentfault.com\/a\/1190000044261890\">https:\/\/segmentfault.com\/a\/1190000044261890<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u4e00\u3001\u80cc\u666f \u5728\u9884\u53d1\u73af\u5883\u4e2d\uff0c\u7531\u6d88\u606f\u9a71\u52a8\u6700\u7ec8\u89e6\u53d1\u6267\u884c\u4e8b\u52a1\u6765\u5199\u5e93\u5b58\uff0c\u4f46\u662f\u5bfc\u81f4MySQL\u53d1\u751f\u6b7b\u9501\uff0c\u5199\u5e93\u5b58\u5931\u8d25\u3002 com. [&hellip;]<\/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-10008339","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\/10008339","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=10008339"}],"version-history":[{"count":0,"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts\/10008339\/revisions"}],"wp:attachment":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=10008339"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=10008339"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=10008339"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}