{"id":523,"date":"2021-07-28T20:38:06","date_gmt":"2021-07-28T12:38:06","guid":{"rendered":"https:\/\/egonlin.com\/?p=523"},"modified":"2025-06-12T16:45:05","modified_gmt":"2025-06-12T08:45:05","slug":"%e5%a4%9a%e7%89%88%e6%9c%ac%e5%b9%b6%e5%8f%91%e6%8e%a7%e5%88%b6mvcc","status":"publish","type":"post","link":"https:\/\/egonlin.com\/?p=523","title":{"rendered":"\u591a\u7248\u672c\u5e76\u53d1\u63a7\u5236MVCC"},"content":{"rendered":"

\u4e00\u00a0\u591a\u7248\u672c\u5e76\u53d1\u63a7\u5236MVCC\uff1aSnapshot Read vs Current Read<\/h1>\n

MySQL InnoDB\u5b58\u50a8\u5f15\u64ce\uff0c\u5b9e\u73b0\u7684\u662f\u57fa\u4e8e\u591a\u7248\u672c\u7684\u5e76\u53d1\u63a7\u5236\u534f\u8bae\u2014\u2014MVCC (Multi-Version Concurrency Control<\/a>) (\u6ce8\uff1a\u4e0eMVCC\u76f8\u5bf9\u7684\uff0c\u662f\u57fa\u4e8e\u9501\u7684\u5e76\u53d1\u63a7\u5236\uff0cLock-Based Concurrency Control)\u3002MVCC\u6700\u5927\u7684\u597d\u5904\uff0c\u76f8\u4fe1\u4e5f\u662f\u8033\u719f\u80fd\u8be6\uff1a\u8bfb\u4e0d\u52a0\u9501\uff0c\u8bfb\u5199\u4e0d\u51b2\u7a81<\/strong>\u3002\u5728\u8bfb\u591a\u5199\u5c11\u7684OLTP\u5e94\u7528\u4e2d\uff0c\u8bfb\u5199\u4e0d\u51b2\u7a81\u662f\u975e\u5e38\u91cd\u8981\u7684\uff0c\u6781\u5927\u7684\u589e\u52a0\u4e86\u7cfb\u7edf\u7684\u5e76\u53d1\u6027\u80fd\uff0c\u8fd9\u4e5f\u662f\u4e3a\u4ec0\u4e48\u73b0\u9636\u6bb5\uff0c\u51e0\u4e4e\u6240\u6709\u7684RDBMS\uff0c\u90fd\u652f\u6301\u4e86MVCC\u3002<\/p>\n

\u5728MVCC\u5e76\u53d1\u63a7\u5236\u4e2d\uff0c\u8bfb\u64cd\u4f5c\u53ef\u4ee5\u5206\u6210\u4e24\u7c7b\uff1a\u5feb\u7167\u8bfb (snapshot read)\u4e0e\u5f53\u524d\u8bfb (current read)<\/strong>\u3002\u5feb\u7167\u8bfb\uff0c\u8bfb\u53d6\u7684\u662f\u8bb0\u5f55\u7684\u53ef\u89c1\u7248\u672c (\u6709\u53ef\u80fd\u662f\u5386\u53f2\u7248\u672c)\uff0c\u4e0d\u7528\u52a0\u9501\u3002\u5f53\u524d\u8bfb\uff0c\u8bfb\u53d6\u7684\u662f\u8bb0\u5f55\u7684\u6700\u65b0\u7248\u672c\uff0c\u5e76\u4e14\uff0c\u5f53\u524d\u8bfb\u8fd4\u56de\u7684\u8bb0\u5f55\uff0c\u90fd\u4f1a\u52a0\u4e0a\u9501\uff0c\u4fdd\u8bc1\u5176\u4ed6\u4e8b\u52a1\u4e0d\u4f1a\u518d\u5e76\u53d1\u4fee\u6539\u8fd9\u6761\u8bb0\u5f55\u3002<\/p>\n

\u5728\u4e00\u4e2a\u652f\u6301MVCC\u5e76\u53d1\u63a7\u5236\u7684\u7cfb\u7edf\u4e2d\uff0c\u54ea\u4e9b\u8bfb\u64cd\u4f5c\u662f\u5feb\u7167\u8bfb\uff1f\u54ea\u4e9b\u64cd\u4f5c\u53c8\u662f\u5f53\u524d\u8bfb\u5462\uff1f\u4ee5MySQL InnoDB\u4e3a\u4f8b\uff1a<\/p>\n

\u5feb\u7167\u8bfb\uff1a<\/strong>\u7b80\u5355\u7684select\u64cd\u4f5c\uff0c\u5c5e\u4e8e\u5feb\u7167\u8bfb\uff0c\u4e0d\u52a0\u9501\u3002(\u5f53\u7136\uff0c\u4e5f\u6709\u4f8b\u5916\uff0c\u8fdb\u4e00\u6b65\u4e86\u89e3\uff0c\u8bf7\u70b9\u51fb<\/a>)<\/p>\n

select * from table where ?;<\/p>\n

\u00a0\u5f53\u524d\u8bfb\uff1a<\/strong>\u7279\u6b8a\u7684\u8bfb\u64cd\u4f5c\uff0c\u63d2\u5165\/\u66f4\u65b0\/\u5220\u9664\u64cd\u4f5c\uff0c\u5c5e\u4e8e\u5f53\u524d\u8bfb\uff0c\u9700\u8981\u52a0\u9501\u3002<\/p>\n

select * from table where ? lock in share mode;<\/p>\n

select * from table where ? for update;<\/p>\n

insert into table values (\u2026);<\/p>\n

update table set ? where ?;<\/p>\n

delete from table where ?;<\/p>\n

\u6240\u6709\u4ee5\u4e0a\u7684\u8bed\u53e5\uff0c\u90fd\u5c5e\u4e8e\u5f53\u524d\u8bfb\uff0c\u8bfb\u53d6\u8bb0\u5f55\u7684\u6700\u65b0\u7248\u672c\u3002\u5e76\u4e14\uff0c\u8bfb\u53d6\u4e4b\u540e\uff0c\u8fd8\u9700\u8981\u4fdd\u8bc1\u5176\u4ed6\u5e76\u53d1\u4e8b\u52a1\u4e0d\u80fd\u4fee\u6539\u5f53\u524d\u8bb0\u5f55\uff0c\u5bf9\u8bfb\u53d6\u8bb0\u5f55\u52a0\u9501\u3002\u5176\u4e2d\uff0c\u9664\u4e86\u7b2c\u4e00\u6761\u8bed\u53e5\uff0c\u5bf9\u8bfb\u53d6\u8bb0\u5f55\u52a0S\u9501 (\u5171\u4eab\u9501)\u5916<\/strong>\uff0c\u5176\u4ed6\u7684\u64cd\u4f5c\uff0c\u90fd\u52a0\u7684\u662fX\u9501 (\u6392\u5b83\u9501)\u3002<\/strong><\/p>\n

 <\/p>\n

\u4e3a\u4ec0\u4e48\u5c06 \u63d2\u5165\/\u66f4\u65b0\/\u5220\u9664 \u64cd\u4f5c\uff0c\u90fd\u5f52\u4e3a\u5f53\u524d\u8bfb\uff1f\u53ef\u4ee5\u770b\u770b\u4e0b\u9762\u8fd9\u4e2a \u66f4\u65b0 \u64cd\u4f5c\uff0c\u5728\u6570\u636e\u5e93\u4e2d\u7684\u6267\u884c\u6d41\u7a0b\uff1a

\"\"<\/div>
\"\"<\/div><\/p>\n

\u4ece\u56fe\u4e2d\uff0c\u53ef\u4ee5\u770b\u5230\uff0c\u4e00\u4e2aUpdate\u64cd\u4f5c\u7684\u5177\u4f53\u6d41\u7a0b\u3002\u5f53Update SQL\u88ab\u53d1\u7ed9MySQL\u540e\uff0cMySQL Server\u4f1a\u6839\u636ewhere\u6761\u4ef6\uff0c\u8bfb\u53d6\u7b2c\u4e00\u6761\u6ee1\u8db3\u6761\u4ef6\u7684\u8bb0\u5f55\uff0c\u7136\u540eInnoDB\u5f15\u64ce\u4f1a\u5c06\u7b2c\u4e00\u6761\u8bb0\u5f55\u8fd4\u56de\uff0c\u5e76\u52a0\u9501 (current read)\u3002\u5f85MySQL Server\u6536\u5230\u8fd9\u6761\u52a0\u9501\u7684\u8bb0\u5f55\u4e4b\u540e\uff0c\u4f1a\u518d\u53d1\u8d77\u4e00\u4e2aUpdate\u8bf7\u6c42\uff0c\u66f4\u65b0\u8fd9\u6761\u8bb0\u5f55\u3002\u4e00\u6761\u8bb0\u5f55\u64cd\u4f5c\u5b8c\u6210\uff0c\u518d\u8bfb\u53d6\u4e0b\u4e00\u6761\u8bb0\u5f55\uff0c\u76f4\u81f3\u6ca1\u6709\u6ee1\u8db3\u6761\u4ef6\u7684\u8bb0\u5f55\u4e3a\u6b62\u3002\u56e0\u6b64\uff0cUpdate\u64cd\u4f5c\u5185\u90e8\uff0c\u5c31\u5305\u542b\u4e86\u4e00\u4e2a\u5f53\u524d\u8bfb\u3002\u540c\u7406\uff0cDelete\u64cd\u4f5c\u4e5f\u4e00\u6837\u3002Insert\u64cd\u4f5c\u4f1a\u7a0d\u5fae\u6709\u4e9b\u4e0d\u540c\uff0c\u7b80\u5355\u6765\u8bf4\uff0c\u5c31\u662fInsert\u64cd\u4f5c\u53ef\u80fd\u4f1a\u89e6\u53d1Unique Key\u7684\u51b2\u7a81\u68c0\u67e5\uff0c\u4e5f\u4f1a\u8fdb\u884c\u4e00\u4e2a\u5f53\u524d\u8bfb\u3002<\/p>\n

\u6ce8<\/strong>\uff1a\u6839\u636e\u4e0a\u56fe\u7684\u4ea4\u4e92\uff0c\u9488\u5bf9\u4e00\u6761\u5f53\u524d\u8bfb\u7684SQL\u8bed\u53e5\uff0cInnoDB\u4e0eMySQL Server\u7684\u4ea4\u4e92\uff0c\u662f\u4e00\u6761\u4e00\u6761\u8fdb\u884c\u7684\uff0c\u56e0\u6b64\uff0c\u52a0\u9501\u4e5f\u662f\u4e00\u6761\u4e00\u6761\u8fdb\u884c\u7684\u3002\u5148\u5bf9\u4e00\u6761\u6ee1\u8db3\u6761\u4ef6\u7684\u8bb0\u5f55\u52a0\u9501\uff0c\u8fd4\u56de\u7ed9MySQL Server\uff0c\u505a\u4e00\u4e9bDML\u64cd\u4f5c\uff1b\u7136\u540e\u5728\u8bfb\u53d6\u4e0b\u4e00\u6761\u52a0\u9501\uff0c\u76f4\u81f3\u8bfb\u53d6\u5b8c\u6bd5\u3002<\/p>\n

\u8f6c\u81ea\uff1ahttps:\/\/www.cnblogs.com\/crazylqy\/p\/7611069.html<\/a><\/p>\n

\u4e8c MVCC\u539f\u7406\u5206\u6790<\/h1>\n

innodb \u591a\u7248\u672c\u5e76\u53d1\u63a7\u5236\u539f\u7406\u8be6\u89e3<\/a><\/p><\/blockquote>\n