{"id":54,"date":"2018-07-09T20:49:47","date_gmt":"2018-07-09T20:49:47","guid":{"rendered":"http:\/\/www.anthonylee.cn\/myblog\/?p=54"},"modified":"2018-12-04T13:49:47","modified_gmt":"2018-12-04T05:49:47","slug":"mysql%e4%b8%ad%e4%b9%90%e8%a7%82%e9%94%81%e3%80%81%e6%82%b2%e8%a7%82%e9%94%81%e3%80%81%e5%85%b1%e4%ba%ab%e9%94%81%e3%80%81%e6%8e%92%e5%ae%83%e9%94%81%e3%80%81%e8%a1%8c%e9%94%81%e3%80%81%e8%a1%a8","status":"publish","type":"post","link":"http:\/\/www.anthonylee.cn\/myblog\/?p=54","title":{"rendered":"mysql\u4e2d\u4e50\u89c2\u9501\u3001\u60b2\u89c2\u9501\u3001\u5171\u4eab\u9501\u3001\u6392\u5b83\u9501\u3001\u884c\u9501\u3001\u8868\u9501\u6982\u5ff5\u603b\u7ed3"},"content":{"rendered":"<p>\u6211\u4eec\u5728\u64cd\u4f5c\u6570\u636e\u5e93\u7684\u65f6\u5019\uff0c\u53ef\u80fd\u4f1a\u7531\u4e8e\u5e76\u53d1\u95ee\u9898\u800c\u5f15\u8d77\u7684\u6570\u636e\u7684\u4e0d\u4e00\u81f4\u6027\uff08\u6570\u636e\u51b2\u7a81\uff09<\/p>\n<p>\u4e50\u89c2\u9501<\/p>\n<p>\u4e50\u89c2\u9501\u4e0d\u662f\u6570\u636e\u5e93\u81ea\u5e26\u7684\uff0c\u9700\u8981\u6211\u4eec\u81ea\u5df1\u53bb\u5b9e\u73b0\u3002\u4e50\u89c2\u9501\u662f\u6307\u64cd\u4f5c\u6570\u636e\u5e93\u65f6(\u66f4\u65b0\u64cd\u4f5c)\uff0c\u60f3\u6cd5\u5f88\u4e50\u89c2\uff0c\u8ba4\u4e3a\u8fd9\u6b21\u7684\u64cd\u4f5c\u4e0d\u4f1a\u5bfc\u81f4\u51b2\u7a81\uff0c\u5728\u64cd\u4f5c\u6570\u636e\u65f6\uff0c\u5e76\u4e0d\u8fdb\u884c\u4efb\u4f55\u5176\u4ed6\u7684\u7279\u6b8a\u5904\u7406\uff08\u4e5f\u5c31\u662f\u4e0d\u52a0\u9501\uff09\uff0c\u800c\u5728\u8fdb\u884c\u66f4\u65b0\u540e\uff0c\u518d\u53bb\u5224\u65ad\u662f\u5426\u6709\u51b2\u7a81\u4e86\u3002<\/p>\n<p>\u901a\u5e38\u5b9e\u73b0\u662f\u8fd9\u6837\u7684\uff1a\u5728\u8868\u4e2d\u7684\u6570\u636e\u8fdb\u884c\u64cd\u4f5c\u65f6(\u66f4\u65b0)\uff0c\u5148\u7ed9\u6570\u636e\u8868\u52a0\u4e00\u4e2a\u7248\u672c(version)\u5b57\u6bb5\uff0c\u6bcf\u64cd\u4f5c\u4e00\u6b21\uff0c\u5c06\u90a3\u6761\u8bb0\u5f55\u7684\u7248\u672c\u53f7\u52a01\u3002\u4e5f\u5c31\u662f\u5148\u67e5\u8be2\u51fa\u90a3\u6761\u8bb0\u5f55\uff0c\u83b7\u53d6\u51faversion\u5b57\u6bb5,\u5982\u679c\u8981\u5bf9\u90a3\u6761\u8bb0\u5f55\u8fdb\u884c\u64cd\u4f5c(\u66f4\u65b0),\u5219\u5148\u5224\u65ad\u6b64\u523bversion\u7684\u503c\u662f\u5426\u4e0e\u521a\u521a\u67e5\u8be2\u51fa\u6765\u65f6\u7684version\u7684\u503c\u76f8\u7b49\uff0c\u5982\u679c\u76f8\u7b49\uff0c\u5219\u8bf4\u660e\u8fd9\u6bb5\u671f\u95f4\uff0c\u6ca1\u6709\u5176\u4ed6\u7a0b\u5e8f\u5bf9\u5176\u8fdb\u884c\u64cd\u4f5c\uff0c\u5219\u53ef\u4ee5\u6267\u884c\u66f4\u65b0\uff0c\u5c06version\u5b57\u6bb5\u7684\u503c\u52a01\uff1b\u5982\u679c\u66f4\u65b0\u65f6\u53d1\u73b0\u6b64\u523b\u7684version\u503c\u4e0e\u521a\u521a\u83b7\u53d6\u51fa\u6765\u7684version\u7684\u503c\u4e0d\u76f8\u7b49\uff0c\u5219\u8bf4\u660e\u8fd9\u6bb5\u671f\u95f4\u5df2\u7ecf\u6709\u5176\u4ed6\u7a0b\u5e8f\u5bf9\u5176\u8fdb\u884c\u64cd\u4f5c\u4e86\uff0c\u5219\u4e0d\u8fdb\u884c\u66f4\u65b0\u64cd\u4f5c\u3002<\/p>\n<p>\u4e3e\u4f8b\uff1a<\/p>\n<p>\u4e0b\u5355\u64cd\u4f5c\u5305\u62ec3\u6b65\u9aa4\uff1a<\/p>\n<p>1.\u67e5\u8be2\u51fa\u5546\u54c1\u4fe1\u606f<\/p>\n<p>select (status,status,version) from t_goods where id=#{id}<\/p>\n<p>2.\u6839\u636e\u5546\u54c1\u4fe1\u606f\u751f\u6210\u8ba2\u5355<\/p>\n<p>3.\u4fee\u6539\u5546\u54c1status\u4e3a2<\/p>\n<p>update t_goods<\/p>\n<p>set status=2,version=version+1<\/p>\n<p>where id=#{id} and version=#{version};<\/p>\n<p>\u9664\u4e86\u81ea\u5df1\u624b\u52a8\u5b9e\u73b0\u4e50\u89c2\u9501\u4e4b\u5916\uff0c\u73b0\u5728\u7f51\u4e0a\u8bb8\u591a\u6846\u67b6\u5df2\u7ecf\u5c01\u88c5\u597d\u4e86\u4e50\u89c2\u9501\u7684\u5b9e\u73b0\uff0c\u5982hibernate\uff0c\u9700\u8981\u65f6\uff0c\u53ef\u80fd\u81ea\u884c\u641c\u7d22&#8221;hiberate \u4e50\u89c2\u9501&#8221;\u8bd5\u8bd5\u770b\u3002<\/p>\n<p>\u60b2\u89c2\u9501<\/p>\n<p>\u4e0e\u4e50\u89c2\u9501\u76f8\u5bf9\u5e94\u7684\u5c31\u662f\u60b2\u89c2\u9501\u4e86\u3002\u60b2\u89c2\u9501\u5c31\u662f\u5728\u64cd\u4f5c\u6570\u636e\u65f6\uff0c\u8ba4\u4e3a\u6b64\u64cd\u4f5c\u4f1a\u51fa\u73b0\u6570\u636e\u51b2\u7a81\uff0c\u6240\u4ee5\u5728\u8fdb\u884c\u6bcf\u6b21\u64cd\u4f5c\u65f6\u90fd\u8981\u901a\u8fc7\u83b7\u53d6\u9501\u624d\u80fd\u8fdb\u884c\u5bf9\u76f8\u540c\u6570\u636e\u7684\u64cd\u4f5c\uff0c\u8fd9\u70b9\u8ddfjava\u4e2d\u7684synchronized\u5f88\u76f8\u4f3c\uff0c\u6240\u4ee5\u60b2\u89c2\u9501\u9700\u8981\u8017\u8d39\u8f83\u591a\u7684\u65f6\u95f4\u3002\u53e6\u5916\u4e0e\u4e50\u89c2\u9501\u76f8\u5bf9\u5e94\u7684\uff0c\u60b2\u89c2\u9501\u662f\u7531\u6570\u636e\u5e93\u81ea\u5df1\u5b9e\u73b0\u4e86\u7684\uff0c\u8981\u7528\u7684\u65f6\u5019\uff0c\u6211\u4eec\u76f4\u63a5\u8c03\u7528\u6570\u636e\u5e93\u7684\u76f8\u5173\u8bed\u53e5\u5c31\u53ef\u4ee5\u4e86\u3002<\/p>\n<p>\u8bf4\u5230\u8fd9\u91cc\uff0c\u7531\u60b2\u89c2\u9501\u6d89\u53ca\u5230\u7684\u53e6\u5916\u4e24\u4e2a\u9501\u6982\u5ff5\u5c31\u51fa\u6765\u4e86\uff0c\u5b83\u4eec\u5c31\u662f\u5171\u4eab\u9501\u4e0e\u6392\u5b83\u9501\u3002\u5171\u4eab\u9501\u548c\u6392\u5b83\u9501\u662f\u60b2\u89c2\u9501\u7684\u4e0d\u540c\u7684\u5b9e\u73b0\uff0c\u5b83\u4fe9\u90fd\u5c5e\u4e8e\u60b2\u89c2\u9501\u7684\u8303\u7574\u3002<\/p>\n<p>\u5171\u4eab\u9501<\/p>\n<p>\u5171\u4eab\u9501\u6307\u7684\u5c31\u662f\u5bf9\u4e8e\u591a\u4e2a\u4e0d\u540c\u7684\u4e8b\u52a1\uff0c\u5bf9\u540c\u4e00\u4e2a\u8d44\u6e90\u5171\u4eab\u540c\u4e00\u4e2a\u9501\u3002\u76f8\u5f53\u4e8e\u5bf9\u4e8e\u540c\u4e00\u628a\u95e8\uff0c\u5b83\u62e5\u6709\u591a\u4e2a\u94a5\u5319\u4e00\u6837\u3002\u5c31\u50cf\u8fd9\u6837\uff0c\u4f60\u5bb6\u6709\u4e00\u4e2a\u5927\u95e8\uff0c\u5927\u95e8\u7684\u94a5\u5319\u6709\u597d\u51e0\u628a\uff0c\u4f60\u6709\u4e00\u628a\uff0c\u4f60\u5973\u670b\u53cb\u6709\u4e00\u628a\uff0c\u4f60\u4eec\u90fd\u53ef\u80fd\u901a\u8fc7\u8fd9\u628a\u94a5\u5319\u8fdb\u5165\u4f60\u4eec\u5bb6\uff0c\u8fdb\u53bb\u556a\u556a\u556a\u5565\u7684\uff0c\u4e00\u4e0b\u7406\u89e3\u4e86\u54c8\uff0c\u6ca1\u9519\uff0c\u8fd9\u4e2a\u5c31\u662f\u6240\u8c13\u7684\u5171\u4eab\u9501\u3002<\/p>\n<p>\u521a\u521a\u8bf4\u4e86\uff0c\u5bf9\u4e8e\u60b2\u89c2\u9501\uff0c\u4e00\u822c\u6570\u636e\u5e93\u5df2\u7ecf\u5b9e\u73b0\u4e86\uff0c\u5171\u4eab\u9501\u4e5f\u5c5e\u4e8e\u60b2\u89c2\u9501\u7684\u4e00\u79cd\uff0c\u90a3\u4e48\u5171\u4eab\u9501\u5728mysql\u4e2d\u662f\u901a\u8fc7\u4ec0\u4e48\u547d\u4ee4\u6765\u8c03\u7528\u5462\u3002\u901a\u8fc7\u67e5\u8be2\u8d44\u6599\uff0c\u4e86\u89e3\u5230\u901a\u8fc7\u5728\u6267\u884c\u8bed\u53e5\u540e\u9762\u52a0\u4e0alock in share mode\u5c31\u4ee3\u8868\u5bf9\u67d0\u4e9b\u8d44\u6e90\u52a0\u4e0a\u5171\u4eab\u9501\u4e86\u3002<\/p>\n<p>\u6bd4\u5982\uff0c\u6211\u8fd9\u91cc\u901a\u8fc7mysql\u6253\u5f00\u4e24\u4e2a\u67e5\u8be2\u7f16\u8f91\u5668\uff0c\u5728\u5176\u4e2d\u5f00\u542f\u4e00\u4e2a\u4e8b\u52a1\uff0c\u5e76\u4e0d\u6267\u884ccommit\u8bed\u53e5<\/p>\n<p>city\u8868DDL\u5982\u4e0b\uff1a<\/p>\n<p>CREATE TABLE `city` (<\/p>\n<p>`id` bigint(20) NOT NULL AUTO_INCREMENT,<\/p>\n<p>`name` varchar(255) DEFAULT NULL,<\/p>\n<p>`state` varchar(255) DEFAULT NULL,<\/p>\n<p>PRIMARY KEY (`id`)<\/p>\n<p>) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;<\/p>\n<div class=\"pgc-img\"><img src=\"http:\/\/p3.pstatp.com\/large\/pgc-image\/8db03a69c00745c1a2bd0e8b7af81235\" alt=\"mysql\u4e2d\u4e50\u89c2\u9501\u3001\u60b2\u89c2\u9501\u3001\u5171\u4eab\u9501\u3001\u6392\u5b83\u9501\u3001\u884c\u9501\u3001\u8868\u9501\u6982\u5ff5\u603b\u7ed3\" \/><\/div>\n<p>begin;<\/p>\n<p>SELECT * from city where id = &#8220;1&#8221; lock in share mode;<\/p>\n<p>\u7136\u540e\u5728\u53e6\u4e00\u4e2a\u67e5\u8be2\u7a97\u53e3\u4e2d\uff0c\u5bf9id\u4e3a1\u7684\u6570\u636e\u8fdb\u884c\u66f4\u65b0<\/p>\n<div class=\"pgc-img\"><img src=\"http:\/\/p1.pstatp.com\/large\/pgc-image\/0e17c5b48d77478093c92ab49ffaeb50\" alt=\"mysql\u4e2d\u4e50\u89c2\u9501\u3001\u60b2\u89c2\u9501\u3001\u5171\u4eab\u9501\u3001\u6392\u5b83\u9501\u3001\u884c\u9501\u3001\u8868\u9501\u6982\u5ff5\u603b\u7ed3\" \/><\/div>\n<p>update city set name=&#8221;666&#8243; where id =&#8221;1&#8243;;<\/p>\n<p>\u6b64\u65f6\uff0c\u64cd\u4f5c\u754c\u9762\u8fdb\u5165\u4e86\u5361\u987f\u72b6\u6001\uff0c\u8fc7\u51e0\u79d2\u540e\uff0c\u4e5f\u63d0\u793a\u9519\u8bef\u4fe1\u606f<\/p>\n<p>[SQL]update city set name=&#8221;666&#8243; where id =&#8221;1&#8243;;<\/p>\n<p>[Err] 1205 &#8211; Lock wait timeout exceeded; try restarting transaction<\/p>\n<p>\u90a3\u4e48\u8bc1\u660e\uff0c\u5bf9\u4e8eid=1\u7684\u8bb0\u5f55\u52a0\u9501\u6210\u529f\u4e86\uff0c\u5728\u4e0a\u4e00\u6761\u8bb0\u5f55\u8fd8\u6ca1\u6709commit\u4e4b\u524d\uff0c\u8fd9\u6761id=1\u7684\u8bb0\u5f55\u88ab\u9501\u4f4f\u4e86\uff0c\u53ea\u6709\u5728\u4e0a\u4e00\u4e2a\u4e8b\u52a1\u91ca\u653e\u6389\u9501\u540e\u624d\u80fd\u8fdb\u884c\u64cd\u4f5c\uff0c\u6216\u7528\u5171\u4eab\u9501\u624d\u80fd\u5bf9\u6b64\u6570\u636e\u8fdb\u884c\u64cd\u4f5c\u3002<\/p>\n<p>\u518d\u5b9e\u9a8c\u4e00\u4e0b\uff1a<\/p>\n<div class=\"pgc-img\"><img src=\"http:\/\/p3.pstatp.com\/large\/pgc-image\/a694956cef6740eea7b6b93314eeb7f8\" alt=\"mysql\u4e2d\u4e50\u89c2\u9501\u3001\u60b2\u89c2\u9501\u3001\u5171\u4eab\u9501\u3001\u6392\u5b83\u9501\u3001\u884c\u9501\u3001\u8868\u9501\u6982\u5ff5\u603b\u7ed3\" \/><\/div>\n<p>update city set name=&#8221;666&#8243; where id =&#8221;1&#8243; lock in share mode;<\/p>\n<p>[Err] 1064 &#8211; You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#8216;lock in share mode&#8217; at line 1<\/p>\n<p>\u52a0\u4e0a\u5171\u4eab\u9501\u540e\uff0c\u4e5f\u63d0\u793a\u9519\u8bef\u4fe1\u606f\u4e86\uff0c\u901a\u8fc7\u67e5\u8be2\u8d44\u6599\u624d\u77e5\u9053\uff0c\u5bf9\u4e8eupdate,insert,delete\u8bed\u53e5\u4f1a\u81ea\u52a8\u52a0\u6392\u5b83\u9501\u7684\u539f\u56e0<\/p>\n<p>\u4e8e\u662f\uff0c\u6211\u53c8\u8bd5\u4e86\u8bd5SELECT * from city where id = &#8220;1&#8221; lock in share mode;<\/p>\n<div class=\"pgc-img\"><img src=\"http:\/\/p3.pstatp.com\/large\/pgc-image\/087afca138044e6f89f8c74c4cf496a9\" alt=\"mysql\u4e2d\u4e50\u89c2\u9501\u3001\u60b2\u89c2\u9501\u3001\u5171\u4eab\u9501\u3001\u6392\u5b83\u9501\u3001\u884c\u9501\u3001\u8868\u9501\u6982\u5ff5\u603b\u7ed3\" \/><\/div>\n<p>\u8fd9\u4e0b\u6210\u529f\u4e86\u3002<\/p>\n<p>\u6392\u5b83\u9501<\/p>\n<p>\u6392\u5b83\u9501\u4e0e\u5171\u4eab\u9501\u76f8\u5bf9\u5e94\uff0c\u5c31\u662f\u6307\u5bf9\u4e8e\u591a\u4e2a\u4e0d\u540c\u7684\u4e8b\u52a1\uff0c\u5bf9\u540c\u4e00\u4e2a\u8d44\u6e90\u53ea\u80fd\u6709\u4e00\u628a\u9501\u3002<\/p>\n<p>\u4e0e\u5171\u4eab\u9501\u7c7b\u578b\uff0c\u5728\u9700\u8981\u6267\u884c\u7684\u8bed\u53e5\u540e\u9762\u52a0\u4e0afor update\u5c31\u53ef\u4ee5\u4e86<\/p>\n<p>\u884c\u9501<\/p>\n<p>\u884c\u9501\uff0c\u7531\u5b57\u9762\u610f\u601d\u7406\u89e3\uff0c\u5c31\u662f\u7ed9\u67d0\u4e00\u884c\u52a0\u4e0a\u9501\uff0c\u4e5f\u5c31\u662f\u4e00\u6761\u8bb0\u5f55\u52a0\u4e0a\u9501\u3002<\/p>\n<p>\u6bd4\u5982\u4e4b\u524d\u6f14\u793a\u7684\u5171\u4eab\u9501\u8bed\u53e5<\/p>\n<p>SELECT * from city where id = &#8220;1&#8221; lock in share mode;<\/p>\n<p>\u7531\u4e8e\u5bf9\u4e8ecity\u8868\u4e2d,id\u5b57\u6bb5\u4e3a\u4e3b\u952e\uff0c\u5c31\u4e5f\u76f8\u5f53\u4e8e\u7d22\u5f15\u3002\u6267\u884c\u52a0\u9501\u65f6\uff0c\u4f1a\u5c06id\u8fd9\u4e2a\u7d22\u5f15\u4e3a1\u7684\u8bb0\u5f55\u52a0\u4e0a\u9501\uff0c\u90a3\u4e48\u8fd9\u4e2a\u9501\u5c31\u662f\u884c\u9501\u3002<\/p>\n<p>\u8868\u9501<\/p>\n<p>\u8868\u9501\uff0c\u548c\u884c\u9501\u76f8\u5bf9\u5e94\uff0c\u7ed9\u8fd9\u4e2a\u8868\u52a0\u4e0a\u9501\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u6211\u4eec\u5728\u64cd\u4f5c\u6570\u636e\u5e93\u7684\u65f6\u5019\uff0c\u53ef\u80fd\u4f1a\u7531\u4e8e\u5e76\u53d1\u95ee\u9898\u800c\u5f15\u8d77\u7684\u6570\u636e\u7684\u4e0d\u4e00\u81f4\u6027\uff08\u6570\u636e\u51b2\u7a81\uff09 \u4e50\u89c2\u9501 \u4e50\u89c2\u9501\u4e0d\u662f\u6570\u636e&hellip; <a href=\"http:\/\/www.anthonylee.cn\/myblog\/?p=54\" class=\"more-link read-more\" rel=\"bookmark\">\u7ee7\u7eed\u9605\u8bfb <span class=\"screen-reader-text\">mysql\u4e2d\u4e50\u89c2\u9501\u3001\u60b2\u89c2\u9501\u3001\u5171\u4eab\u9501\u3001\u6392\u5b83\u9501\u3001\u884c\u9501\u3001\u8868\u9501\u6982\u5ff5\u603b\u7ed3<\/span><i class=\"fa fa-arrow-right\"><\/i><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[2],"tags":[],"_links":{"self":[{"href":"http:\/\/www.anthonylee.cn\/myblog\/index.php?rest_route=\/wp\/v2\/posts\/54"}],"collection":[{"href":"http:\/\/www.anthonylee.cn\/myblog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.anthonylee.cn\/myblog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.anthonylee.cn\/myblog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.anthonylee.cn\/myblog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=54"}],"version-history":[{"count":1,"href":"http:\/\/www.anthonylee.cn\/myblog\/index.php?rest_route=\/wp\/v2\/posts\/54\/revisions"}],"predecessor-version":[{"id":55,"href":"http:\/\/www.anthonylee.cn\/myblog\/index.php?rest_route=\/wp\/v2\/posts\/54\/revisions\/55"}],"wp:attachment":[{"href":"http:\/\/www.anthonylee.cn\/myblog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=54"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.anthonylee.cn\/myblog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=54"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.anthonylee.cn\/myblog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=54"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}