{"id":109457,"date":"2026-09-24T07:24:51","date_gmt":"2026-09-23T23:24:51","guid":{"rendered":"https:\/\/www.wsisp.com\/helps\/109457.html"},"modified":"2026-09-24T07:24:51","modified_gmt":"2026-09-23T23:24:51","slug":"mysql-%e5%86%85%e6%a0%b8%e5%ae%9e%e6%88%98%ef%bc%884%ef%bc%89%ef%bc%9a%e8%a1%8c%e9%94%81%e3%80%81%e9%97%b4%e9%9a%99%e9%94%81%e4%b8%8e%e6%ad%bb%e9%94%81%e5%ae%9a%e4%bd%8d","status":"publish","type":"post","link":"https:\/\/www.wsisp.com\/helps\/109457.html","title":{"rendered":"MySQL \u5185\u6838\u5b9e\u6218\uff084\uff09\uff1a\u884c\u9501\u3001\u95f4\u9699\u9501\u4e0e\u6b7b\u9501\u5b9a\u4f4d"},"content":{"rendered":"

\u95ee\u9898\u80cc\u666f<\/h3>\n

\u4e0a\u4e00\u7bc7\u7ed3\u5c3e\u8bf4"\u9694\u79bb\u8ba9\u8bfb\u5199\u4e92\u4e0d\u963b\u585e，\u4ee3\u4ef7\u662f\u5199\u4e0e\u5199\u6392\u961f"，\u672c\u7bc7\u5c31\u94bb\u5230\u961f\u5217\u91cc\u53bb\u3002InnoDB \u7684\u8bfb\u9760 MVCC \u5404\u8d70\u5404\u7684\u65f6\u95f4\u7ebf，\u5199\u5374\u5fc5\u987b\u6c47\u5230\u540c\u4e00\u4e2a\u73b0\u5b9e：\u5f53\u524d\u8bfb\u8bfb\u6700\u65b0\u7248\u672c\u3001\u6539\u6700\u65b0\u7248\u672c，\u4e24\u4e2a\u4e8b\u52a1\u6539\u540c\u4e00\u884c\u65f6\u53ea\u80fd\u4e00\u4e2a\u4e00\u4e2a\u6765\u3002\u4e8e\u662f\u51fa\u73b0\u4e86 OLTP \u7cfb\u7edf\u91cc\u6700\u4e09\u7c7b\u6700\u5413\u4eba\u7684\u73b0\u573a：UPDATE \u4e00\u4e2a\u4e0d\u5b58\u5728\u7684\u884c，\u53e6\u4e00\u4ef6\u4e8b\u52a1 INSERT \u5374\u88ab\u83ab\u540d\u5361\u5230 1205 \u8d85\u65f6；\u4e24\u4e2a\u4e1a\u52a1\u6a21\u5757\u4e0a\u7ebf\u534a\u5e74\u76f8\u5b89\u65e0\u4e8b，\u67d0\u5929\u8d77\u5076\u53d1 1213 \u6b7b\u9501\u4e14\u8d8a\u6765\u8d8a\u9891\u7e41；\u6700\u60e8\u7684\u4e00\u79cd\u2014\u2014\u4e00\u6761 WHERE \u6ca1\u8d70\u7d22\u5f15\u7684 UPDATE \u628a\u6574\u5f20\u8868\u9501\u6b7b，\u8fde\u63a5\u6c60\u96ea\u5d29\u3002\u8fd9\u4e9b\u73b0\u8c61\u5171\u7528\u4e00\u5957\u89c4\u5219：InnoDB \u5728 RR \u4e0b\u7528"\u8bb0\u5f55\u9501 + \u95f4\u9699\u9501"\u7ec4\u5408\u62f3\u9632\u5199\u8def\u5f84\u5e7b\u8bfb，\u800c\u95f4\u9699\u9501\u4e4b\u95f4\u5f7c\u6b64\u517c\u5bb9\u3001\u5374\u56f4\u527f\u63d2\u5165\u610f\u5411\u9501\u3002\u672c\u7bc7\u56de\u7b54\u56db\u4e2a\u95ee\u9898：InnoDB \u7684\u9501\u5230\u5e95\u6709\u54ea\u51e0\u79cd\u3001\u52a0\u5728\u4ec0\u4e48\u5bf9\u8c61\u4e0a；\u4e0d\u540c SQL \u5f62\u6001\u5bf9\u5e94\u7684\u52a0\u9501\u96c6\u5408\u662f\u4ec0\u4e48\u89c4\u5f8b；\u6b7b\u9501\u600e\u4e48\u88ab\u68c0\u6d4b\u3001\u727a\u7272\u8005\u600e\u4e48\u9009；\u4ee5\u53ca\u751f\u4ea7\u73b0\u573a\u600e\u4e48\u5728\u5341\u5206\u949f\u5185\u628a\u9501\u4e8b\u6545\u5b9a\u4f4d\u5230\u8bed\u53e5\u548c\u4e8b\u52a1\u3002<\/p>\n

\u6838\u5fc3\u539f\u7406<\/h3>\n

\u7b2c\u4e00，\u9501\u7684"\u7c7b\u578b \u00d7 \u5f3a\u5ea6"\u4e24\u4e2a\u7ef4\u5ea6\u3002\u5f3a\u5ea6\u4e0a\u5206 S \u9501（\u5171\u4eab，LOCK IN SHARE MODE）\u4e0e X \u9501（\u6392\u4ed6，UPDATE\/DELETE\/INSERT\/FOR UPDATE），\u517c\u5bb9\u77e9\u9635\u5c31\u4e00\u53e5\u8bdd：S \u4e0e S \u76f8\u5bb9，\u5176\u4f59\u7686\u51b2\u7a81\u3002\u7c7b\u578b\u4e0a\u6309\u52a0\u9501\u5bf9\u8c61\u5206\u56db\u79cd：\u8bb0\u5f55\u9501\u9501\u7d22\u5f15\u8bb0\u5f55\u672c\u8eab；\u95f4\u9699\u9501\u9501\u4e24\u6761\u7d22\u5f15\u8bb0\u5f55\u4e4b\u95f4\u7684\u5f00\u533a\u95f4，\u53ea\u62e6\u63d2\u5165\u4e0d\u62e6\u8bfb\u5199；next-key \u9501=\u95f4\u9699+\u5176\u540e\u8bb0\u5f55\u7684\u5de6\u5f00\u53f3\u95ed\u7ec4\u5408，RR \u4e0b\u626b\u63cf\u7684\u9ed8\u8ba4\u5355\u4f4d；\u63d2\u5165\u610f\u5411\u9501\u662f INSERT \u524d\u7684\u7279\u6b8a\u610f\u5411\u9501，\u4e0e\u4efb\u4f55\u95f4\u9699\u9501\/next-key \u9501\u51b2\u7a81\u3002\u4e00\u4e2a\u53cd\u76f4\u89c9\u4f46\u51b3\u5b9a\u4e00\u5207\u884c\u4e3a\u7684\u6027\u8d28：\u95f4\u9699\u9501\u4e4b\u95f4\u4e92\u76f8\u517c\u5bb9\u2014\u2014\u4e24\u4e2a\u4e8b\u52a1\u53ef\u4ee5"\u6301\u6709"\u540c\u4e00\u6bb5\u95f4\u9699，\u4f46\u8c01\u4e5f\u522b\u60f3\u5f80\u91cc\u63d2；\u800c\u4e00\u65e6\u6709\u63d2\u5165\u610f\u5411\u649e\u8fdb\u88ab\u9501\u95f4\u9699，\u7b49\u5f85\u5c31\u53d1\u751f\u4e86\u3002\u6b64\u5916 InnoDB \u7528\u9690\u5f0f\u9501\u7701\u5185\u5b58：\u65b0\u63d2\u5165\u7684\u884c\u4e0d\u7acb\u523b\u5efa\u9501\u7ed3\u6784，\u522b\u7684\u4e8b\u52a1\u6765\u78b0\u65f6\u624d\u7531 trx_id \u8865\u5efa\u663e\u5f0f\u9501\u3002<\/p>\n

\u7b2c\u4e8c，\u52a0\u9501\u96c6\u5408\u8ddf\u7740\u8bbf\u95ee\u8def\u5f84\u8d70\u3002\u7b49\u503c\u67e5\u8be2\u6253\u4e2d\u552f\u4e00\u7d22\u5f15\u8bb0\u5f55：\u53ea\u52a0\u4e00\u4e2a\u8bb0\u5f55\u9501，\u524d\u540e\u95f4\u9699\u90fd\u4e0d\u9501\u2014\u2014\u8fd9\u662f\u5e76\u53d1\u6700\u597d\u7684\u8def\u5f84\u3002\u7b49\u503c\u67e5\u4e0d\u5230（\u5982 WHERE id=25，id \u552f\u4e00）：\u9000\u5316\u4e3a\u9501"\u641c\u7d22\u843d\u70b9"\u6240\u5728\u95f4\u9699\u7684 gap lock，\u9501 (20,30) \u4f46\u4e0d\u542b\u8bb0\u5f55，\u4e13\u804c\u62e6 INSERT 25，\u9632\u6b62\u522b\u7684\u4e8b\u52a1\u5728\u7ed3\u679c\u96c6\u91cc\u5236\u9020\u5e7b\u5f71\u3002\u975e\u552f\u4e00\u7d22\u5f15\u7b49\u503c\u547d\u4e2d：next-key \u9501\u547d\u4e2d\u8bb0\u5f55 + \u53f3\u90bb\u95f4\u9699，\u56e0\u4e3a\u8981\u786e\u8ba4"\u6ca1\u6709\u4e0b\u4e00\u884c\u4e86"\u5fc5\u987b\u626b\u5230\u7b2c\u4e00\u4e2a\u4e0d\u6ee1\u8db3\u6761\u4ef6\u7684\u8bb0\u5f55\u3002\u6700\u574f\u7684：WHERE \u4e0d\u8d70\u7d22\u5f15，\u5168\u8868\u626b\u63cf\u610f\u5473\u7740\u5bf9\u6bcf\u4e2a\u7ecf\u8fc7\u7684\u8bb0\u5f55\u52a0 next-key \u9501\u2014\u2014\u7b49\u4e8e\u9501\u4f4f\u6574\u5f20\u8868\u7684\u6240\u6709\u8bb0\u5f55\u4e0e\u6240\u6709\u95f4\u9699，UPDATE \u672a\u547d\u4e2d\u4efb\u4f55\u884c\u4e5f\u7167\u6837\u5168\u9501（\u4f18\u5316\u5668\u6ca1\u5f97\u9009，\u626b\u63cf\u5373\u52a0\u9501）\u3002RC \u5219\u6e29\u548c\u5f97\u591a：\u4e0d\u52a0\u95f4\u9699\u9501（\u5916\u952e\u4e0e\u91cd\u590d\u952e\u68c0\u67e5\u9664\u5916），\u626b\u63cf\u4e2d\u4e0d\u5339\u914d\u7684\u884c\u8fd8\u4f1a\u505a\u534a\u4e00\u81f4\u8bfb\u63d0\u524d\u91ca\u653e\u9501，\u6240\u4ee5"\u6539 RC \u9501\u7ea0\u7eb7\u5c11\u4e00\u534a"\u6709\u575a\u5b9e\u7684\u673a\u5236\u4f9d\u636e\u3002<\/p>\n

\u7b2c\u4e09，\u95f4\u9699\u9501\u4e3a\u4ec0\u4e48\u975e\u5b58\u5728\u4e0d\u53ef\u3002\u4e0a\u4e00\u7bc7\u8bf4\u8fc7：MVCC \u53ea\u6321\u5f97\u4f4f\u5feb\u7167\u8bfb\u7684\u5e7b\u8c61，\u5f53\u524d\u8bfb\u7684\u5e7b\u8c61\u5fc5\u987b\u9760"\u628a\u53ef\u80fd\u63d2\u5165\u7684\u8303\u56f4\u5148\u5708\u8d77\u6765"\u3002RR \u7684\u5b8c\u6574\u627f\u8bfa\u662f"\u540c\u4e00\u4e8b\u52a1\u5185\u91cd\u590d\u8303\u56f4\u67e5\u8be2\u7ed3\u679c\u96c6\u4e0d\u53d8"，\u5151\u73b0\u65b9\u5f0f\u5c31\u662f next-key\/gap \u56f4\u51fa\u7684\u6805\u680f\u3002\u6805\u680f\u7684\u4ee3\u4ef7\u662f\u63d2\u5165\u5e76\u53d1：\u70ed\u70b9\u533a\u95f4\u7684 UPDATE \u4f1a\u963b\u585e\u540c\u533a\u95f4 INSERT，\u9ad8\u5e76\u53d1\u5199\u5bc6\u96c6\u4e1a\u52a1\u5b81\u613f\u8981 RC + ROW binlog \u6362\u541e\u5410，\u628a\u9632\u5e7b\u7684\u8d23\u4efb\u4ea4\u7ed9\u4e1a\u52a1\u5c42\u7684\u663e\u5f0f FOR UPDATE\u2014\u2014\u8fd9\u662f\u9694\u79bb\u7ea7\u522b\u9009\u578b\u7684\u4e0b\u534a\u573a\u3002<\/p>\n

\u7b2c\u56db，\u6b7b\u9501：\u7b49\u5f85\u56fe\u3001\u68c0\u6d4b\u4e0e\u727a\u7272\u8005\u3002\u4e8b\u52a1 A \u6301\u9501\u7b49 B \u7684\u9501\u3001B \u6301\u9501\u7b49 A \u7684\u9501，\u7b49\u5f85\u56fe\u6210\u73af\u4e14\u65e0\u5916\u90e8\u5e72\u9884\u6c38\u4e0d\u81ea\u89e3\u2014\u2014\u4e0e"\u5355\u7eaf\u7684\u5feb"（\u6392\u961f\u4f1a\u968f\u63d0\u4ea4\u6d88\u6563）\u672c\u8d28\u4e0d\u540c\u3002InnoDB \u5728\u6bcf\u4e2a\u9501\u8bf7\u6c42\u5165\u7b49\u5f85\u961f\u5217\u65f6\u505a\u56fe\u68c0\u6d4b，\u627e\u5230\u73af\u5373\u56de\u6eda\u73af\u4e2d trx_weight \u6700\u5c0f\u7684\u4e8b\u52a1（weight\u2248undo \u91cf+\u9501\u6570，\u76f4\u89c9：\u8ba9\u56de\u6eda\u4ee3\u4ef7\u5c0f\u7684\u4e00\u65b9\u5148\u8d70），\u62a5 1213；\u88ab\u56de\u6eda\u65b9\u5e94\u7528\u5fc5\u987b\u6355\u83b7\u5e76\u91cd\u8bd5\u6574\u7b14\u4e8b\u52a1\u3002\u68c0\u6d4b\u672c\u8eab\u6709\u6210\u672c：\u70ed\u70b9\u884c\u4e0a\u6392\u961f\u4e8b\u52a1\u8d8a\u591a，\u6bcf\u6b21\u5165\u961f\u7684\u5168\u56fe\u904d\u5386\u8d8a\u8d35，\u6781\u7aef\u4e89\u7528\u4e0b\u9501\u68c0\u6d4b\u80fd\u5403\u6389\u5927\u91cf CPU，\u6b64\u65f6\u624d\u6709"\u5173 innodb_deadlock_detect\u3001\u8c03\u5c0f innodb_lock_wait_timeout \u9760\u8d85\u65f6\u515c\u5e95"\u7684\u8ba8\u8bba\u7a7a\u95f4\u2014\u2014\u90a3\u662f\u7528"\u66f4\u6162\u7684\u5931\u8d25"\u6362"\u66f4\u4fbf\u5b9c\u7684\u6392\u961f"，\u4e0d\u662f\u767d\u6361\u7684\u4f18\u5316\u3002<\/p>\n

\u7b2c\u4e00\u6b21\u4ee3\u7801\u5b9e\u9a8c\u53ca\u8f93\u51fa<\/h3>\n

\u7528\u7eaf Python \u5efa\u6a21 RR\/RC \u7684\u52a0\u9501\u89c4\u5219（\u5185\u5b58\u6a21\u62df，\u975e\u771f mysqld）：\u805a\u7c07\u7d22\u5f15\u4e0a\u6709\u8bb0\u5f55 10\/20\/30，locks_for() \u6309\u7b2c\u4e8c\u8282\u7684\u89c4\u5f8b\u4ea7\u51fa\u626b\u63cf\u533a\u95f4\u5bf9\u5e94\u7684 next-key \u96c6\u5408，insert_blocked() \u5224\u5b9a T2 \u7684 INSERT（\u63d2\u5165\u610f\u5411\u9501）\u662f\u5426\u4e0e\u5728\u9014\u9501\u51b2\u7a81\u3002\u5bf9\u6bd4\u4e24\u6761 SQL：\u547d\u4e2d 20 \u7684\u8303\u56f4 UPDATE，\u4ee5\u53ca\u76ee\u6807\u884c\u6839\u672c\u4e0d\u5b58\u5728\u7684\u7b49\u503c UPDATE\u3002<\/p>\n

# InnoDB RR \u9501\u6a21\u578b\u6a21\u62df: \u805a\u7c07\u7d22\u5f15\u73b0\u6709\u8bb0\u5f55 10\/20\/30 (supremum=\u6b63\u65e0\u7a77\u54e8\u5175)<\/span>
\n# next-key \u9501 = \u95f4\u9699\u9501 + \u8bb0\u5f55\u9501; \u63d2\u5165\u610f\u5411\u9501\u843d\u8fdb\u88ab\u9501\u95f4\u9699\u5373\u51b2\u7a81<\/span>
\nINDEX =<\/span> [<\/span>10<\/span>,<\/span> 20<\/span>,<\/span> 30<\/span>]<\/span><\/p>\n

def<\/span> locks_for<\/span>(<\/span>iso,<\/span> sql_lo,<\/span> sql_hi)<\/span>:<\/span>
\n """RR \u7d22\u5f15\u626b\u63cf\u7684\u52a0\u9501\u96c6\u5408: \u8303\u56f4\u5185\u6bcf\u4e2a\u8bb0\u5f55\u52a0 next-key (prev, k],
\n \u518d\u7ed9\u7b2c\u4e00\u4e2a\u8d8a\u754c\u8bb0\u5f55\u8865\u4e00\u4e2a next-key \u4f5c\u4e3a\u626b\u63cf\u7ec8\u70b9; RC \u4e0d\u52a0\u95f4\u9699\u9501"""<\/span>
\n if<\/span> iso ==<\/span> "RC"<\/span>:<\/span>
\n return<\/span> set<\/span>(<\/span>)<\/span>
\n locks =<\/span> set<\/span>(<\/span>)<\/span>
\n def<\/span> nk<\/span>(<\/span>k)<\/span>:<\/span>
\n prev =<\/span> max<\/span>(<\/span>(<\/span>x for<\/span> x in<\/span> INDEX if<\/span> x <<\/span> k)<\/span>,<\/span> default=<\/span>–<\/span>1<\/span>)<\/span>
\n return<\/span> (<\/span>"NK"<\/span>,<\/span> prev,<\/span> k)<\/span>
\n for<\/span> k in<\/span> INDEX:<\/span>
\n if<\/span> sql_lo <=<\/span> k <=<\/span> sql_hi:<\/span>
\n locks.<\/span>add(<\/span>nk(<\/span>k)<\/span>)<\/span>
\n beyond =<\/span> min<\/span>(<\/span>(<\/span>k for<\/span> k in<\/span> INDEX if<\/span> k ><\/span> sql_hi)<\/span>,<\/span> default=<\/span>None<\/span>)<\/span>
\n locks.<\/span>add(<\/span>nk(<\/span>beyond)<\/span> if<\/span> beyond else<\/span> (<\/span>"NK"<\/span>,<\/span> INDEX[<\/span>–<\/span>1<\/span>]<\/span>,<\/span> 10<\/span> **<\/span> 9<\/span>)<\/span>)<\/span>
\n return<\/span> locks<\/p>\n

def<\/span> insert_blocked<\/span>(<\/span>iso,<\/span> sql_lo,<\/span> sql_hi,<\/span> new_key)<\/span>:<\/span>
\n for<\/span> lk in<\/span> locks_for(<\/span>iso,<\/span> sql_lo,<\/span> sql_hi)<\/span>:<\/span>
\n kind,<\/span> lo,<\/span> hi =<\/span> lk
\n if<\/span> lo <<\/span> new_key <=<\/span> hi:<\/span> return<\/span> lk
\n return<\/span> None<\/span><\/p>\n

print<\/span>(<\/span>"\u573a\u666f: T1 \u6267\u884c UPDATE t SET v=v+1 WHERE id BETWEEN 18 AND 26 (\u547d\u4e2d 20)"<\/span>)<\/span>
\nfor<\/span> iso in<\/span> (<\/span>"RR"<\/span>,<\/span> "RC"<\/span>)<\/span>:<\/span>
\n lk =<\/span> locks_for(<\/span>iso,<\/span> 18<\/span>,<\/span> 26<\/span>)<\/span>
\n print<\/span>(<\/span>" %s \u4e0b T1 \u6301\u6709\u9501: %s"<\/span> %<\/span> (<\/span>iso,<\/span> sorted<\/span>(<\/span>lk)<\/span> or<\/span> "\u4ec5\u547d\u4e2d\u8bb0\u5f55\u7684\u884c\u9501(\u6a21\u578b\u7b80\u5316)"<\/span>)<\/span>)<\/span>
\n for<\/span> ins in<\/span> (<\/span>15<\/span>,<\/span> 25<\/span>,<\/span> 20<\/span>,<\/span> 35<\/span>)<\/span>:<\/span>
\n b =<\/span> insert_blocked(<\/span>iso,<\/span> 18<\/span>,<\/span> 26<\/span>,<\/span> ins)<\/span>
\n tag =<\/span> "INSERT id=%d"<\/span> %<\/span> ins
\n if<\/span> b:<\/span>
\n print<\/span>(<\/span>" T2 %s -> \u4e0e %s \u51b2\u7a81, \u6302\u8d77\u7b49\u5f85(1205 lock wait)"<\/span> %<\/span> (<\/span>tag,<\/span> b)<\/span>)<\/span>
\n else<\/span>:<\/span>
\n print<\/span>(<\/span>" T2 %s -> \u653e\u884c"<\/span> %<\/span> tag)<\/span>
\nprint<\/span>(<\/span>)<\/span>
\nprint<\/span>(<\/span>"\u573a\u666f\u4e8c: T1 \u6267\u884c UPDATE … WHERE id=25 (\u4e0d\u5b58\u5728)"<\/span>)<\/span>
\nb_rr =<\/span> insert_blocked(<\/span>"RR"<\/span>,<\/span> 25<\/span>,<\/span> 25<\/span>,<\/span> 25<\/span>)<\/span>
\nprint<\/span>(<\/span>" RR: T1 \u9501\u4f4f (20,30] next-key; T2 \u6b64\u523b INSERT 25 -> %s"<\/span> %<\/span> (<\/span>"\u88ab\u95f4\u9699\u9501\u6321\u4f4f(\u9632\u5e7b\u8bfb)"<\/span> if<\/span> b_rr else<\/span> "\u653e\u884c"<\/span>)<\/span>)<\/span>
\nprint<\/span>(<\/span>" RC: \u65e0\u884c\u53ef\u9501\u4ea6\u65e0\u95f4\u9699\u9501 -> INSERT 25 \u653e\u884c, \u7531\u8bed\u53e5\u7ea7\u884c\u9501\u515c\u5e95"<\/span>)<\/span><\/p>\n

\u8fd0\u884c\u8f93\u51fa：<\/p>\n

\u573a\u666f: T1 \u6267\u884c UPDATE t SET v=v+1 WHERE id BETWEEN 18 AND 26 (\u547d\u4e2d 20)
\n RR \u4e0b T1 \u6301\u6709\u9501: [('NK', 10, 20), ('NK', 20, 30)]
\n T2 INSERT id=15 -> \u4e0e ('NK', 10, 20) \u51b2\u7a81, \u6302\u8d77\u7b49\u5f85(1205 lock wait)
\n T2 INSERT id=25 -> \u4e0e ('NK', 20, 30) \u51b2\u7a81, \u6302\u8d77\u7b49\u5f85(1205 lock wait)
\n T2 INSERT id=20 -> \u4e0e ('NK', 10, 20) \u51b2\u7a81, \u6302\u8d77\u7b49\u5f85(1205 lock wait)
\n T2 INSERT id=35 -> \u653e\u884c
\n RC \u4e0b T1 \u6301\u6709\u9501: \u4ec5\u547d\u4e2d\u8bb0\u5f55\u7684\u884c\u9501(\u6a21\u578b\u7b80\u5316)
\n T2 INSERT id=15 -> \u653e\u884c
\n T2 INSERT id=25 -> \u653e\u884c
\n T2 INSERT id=20 -> \u653e\u884c
\n T2 INSERT id=35 -> \u653e\u884c<\/p>\n

\u573a\u666f\u4e8c: T1 \u6267\u884c UPDATE … WHERE id=25 (\u4e0d\u5b58\u5728)
\n RR: T1 \u9501\u4f4f (20,30] next-key; T2 \u6b64\u523b INSERT 25 -> \u88ab\u95f4\u9699\u9501\u6321\u4f4f(\u9632\u5e7b\u8bfb)
\n RC: \u65e0\u884c\u53ef\u9501\u4ea6\u65e0\u95f4\u9699\u9501 -> INSERT 25 \u653e\u884c, \u7531\u8bed\u53e5\u7ea7\u884c\u9501\u515c\u5e95<\/p>\n

\u8f93\u51fa\u91cc\u4e09\u4e2a\u7ec6\u8282\u5bf9\u5e94\u4e09\u7c7b\u4e8b\u6545\u3002\u7b2c\u4e00，RR \u8303\u56f4 UPDATE \u9501\u4e86\u4e24\u6bb5 next-key：(10,20] \u7f69\u4f4f\u547d\u4e2d\u8bb0\u5f55，(20,30] \u7f69\u4f4f"\u626b\u63cf\u505c\u4e0b\u7684\u5730\u65b9"\u2014\u2014\u6240\u4ee5 UPDATE \u660e\u660e\u53ea\u6539\u4e00\u884c，\u63d2\u5165 15 \u5230 25 \u4e4b\u95f4\u5168\u90e8\u6392\u961f，\u8fd9\u5c31\u662f"UPDATE \u9501\u4e86\u4e0d\u5b58\u5728\u7684\u884c"\u7684\u771f\u76f8\u3002\u7b2c\u4e8c，INSERT id=20 \u4e5f\u88ab\u62e6：\u8bb0\u5f55\u9501\u672c\u5c31\u542b\u5728 next-key \u91cc（\u4e14\u771f\u5b9e\u573a\u666f\u8fd8\u4f1a\u649e\u4e3b\u952e\u91cd\u590d）\u3002\u7b2c\u4e09，\u573a\u666f\u4e8c UPDATE \u4e00\u884c\u6ca1\u6539\u5374\u7167\u6837\u7559\u4e0b\u95f4\u9699\u9501：\u52a0\u9501\u51b3\u7b56\u53d1\u751f\u5728\u626b\u63cf\u65f6\u3001\u4e0e\u662f\u5426\u547d\u4e2d\u65e0\u5173，\u201c\u6ca1\u66f4\u65b0\u5230\u6570\u636e"\u4e0d\u7b49\u4e8e"\u6ca1\u9501\u6570\u636e\u201d\u3002\u628a RR \u4e24\u884c\u4e0e RC \u4e24\u884c\u5bf9\u7167，\u5c31\u7406\u89e3\u4e86\u4e3a\u4ec0\u4e48\u7535\u5546\u5927\u4fc3\u524d\u4f1a\u6709\u56e2\u961f\u628a\u70ed\u70b9\u8868\u4e8b\u52a1\u4e34\u65f6\u964d\u7ea7 RC：\u95f4\u9699\u9501\u6ca1\u4e86，\u63d2\u5165\u901a\u9053\u5168\u5f00，\u4ee3\u4ef7\u662f\u5e7b\u8bfb\u9632\u7ebf\u4ea4\u7ed9\u5e94\u7528\u5c42\u7684 FOR UPDATE\u3002<\/p>\n

\u5de5\u7a0b\u5316\u6539\u8fdb<\/h3>\n

\u7b2c\u4e00\u6b65，\u8ba9\u6bcf\u4e00\u6b21\u52a0\u9501\u90fd\u8d70\u7d22\u5f15\u7b49\u503c\u3002UPDATE\/DELETE \u7684 WHERE \u5fc5\u987b\u547d\u4e2d\u4e3b\u952e\u3001\u552f\u4e00\u952e\u6216\u9ad8\u9009\u62e9\u6027\u4e8c\u7ea7\u7d22\u5f15\u2014\u2014\u8fd9\u51b3\u5b9a\u9501\u96c6\u5408\u662f"\u4e00\u4e2a\u8bb0\u5f55\u9501"\u8fd8\u662f"\u5168\u8868 next-key"\u3002\u4e0a\u7ebf\u524d\u7528 EXPLAIN \u5ba1\u5199\u8bed\u53e5，type=ALL \u7684 UPDATE \u76f4\u63a5\u6253\u56de；\u5e94\u7528\u5c42\u4fdd\u7559 sql_safe_updates=1（WHERE \u65e0\u952e\u5217\u7981\u6b62\u6539\u5220）\u4f5c\u4e3a\u6700\u540e\u62a4\u680f，DBA \u4f1a\u8bdd\u53ef\u5173\u4f46\u8981\u663e\u5f0f\u58f0\u660e\u3002\u4e8b\u52a1\u5185\u6bcf\u591a\u6301\u6709\u4e00\u628a\u9501\u3001\u6bcf\u591a\u505c\u7559\u4e00\u79d2，\u7b49\u5f85\u56fe\u4e0a\u7684\u73af\u5c31\u591a\u4e00\u4e2a\u4e58\u6570\u2014\u2014\u77ed\u4e8b\u52a1\u662f\u9501\u95ee\u9898\u7684\u4e00\u534a\u7b54\u6848\u3002<\/p>\n

\u7b2c\u4e8c\u6b65，\u5168\u5e93\u7edf\u4e00\u52a0\u9501\u987a\u5e8f，\u628a\u6b7b\u9501\u627c\u6740\u5728\u534f\u8bae\u5c42\u3002\u6b7b\u9501\u7684\u6784\u6210\u8981\u4ef6\u662f"\u4ea4\u9519\u7533\u8bf7"，\u540c\u4e00\u6279\u884c\u7684\u52a0\u9501\u6b21\u5e8f\u53ea\u8981\u5168\u5c40\u4e00\u81f4\u5c31\u6210\u4e0d\u4e86\u73af：\u6279\u91cf\u64cd\u4f5c\u5148 ORDER BY id \u518d\u9010\u884c\u5904\u7406；\u8de8\u8868\u4e8b\u52a1\u6309\u56fa\u5b9a\u7684\u8868\u5e8f（\u5982\u5b57\u5178\u5e8f）\u8bbf\u95ee；\u5148\u9501\u7236\u884c\u518d\u9501\u5b50\u884c\u7684\u7ea6\u5b9a\u5199\u8fdb\u56e2\u961f\u89c4\u8303\u3002\u5bf9 8.0.22 \u540e\u7684"\u81ea\u52a8\u63d0\u4ea4\u4e0b\u52a0\u9501\u987a\u5e8f\u7531\u6267\u884c\u8ba1\u5212\u51b3\u5b9a"\u4e5f\u8981\u76ef\u4f4f：\u540c\u4e00\u6bb5\u4ee3\u7801\u4e24\u6761 SQL \u7684\u6267\u884c\u8ba1\u5212\u56e0\u7edf\u8ba1\u4fe1\u606f\u6f02\u79fb\u4e92\u6362，\u9501\u5e8f\u5c31\u53d8\u4e86，\u6b7b\u9501"\u7a81\u7136\u590d\u73b0"\u5e38\u6e90\u4e8e\u6b64\u3002<\/p>\n

\u7b2c\u4e09\u6b65，\u628a\u53d6\u8bc1\u94fe\u56fa\u5316\u6210 SOP\u3002\u7b2c\u4e00\u73b0\u573a\u67e5 sys.innodb_lock_waits：\u4e00\u5f20\u89c6\u56fe\u76f4\u63a5\u7ed9\u51fa blocking_pid \u4e0e waiting_pid\u3001\u53cc\u65b9 SQL，1205 \u7c7b\u95ee\u9898\u4e94\u79d2\u5b9a\u4f4d\u5835\u6e90\u5934；\u7ec6\u7c92\u5ea6\u9501\u5bf9\u8c61\u770b performance_schema.data_locks（\u9501\u7c7b\u578b\u3001\u7d22\u5f15\u540d\u3001\u9501\u7684\u8303\u56f4 LOCK_RANGE \u8fb9\u754c），8.0 \u5df2\u53d6\u4ee3\u65e7\u7684 information_schema.INNODB_LOCKS\u3002\u6b7b\u9501\u5219\u770b SHOW ENGINE INNODB STATUS \u7684 LATEST DETECTED DEADLOCK \u6bb5\u2014\u2014\u5b83\u53ea\u7559\u6700\u8fd1\u4e00\u6b21\u4e14\u968f\u65f6\u88ab\u8986\u76d6，\u6240\u4ee5\u5168\u5c40\u5f00 innodb_print_all_deadlocks=ON \u628a\u6bcf\u6b21\u6b7b\u9501\u843d\u9519\u8bef\u65e5\u5fd7，\u4e8b\u540e\u6309\u6bb5\u5206\u6790：\u627e WAITING FOR … HOLDS THE LOCK \u4e24\u884c，\u9501\u5bf9\u8c61\u4e0e\u8bed\u53e5\u4e00\u76ee\u4e86\u7136\u3002<\/p>\n

\u7b2c\u56db\u6b65，\u70ed\u70b9\u884c\u5355\u72ec\u505a\u6d41\u91cf\u6574\u5f62\u3002\u79d2\u6740\u6263\u51cf\u8fd9\u7c7b"\u5343\u519b\u4e07\u9a6c\u8fc7\u540c\u4e00\u628a\u884c\u9501"\u7684\u573a\u666f，\u4e89\u7528\u6210\u672c\u5728\u6392\u961f\u4e0e\u6b7b\u9501\u68c0\u6d4b\u4e0a\u53cc\u91cd\u653e\u5927：\u9996\u9009\u628a"\u4e00\u5356\u4e00\u9000"\u5408\u5e76\u2014\u2014\u5e94\u7528\u5c42\u6512\u6279，\u4e00\u6761 UPDATE stock=stock-? WHERE id=? \u5e26\u805a\u5408\u589e\u91cf，\u628a\u5343\u6b21\u52a0\u9501\u5408\u6210\u4e00\u6b21；\u6216\u628a\u8ba1\u6570\u524d\u79fb\u5230 Redis\/\u4e2d\u95f4\u961f\u5217，MySQL \u843d\u6700\u7ec8\u503c\u3002\u786e\u6709\u5927\u91cf\u77ac\u65f6\u6392\u961f\u4e14\u4e1a\u52a1\u63a5\u53d7\u8d85\u65f6\u5931\u8d25\u65f6，\u53ef\u8bc4\u4f30 innodb_deadlock_detect=OFF + innodb_lock_wait_timeout \u538b\u5230 1~2 \u79d2，\u7528\u5feb\u901f\u5931\u8d25\u66ff\u4ee3\u68c0\u6d4b\u5f00\u9500\u2014\u2014\u6b64\u914d\u7f6e\u4f1a\u653e\u5927"\u771f\u6b7b\u9501\u9760\u8d85\u65f6\u6162\u6162\u7b49"，\u5fc5\u987b\u914d\u5408\u5e94\u7528\u5c42\u91cd\u8bd5\u4e0e\u5145\u5206\u538b\u6d4b，\u4ec5\u9650\u70ed\u70b9\u4e89\u7528\u5355\u4e00\u7684\u5e93\u3002<\/p>\n

\u7b2c\u4e8c\u6b21\u4ee3\u7801\u5b9e\u9a8c\u53ca\u8f93\u51fa<\/h3>\n

\u6b7b\u9501\u7684\u6700\u5c0f\u5b8c\u6574\u673a\u5236\u6a21\u578b（\u786e\u5b9a\u6027\u540c\u6b65\u56de\u653e）：LockMgr \u7ef4\u62a4\u8d44\u6e90\u6301\u6709\u8005\u4e0e\u7b49\u5f85\u961f\u5217，\u6bcf\u6b21\u5165\u961f\u5728\u7b49\u5f85\u56fe\u4e0a\u505a BFS \u627e\u73af；\u6210\u73af\u5373\u6309"undo \u5c0f\u8005\u4f18\u5148\u727a\u7272"\u56de\u6eda\u5e76\u91ca\u653e\u5176\u9501，\u961f\u5217\u81ea\u52a8\u63a8\u8fdb\u3002\u573a\u666f\u4e00\u662f\u4ea4\u9519\u52a0\u9501（A:1\u21922，B:2\u21921），\u573a\u666f\u4e8c\u628a B \u7684\u7533\u8bf7\u987a\u5e8f\u6539\u6210\u4e0e A \u4e00\u81f4（\u90fd\u5347\u5e8f）\u3002<\/p>\n

# \u9501\u7b49\u5f85\u56fe + \u6b7b\u9501\u68c0\u6d4b(\u540c\u6b65\u56de\u653e\u6a21\u578b): \u8d44\u6e90\u72ec\u5360 X \u9501, \u649e\u9501\u5373\u6392\u961f (waiter, res)<\/span>
\n# \u6bcf\u6b21\u5165\u961f\u540e\u5168\u56fe\u627e\u73af; \u6210\u73af\u5219\u56de\u6eda undo \u91cf\u5c0f\u7684\u4e00\u65b9(InnoDB trx_weight \u53d6\u820d\u7684\u8fd1\u4f3c)<\/span><\/p>\n

class<\/span> LockMgr<\/span>:<\/span>
\n def<\/span> __init__<\/span>(<\/span>self,<\/span> undo)<\/span>:<\/span>
\n self.<\/span>holder,<\/span> self.<\/span>waits,<\/span> self.<\/span>undo =<\/span> {<\/span>}<\/span>,<\/span> [<\/span>]<\/span>,<\/span> dict<\/span>(<\/span>undo)<\/span>
\n self.<\/span>killed =<\/span> set<\/span>(<\/span>)<\/span> # \u88ab\u6b7b\u9501\u56de\u6eda\u7684\u4e8b\u52a1<\/span><\/p>\n

def<\/span> request<\/span>(<\/span>self,<\/span> trx,<\/span> res)<\/span>:<\/span>
\n cur =<\/span> self.<\/span>holder.<\/span>get(<\/span>res)<\/span>
\n if<\/span> cur in<\/span> (<\/span>None<\/span>,<\/span> trx)<\/span>:<\/span>
\n self.<\/span>holder[<\/span>res]<\/span> =<\/span> trx
\n print<\/span>(<\/span>" %s \u62ff\u5230 %s \u7684 X \u9501"<\/span> %<\/span> (<\/span>trx,<\/span> res)<\/span>)<\/span>
\n return<\/span> True<\/span>
\n self.<\/span>waits.<\/span>append(<\/span>(<\/span>trx,<\/span> res)<\/span>)<\/span>
\n print<\/span>(<\/span>" %s \u8bf7\u6c42 %s: \u88ab %s \u6301\u6709, \u5165\u961f\u7b49\u5f85"<\/span> %<\/span> (<\/span>trx,<\/span> res,<\/span> cur)<\/span>)<\/span>
\n return<\/span> False<\/span><\/p>\n

def<\/span> advance<\/span>(<\/span>self)<\/span>:<\/span>
\n moved =<\/span> True<\/span>
\n while<\/span> moved:<\/span>
\n moved =<\/span> False<\/span>
\n for<\/span> pair in<\/span> list<\/span>(<\/span>self.<\/span>waits)<\/span>:<\/span>
\n trx,<\/span> res =<\/span> pair
\n if<\/span> self.<\/span>holder.<\/span>get(<\/span>res)<\/span> is<\/span> None<\/span>:<\/span>
\n self.<\/span>waits.<\/span>remove(<\/span>pair)<\/span>
\n self.<\/span>holder[<\/span>res]<\/span> =<\/span> trx
\n print<\/span>(<\/span>" %s \u83b7\u9501: %s \u5df2\u91ca\u653e"<\/span> %<\/span> (<\/span>trx,<\/span> res)<\/span>)<\/span>
\n moved =<\/span> True<\/span><\/p>\n

def<\/span> release<\/span>(<\/span>self,<\/span> trx,<\/span> how)<\/span>:<\/span>
\n owned =<\/span> [<\/span>r for<\/span> r,<\/span> h in<\/span> self.<\/span>holder.<\/span>items(<\/span>)<\/span> if<\/span> h ==<\/span> trx]<\/span>
\n for<\/span> res in<\/span> owned:<\/span>
\n del<\/span> self.<\/span>holder[<\/span>res]<\/span>
\n self.<\/span>waits =<\/span> [<\/span>p for<\/span> p in<\/span> self.<\/span>waits if<\/span> p[<\/span>0<\/span>]<\/span> !=<\/span> trx]<\/span>
\n print<\/span>(<\/span>" %s %s, \u91ca\u653e %d \u9879\u9501"<\/span> %<\/span> (<\/span>trx,<\/span> how,<\/span> len<\/span>(<\/span>owned)<\/span>)<\/span>)<\/span>
\n if<\/span> "\u56de\u6eda"<\/span> in<\/span> how:<\/span>
\n self.<\/span>killed.<\/span>add(<\/span>trx)<\/span>
\n self.<\/span>advance(<\/span>)<\/span><\/p>\n

def<\/span> detect<\/span>(<\/span>self)<\/span>:<\/span>
\n adj =<\/span> {<\/span>}<\/span>
\n for<\/span> waiter,<\/span> res in<\/span> self.<\/span>waits:<\/span>
\n adj.<\/span>setdefault(<\/span>waiter,<\/span> set<\/span>(<\/span>)<\/span>)<\/span>.<\/span>add(<\/span>self.<\/span>holder[<\/span>res]<\/span>)<\/span>
\n for<\/span> start in<\/span> sorted<\/span>(<\/span>adj)<\/span>:<\/span>
\n frontier,<\/span> seen =<\/span> [<\/span>(<\/span>start,<\/span> [<\/span>start]<\/span>)<\/span>]<\/span>,<\/span> {<\/span>start}<\/span>
\n while<\/span> frontier:<\/span>
\n node,<\/span> path =<\/span> frontier.<\/span>pop(<\/span>0<\/span>)<\/span>
\n for<\/span> nxt in<\/span> sorted<\/span>(<\/span>adj.<\/span>get(<\/span>node,<\/span> (<\/span>)<\/span>)<\/span>)<\/span>:<\/span>
\n if<\/span> nxt ==<\/span> start:<\/span>
\n print<\/span>(<\/span>" !! \u7b49\u5f85\u56fe\u6210\u73af: %s"<\/span> %<\/span> " -> "<\/span>.<\/span>join(<\/span>path +<\/span> [<\/span>start]<\/span>)<\/span>)<\/span>
\n victim =<\/span> min<\/span>(<\/span>set<\/span>(<\/span>path)<\/span>,<\/span> key=<\/span>lambda<\/span> t:<\/span> self.<\/span>undo[<\/span>t]<\/span>)<\/span>
\n print<\/span>(<\/span>" !! LATEST DETECTED DEADLOCK: \u727a\u7272\u8005 %s (undo=%d \u6743\u91cd\u5c0f)"<\/span>
\n %<\/span> (<\/span>victim,<\/span> self.<\/span>undo[<\/span>victim]<\/span>)<\/span>)<\/span>
\n self.<\/span>release(<\/span>victim,<\/span> "\u56de\u6eda(errno 1213)"<\/span>)<\/span>
\n return<\/span> victim
\n if<\/span> nxt not<\/span> in<\/span> seen:<\/span>
\n seen.<\/span>add(<\/span>nxt)<\/span>
\n frontier.<\/span>append(<\/span>(<\/span>nxt,<\/span> path +<\/span> [<\/span>nxt]<\/span>)<\/span>)<\/span>
\n return<\/span> None<\/span><\/p>\n

def<\/span> commit<\/span>(<\/span>self,<\/span> trx)<\/span>:<\/span>
\n self.<\/span>release(<\/span>trx,<\/span> "\u63d0\u4ea4"<\/span>)<\/span><\/p>\n

def<\/span> run<\/span>(<\/span>title,<\/span> steps,<\/span> undo)<\/span>:<\/span>
\n print<\/span>(<\/span>title)<\/span>
\n lm =<\/span> LockMgr(<\/span>undo)<\/span>
\n for<\/span> trx,<\/span> res in<\/span> steps:<\/span>
\n lm.<\/span>request(<\/span>trx,<\/span> res)<\/span>
\n lm.<\/span>detect(<\/span>)<\/span>
\n for<\/span> trx in<\/span> (<\/span>"A"<\/span>,<\/span> "B"<\/span>)<\/span>:<\/span>
\n if<\/span> trx not<\/span> in<\/span> lm.<\/span>killed:<\/span>
\n lm.<\/span>commit(<\/span>trx)<\/span>
\n for<\/span> trx in<\/span> sorted<\/span>(<\/span>lm.<\/span>killed)<\/span>:<\/span>
\n print<\/span>(<\/span>" \u5e94\u7528\u5c42\u6355\u83b7 1213, %s \u91cd\u8bd5\u6574\u7b14\u4e8b\u52a1: \u6b64\u65f6\u65e0\u4eba\u5728\u9014, \u987a\u5e8f\u62ff\u9501\u6210\u529f"<\/span> %<\/span> trx)<\/span><\/p>\n

run(<\/span>"\u573a\u666f\u4e00(\u4ea4\u9519\u52a0\u9501): A \u9501 1 \u540e\u8981 2, B \u9501 2 \u540e\u8981 1"<\/span>,<\/span>
\n [<\/span>(<\/span>"A"<\/span>,<\/span> "id=1"<\/span>)<\/span>,<\/span> (<\/span>"B"<\/span>,<\/span> "id=2"<\/span>)<\/span>,<\/span> (<\/span>"A"<\/span>,<\/span> "id=2"<\/span>)<\/span>,<\/span> (<\/span>"B"<\/span>,<\/span> "id=1"<\/span>)<\/span>]<\/span>,<\/span>
\n {<\/span>"A"<\/span>:<\/span> 2<\/span>,<\/span> "B"<\/span>:<\/span> 7<\/span>}<\/span>)<\/span>
\nrun(<\/span>"\u573a\u666f\u4e8c(\u7edf\u4e00\u5347\u5e8f): A\u3001B \u90fd\u6309 id \u5347\u5e8f\u7533\u8bf7"<\/span>,<\/span>
\n [<\/span>(<\/span>"A"<\/span>,<\/span> "id=1"<\/span>)<\/span>,<\/span> (<\/span>"B"<\/span>,<\/span> "id=1"<\/span>)<\/span>,<\/span> (<\/span>"A"<\/span>,<\/span> "id=2"<\/span>)<\/span>,<\/span> (<\/span>"B"<\/span>,<\/span> "id=2"<\/span>)<\/span>]<\/span>,<\/span>
\n {<\/span>"A"<\/span>:<\/span> 2<\/span>,<\/span> "B"<\/span>:<\/span> 7<\/span>}<\/span>)<\/span><\/p>\n

\u8fd0\u884c\u8f93\u51fa：<\/p>\n

\u573a\u666f\u4e00(\u4ea4\u9519\u52a0\u9501): A \u9501 1 \u540e\u8981 2, B \u9501 2 \u540e\u8981 1
\n A \u62ff\u5230 id=1 \u7684 X \u9501
\n B \u62ff\u5230 id=2 \u7684 X \u9501
\n A \u8bf7\u6c42 id=2: \u88ab B \u6301\u6709, \u5165\u961f\u7b49\u5f85
\n B \u8bf7\u6c42 id=1: \u88ab A \u6301\u6709, \u5165\u961f\u7b49\u5f85
\n !! \u7b49\u5f85\u56fe\u6210\u73af: A -> B -> A
\n !! LATEST DETECTED DEADLOCK: \u727a\u7272\u8005 A (undo=2 \u6743\u91cd\u5c0f)
\n A \u56de\u6eda(errno 1213), \u91ca\u653e 1 \u9879\u9501
\n B \u83b7\u9501: id=1 \u5df2\u91ca\u653e
\n B \u63d0\u4ea4, \u91ca\u653e 2 \u9879\u9501
\n \u5e94\u7528\u5c42\u6355\u83b7 1213, A \u91cd\u8bd5\u6574\u7b14\u4e8b\u52a1: \u6b64\u65f6\u65e0\u4eba\u5728\u9014, \u987a\u5e8f\u62ff\u9501\u6210\u529f
\n\u573a\u666f\u4e8c(\u7edf\u4e00\u5347\u5e8f): A\u3001B \u90fd\u6309 id \u5347\u5e8f\u7533\u8bf7
\n A \u62ff\u5230 id=1 \u7684 X \u9501
\n B \u8bf7\u6c42 id=1: \u88ab A \u6301\u6709, \u5165\u961f\u7b49\u5f85
\n A \u62ff\u5230 id=2 \u7684 X \u9501
\n B \u8bf7\u6c42 id=2: \u88ab A \u6301\u6709, \u5165\u961f\u7b49\u5f85
\n A \u63d0\u4ea4, \u91ca\u653e 2 \u9879\u9501
\n B \u83b7\u9501: id=1 \u5df2\u91ca\u653e
\n B \u83b7\u9501: id=2 \u5df2\u91ca\u653e
\n B \u63d0\u4ea4, \u91ca\u653e 2 \u9879\u9501<\/p>\n

\u4e24\u4e2a\u573a\u666f\u7684\u4e2d\u95f4\u6001\u60ca\u4eba\u76f8\u4f3c\u2014\u2014\u90fd\u6709\u4e8b\u52a1"\u5165\u961f\u7b49\u5f85"\u2014\u2014\u4f46\u7ed3\u5c40\u5b8c\u5168\u4e0d\u540c：\u573a\u666f\u4e8c\u53ea\u662f\u6392\u961f（\u7b49\u5f85\u56fe\u662f B\u2192A \u7684\u5355\u5411\u94fe，\u65e0\u73af），\u63d0\u4ea4\u4e00\u6765\u4f9d\u6b21\u653e\u884c；\u573a\u666f\u4e00\u5728\u7b2c 4 \u6b65\u95ed\u5408\u6210\u73af，\u68c0\u6d4b\u5668\u7acb\u523b\u4ecb\u5165\u3002\u7b49\u5f85\u4e0d\u662f\u6b7b\u9501，\u73af\u624d\u662f，\u8fd9\u53e5\u8bdd\u51b3\u5b9a\u4e86\u6392\u969c\u65f6\u4f60\u8be5\u529d\u4eba\u7b49\u4e00\u4e0b\u8fd8\u662f\u8be5\u62c6\u4ee3\u7801\u3002\u53e6\u4e24\u4e2a\u53ef\u8fc1\u79fb\u7684\u7ec6\u8282：\u727a\u7272\u8005\u9009\u62e9\u770b\u6743\u91cd\u2014\u2014\u56de\u6eda undo \u5c11\u7684 A \u800c\u4e0d\u662f B，\u4e0e InnoDB"\u56de\u6eda\u4ee3\u4ef7\u6700\u5c0f"\u7684\u53d6\u5411\u4e00\u81f4，\u8fd9\u4e5f\u662f\u4e3a\u4ec0\u4e48\u751f\u4ea7\u6b7b\u9501\u65e5\u5fd7\u91cc\u88ab\u6740\u7684\u4e0d\u4e00\u5b9a\u662f\u8087\u4e8b\u66f4\u5927\u7684\u90a3\u4e2a；\u4ee5\u53ca\u56de\u6eda\u540e\u5fc5\u987b\u6574\u7b14\u91cd\u8bd5\u2014\u2014A \u7684\u4e24\u6761\u52a0\u9501\u5df2\u5168\u90e8\u5931\u6548，\u53ea\u91cd\u653e\u6700\u540e\u4e00\u6761 UPDATE \u4f1a\u9020\u6210\u4e1a\u52a1\u72b6\u6001\u6b8b\u7f3a，\u91cd\u8bd5\u903b\u8f91\u8981\u653e\u5728\u4e8b\u52a1\u8fb9\u754c\u4e0a，\u800c\u4e0d\u662f\u8bed\u53e5\u4e0a\u3002<\/p>\n

\u5e38\u89c1\u9677\u9631<\/h3>\n

\u5176\u4e00，\u65e0\u7d22\u5f15 WHERE \u7684 UPDATE \u9501\u5168\u8868：\u626b\u63cf\u8def\u5f84\u51b3\u5b9a\u9501\u96c6\u5408，type=ALL \u7684\u5199\u8bed\u53e5\u5728 RR \u4e0b\u7b49\u4e8e\u8868\u7ea7\u6392\u4ed6，\u4e00\u6761\u6162 UPDATE \u6302\u4f4f\u5168\u7ad9；\u5e94\u6025\u9760 kill \u963b\u585e\u6e90（sys.innodb_lock_waits \u6216 SELECT * FROM information_schema.innodb_trx ORDER BY trx_started LIMIT 1 \u627e\u6700\u65e9\u4e8b\u52a1），\u6839\u6cbb\u9760\u8865\u7d22\u5f15\u3002\u5176\u4e8c，\u628a 1205 \u5f53\u8fde\u63a5\u6c60\u95ee\u9898：\u8c03\u5927 innodb_lock_wait_timeout（\u9ed8\u8ba4 50 \u79d2）\u53ea\u662f\u8ba9\u6392\u961f\u66f4\u4e45\u3001\u96ea\u5d29\u66f4\u6162，\u6b63\u786e\u52a8\u4f5c\u662f\u67e5\u8c01\u6301\u6709\u9501\u3001\u9501\u8303\u56f4\u80fd\u4e0d\u80fd\u7f29\u5c0f\u3002\u5176\u4e09，INSERT … ON DUPLICATE KEY UPDATE \u4e0e INSERT INTO … SELECT \u5728 RR \u4e0b\u7684\u95f4\u9699\u9501：\u91cd\u590d\u952e\u68c0\u67e5\u4f1a\u7559\u4e0b next-key\/gap，\u591a\u4e2a\u5e76\u53d1 upsert \u4e92\u9501\u95f4\u9699\u518d\u5404\u81ea\u63d2\u5165\u610f\u5411\u649e\u8fdb\u53bb，\u662f\u6b7b\u9501\u65e5\u5fd7\u91cc\u7684\u5e38\u5ba2\u2014\u2014\u70ed\u70b9\u573a\u666f\u6539"\u5148 UPDATE \u65e0\u679c\u518d INSERT"\u5e76\u5bb9\u5fcd 1062，\u6216\u964d\u7ea7 RC\u3002\u5176\u56db，\u6b7b\u9501\u65e5\u5fd7\u53ea\u4fe1 SHOW ENGINE INNODB STATUS：\u90a3\u91cc\u53ea\u5b58\u6700\u8fd1\u4e00\u6b21，\u4e24\u6b21\u6b7b\u9501\u95f4\u9694\u77ed\u5c31\u6c38\u8fdc\u770b\u4e0d\u5230\u5168\u8c8c，innodb_print_all_deadlocks \u8981\u63d0\u524d\u5f00，\u4e8b\u540e\u7ffb\u9519\u8bef\u65e5\u5fd7\u3002\u5176\u4e94，\u4ee5\u4e3a\u95f4\u9699\u9501\u4f1a\u4e92\u76f8\u963b\u585e：gap \u4e0e gap \u76f8\u5bb9，\u4e24\u4e2a\u4e8b\u52a1\u7684 UPDATE \u90fd\u80fd"\u9501\u4f4f"\u540c\u4e00\u95f4\u9699\u518d\u5404\u63d2\u5404\u7684\u2014\u2014\u771f\u6b63\u6210\u73af\u7684\u5f80\u5f80\u662f\u53cc\u65b9\u968f\u540e\u5404\u6301\u4e00\u4e2a gap\u3001\u518d\u5404\u8981\u4e00\u4e2a\u63d2\u5165\u610f\u5411\u8fd9\u79cd\u56db\u4e0d\u50cf\u7ec4\u5408，\u8bfb\u9501\u65e5\u5fd7\u65f6\u6309\u9501\u7c7b\u578b\u5206\u5f00\u770b，\u522b\u88ab"\u9501\u4e86\u540c\u4e00\u4e2a\u5730\u65b9"\u7684\u8868\u8c61\u9a97\u3002<\/p>\n

\u843d\u5730\u6e05\u5355<\/h3>\n