{"id":235109,"date":"2022-02-08T09:54:05","date_gmt":"2022-02-08T01:54:05","guid":{"rendered":"https:\/\/lrxjmw.cn\/?p=235109"},"modified":"2022-01-29T15:54:56","modified_gmt":"2022-01-29T07:54:56","slug":"mysql-play-lize","status":"publish","type":"post","link":"https:\/\/lrxjmw.cn\/mysql-play-lize.html","title":{"rendered":"\u7b80\u5355\u4ecb\u7ecdMySQL\u6253\u5370\u6b7b\u9501\u65e5\u5fd7\u7684\u65b9\u6cd5"},"content":{"rendered":"
\u5bfc\u8bfb<\/td>\n | \u672c\u6587\u4e3b\u8981\u4ecb\u7ecd\u4e86MySQL\u6253\u5370\u6b7b\u9501\u65e5\u5fd7\u7684\u65b9\u6cd5\u6b65\u9aa4\uff0c\u6587\u4e2d\u901a\u8fc7\u793a\u4f8b\u4ee3\u7801\u4ecb\u7ecd\u7684\u975e\u5e38\u8be6\u7ec6\uff0c\u5177\u6709\u4e00\u5b9a\u7684\u53c2\u8003\u4ef7\u503c\uff0c\u611f\u5174\u8da3\u7684\u5c0f\u4f19\u4f34\u4eec\u53ef\u4ee5\u53c2\u8003\u4e00\u4e0b<\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \u524d\u8a00\uff1a<\/strong><\/div>\n \u5728 MySQL \u8fd0\u7ef4\u8fc7\u7a0b\u4e2d\uff0c\u96be\u514d\u4f1a\u9047\u5230 MySQL \u6b7b\u9501\u7684\u60c5\u51b5\uff0c\u4e00\u65e6\u7ebf\u4e0a\u4e1a\u52a1\u65e5\u6e10\u590d\u6742\uff0c\u5404\u79cd\u4e1a\u52a1\u64cd\u4f5c\u4e4b\u95f4\u5f80\u5f80\u4f1a\u4ea7\u751f\u9501\u51b2\u7a81\uff0c\u6709\u4e9b\u4f1a\u5bfc\u81f4\u6b7b\u9501\u5f02\u5e38\u3002\u8fd9\u79cd\u6b7b\u9501\u5f02\u5e38\u4e00\u822c\u8981\u5728\u7279\u5b9a\u65f6\u95f4\u7279\u5b9a\u6570\u636e\u548c\u7279\u5b9a\u4e1a\u52a1\u64cd\u4f5c\u624d\u4f1a\u590d\u73b0\uff0c\u6709\u65f6\u5019\u5904\u7406\u8d77\u6765\u6beb\u65e0\u5934\u7eea\uff0c\u4e00\u822c\u53ea\u80fd\u4ece\u6b7b\u9501\u65e5\u5fd7\u4e0b\u624b\u3002\u672c\u7bc7\u6587\u7ae0\u6211\u4eec\u4e00\u8d77\u6765\u770b\u4e0b MySQL \u7684\u6b7b\u9501\u65e5\u5fd7\u3002<\/p>\n 1.\u624b\u52a8\u6253\u5370\u6b7b\u9501\u65e5\u5fd7<\/strong><\/div>\n \u5f53\u4e1a\u52a1\u53d1\u751f\u6b7b\u9501\u65f6\uff0c\u9996\u5148\u662f\u7ebf\u4e0a\u9519\u8bef\u65e5\u5fd7\u62a5\u8b66\u53d1\u73b0\u6b7b\u9501\u5f02\u5e38\uff0c\u4e5f\u4f1a\u63d0\u793a\u4e00\u4e9b\u5806\u6808\u4fe1\u606f\uff0c\u7136\u540e\u4f1a\u53cd\u9988\u5230\u6570\u636e\u5e93\u5c42\u9762\u8fdb\u884c\u6392\u67e5\u3002\u6211\u4eec\u4e00\u822c\u4f1a\u5728\u547d\u4ee4\u884c\u6267\u884c show engine innodb status\\G \u6765\u8f93\u51fa\u6b7b\u9501\u65e5\u5fd7\uff0c\\G \u7684\u4f5c\u7528\u662f\u5c06\u67e5\u8be2\u5230\u7684\u7ed3\u679c\uff0c\u6bcf\u884c\u663e\u793a\u4e00\u4e2a\u5b57\u6bb5\u548c\u5b57\u6bb5\u503c\uff0c\u65b9\u4fbf\u67e5\u770b\u3002<\/p>\n show engine innodb status \u662f MySQL \u63d0\u4f9b\u7684\u4e00\u4e2a\u7528\u4e8e\u67e5\u770b innodb \u5f15\u64ce\u7cfb\u7edf\u4fe1\u606f\u7684\u5de5\u5177\u3002\u5b83\u4f1a\u8f93\u51fa\u5927\u91cf\u7684\u5185\u90e8\u4fe1\u606f\uff0c\u5185\u5bb9\u5206\u4e3a\u5f88\u591a\u5c0f\u6bb5\uff0c\u6bcf\u4e00\u6bb5\u5bf9\u5e94 innodb \u5b58\u50a8\u5f15\u64ce\u4e0d\u540c\u90e8\u5206\u7684\u4fe1\u606f\uff0c\u5176\u4e2d LATEST DETECTED DEADLOCK \u90e8\u5206\u663e\u793a\u7684\u6700\u8fd1\u4e00\u6b21\u7684\u6b7b\u9501\u4fe1\u606f\u3002<\/p>\n \u4e0b\u9762\u6211\u4eec\u624b\u52a8\u5236\u9020\u4e00\u6b21\u6b7b\u9501\uff0c\u6765\u770b\u4e00\u4e0b\u6b7b\u9501\u65e5\u5fd7\u76f8\u5173\u4fe1\u606f\uff1a<\/p>\n \r\n------------------------\r\nLATEST DETECTED DEADLOCK\r\n------------------------\r\n2021-11-10 17:03:10 0x7fb040672700\r\n*** (1) TRANSACTION:\r\nTRANSACTION 46913, ACTIVE 142 sec starting index read\r\nmysql tables in use 1, locked 1\r\nLOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1\r\nMySQL thread id 2997198, OS thread handle 140394973071104, query id 9145673 localhost root updating\r\nupdate test_tb set stu_name = 'lisi' where stu_id = 1006\r\n*** (1) WAITING FOR THIS LOCK TO BE GRANTED:\r\nRECORD LOCKS space id 224 page no 4 n bits 80 index uk_stu_id of table `testdb`.`test_tb` trx id 46913 lock_mode X locks rec but not gap waiting\r\nRecord lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0\r\n 0: len 4; hex 800003ee; asc ;;\r\n 1: len 4; hex 80000006; asc ;;\r\n \r\n*** (2) TRANSACTION:\r\nTRANSACTION 46914, ACTIVE 103 sec starting index read\r\nmysql tables in use 1, locked 1\r\n4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1\r\nMySQL thread id 2997201, OS thread handle 140394971473664, query id 9145681 localhost root updating\r\nupdate test_tb set age = 21 where stu_id = 1005\r\n*** (2) HOLDS THE LOCK(S):\r\nRECORD LOCKS space id 224 page no 4 n bits 80 index uk_stu_id of table `testdb`.`test_tb` trx id 46914 lock_mode X locks rec but not gap\r\nRecord lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0\r\n 0: len 4; hex 800003ee; asc ;;\r\n 1: len 4; hex 80000006; asc ;;\r\n \r\n*** (2) WAITING FOR THIS LOCK TO BE GRANTED:\r\nRECORD LOCKS space id 224 page no 4 n bits 80 index uk_stu_id of table `testdb`.`test_tb` trx id 46914 lock_mode X locks rec but not gap waiting\r\nRecord lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0\r\n 0: len 4; hex 800003ed; asc ;;\r\n 1: len 4; hex 80000005; asc ;;\r\n \r\n*** WE ROLL BACK TRANSACTION (2)\r\n \r\n# \u4ee5\u4e0a\u4e3a\u539f\u6587 \u4e0b\u9762\u589e\u52a0\u4e2a\u4eba\u5206\u6790\r\n------------------------\r\nLATEST DETECTED DEADLOCK\r\n------------------------\r\n2021-11-10 17:03:10 0x7fb040672700 #\u8fd9\u91cc\u663e\u793a\u4e86\u6700\u8fd1\u4e00\u6b21\u53d1\u751f\u6b7b\u9501\u7684\u65e5\u671f\u548c\u65f6\u95f4\r\n*** (1) TRANSACTION: #\u6b7b\u9501\u76f8\u5173\u7684\u7b2c\u4e00\u4e2a\u4e8b\u52a1\r\nTRANSACTION 46913, ACTIVE 142 sec starting index read\r\n#\u8fd9\u884c\u8868\u793a\u4e8b\u52a1id\u4e3a46913\uff0c\u4e8b\u52a1\u5904\u4e8e\u6d3b\u8dc3\u72b6\u6001142s\uff0cstarting index read\u8868\u793a\u6b63\u5728\u4f7f\u7528\u7d22\u5f15\u8bfb\u53d6\u6570\u636e\u884c\r\nmysql tables in use 1, locked 1\r\n#\u8fd9\u884c\u8868\u793a\u8be5\u4e8b\u52a1\u6b63\u5728\u4f7f\u75281\u4e2a\u8868\uff0c\u4e14\u6d89\u53ca\u9501\u7684\u8868\u67091\u4e2a\r\nLOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1\r\n#\u8fd9\u884c\u8868\u793a\u5728\u7b49\u5f854\u628a\u9501\uff0c\u5360\u7528\u5185\u5b581136\u5b57\u8282\uff0c\u6d89\u53ca3\u884c\u8bb0\u5f55\r\nMySQL thread id 2997198, OS thread handle 140394973071104, query id 9145673 localhost root updating\r\n#\u8fd9\u884c\u8868\u793a\u8be5\u4e8b\u52a1\u7684\u7ebf\u7a0bID\u4fe1\u606f\uff0c\u64cd\u4f5c\u7cfb\u7edf\u53e5\u67c4\u4fe1\u606f\uff0c\u8fde\u63a5\u6765\u6e90\u3001\u7528\u6237\r\nupdate test_tb set stu_name = 'lisi' where stu_id = 1006\r\n#\u8fd9\u884c\u8868\u793a\u4e8b\u52a1\u6267\u884c\u7684\u6700\u540e\u4e00\u6761SQL\u4fe1\u606f\r\n*** (1) WAITING FOR THIS LOCK TO BE GRANTED: #\u4e8b\u52a11\u60f3\u8981\u83b7\u53d6\u7684\u9501\r\nRECORD LOCKS space id 224 page no 4 n bits 80 index uk_stu_id of table `testdb`.`test_tb` trx id 46913 lock_mode X locks rec but not gap waiting\r\n#\u8fd9\u884c\u4fe1\u606f\u8868\u793a\u7b49\u5f85\u7684\u9501\u662f\u4e00\u4e2arecord lock\uff0c\u7a7a\u95f4id\u662f224\uff0c\u9875\u7f16\u53f7\u4e3a4\uff0c\u5927\u6982\u4f4d\u7f6e\u5728\u9875\u768480\u4f4d\u5904\uff0c\u9501\u53d1\u751f\u5728\u8868testdb.test_tb\u7684uk_stu_id\u7d22\u5f15\u4e0a\uff0c\u662f\u4e00\u4e2aX\u9501\uff0c\u4f46\u662f\u4e0d\u662fgap lock\uff0cwaiting\u8868\u793a\u6b63\u5728\u7b49\u5f85\u9501\r\nRecord lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0\r\n 0: len 4; hex 800003ee; asc ;;\r\n 1: len 4; hex 80000006; asc ;;\r\n \r\n*** (2) TRANSACTION: #\u6b7b\u9501\u76f8\u5173\u7684\u7b2c\u4e00\u4e2a\u4e8b\u52a1\r\nTRANSACTION 46914, ACTIVE 103 sec starting index read\r\n#\u8fd9\u884c\u8868\u793a\u4e8b\u52a12\u7684id\u4e3a46914\uff0c\u4e8b\u52a1\u5904\u4e8e\u6d3b\u8dc3\u72b6\u6001103s\r\nmysql tables in use 1, locked 1\r\n#\u6b63\u5728\u4f7f\u75281\u4e2a\u8868\uff0c\u6d89\u53ca\u9501\u7684\u8868\u67091\u4e2a\r\n4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1\r\n#\u6d89\u53ca4\u628a\u9501\uff0c3\u884c\u8bb0\u5f55\r\nMySQL thread id 2997201, OS thread handle 140394971473664, query id 9145681 localhost root updating\r\n#\u4e8b\u52a12\u7684\u7ebf\u7a0bID\u4fe1\u606f\uff0c\u64cd\u4f5c\u7cfb\u7edf\u53e5\u67c4\u4fe1\u606f\uff0c\u8fde\u63a5\u6765\u6e90\u3001\u7528\u6237\r\nupdate test_tb set age = 21 where stu_id = 1005\r\n#\u7b2c\u4e8c\u4e2a\u4e8b\u52a1\u7684SQL\r\n*** (2) HOLDS THE LOCK(S): # \u4e8b\u52a12\u6301\u6709\u7684\u9501 \u6b63\u662f\u4e8b\u52a11\u60f3\u8981\u83b7\u53d6\u7684\u9501\r\nRECORD LOCKS space id 224 page no 4 n bits 80 index uk_stu_id of table `testdb`.`test_tb` trx id 46914 lock_mode X locks rec but not gap\r\nRecord lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0\r\n 0: len 4; hex 800003ee; asc ;;\r\n 1: len 4; hex 80000006; asc ;;\r\n \r\n*** (2) WAITING FOR THIS LOCK TO BE GRANTED:\r\nRECORD LOCKS space id 224 page no 4 n bits 80 index uk_stu_id of table `testdb`.`test_tb` trx id 46914 lock_mode X locks rec but not gap waiting\r\nRecord lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0\r\n 0: len 4; hex 800003ed; asc ;;\r\n 1: len 4; hex 80000005; asc ;;\r\n#\u4e0a\u9762\u8fd9\u90e8\u5206\u662f\u4e8b\u52a1\u4e8c\u6b63\u5728\u7b49\u5f85\u7684\u9501\uff0c\u4ece\u4fe1\u606f\u4e0a\u770b\uff0c\u7b49\u5f85\u7684\u662f\u540c\u4e00\u4e2a\u8868\uff0c\u540c\u4e00\u4e2a\u7d22\u5f15\uff0c\u540c\u4e00\u4e2apage\u4e0a\u7684record lock X\u9501\uff0c\u4f46\u662fheap no\u4f4d\u7f6e\u4e0d\u540c\uff0c\u5373\u4e0d\u540c\u7684\u884c\u4e0a\u7684\u9501\r\n \r\n*** WE ROLL BACK TRANSACTION (2) #\u8868\u793a\u4e8b\u52a12\u88ab\u56de\u6eda<\/pre>\n |