{"id":1245,"date":"2020-05-18T20:31:45","date_gmt":"2020-05-18T12:31:45","guid":{"rendered":"http:\/\/39.97.231.10\/?p=1245"},"modified":"2020-05-18T20:31:45","modified_gmt":"2020-05-18T12:31:45","slug":"%e6%ad%bb%e9%94%81%e7%9a%84%e8%bf%87%e7%a8%8b%e5%88%86%e6%9e%90","status":"publish","type":"post","link":"http:\/\/www.weijing.co\/?p=1245","title":{"rendered":"MySQL\u6b7b\u9501\u7684\u8fc7\u7a0b\u5206\u6790"},"content":{"rendered":"
\n

1. MySQL\u4e8b\u52a1\u9694\u79bb\u7ea7\u522b<\/h4>\n

\u4e8b\u52a1\u9694\u79bb\u7ea7\u522b\u4e3aRC\u8bfb\u5df2\u63d0\u4ea4 MySQL\u7248\u672c\u4e3a 5.7.26<\/p>\n

2. \u8868\u7ed3\u6784\u548c\u6570\u636e\u7684\u521d\u59cb\u5316<\/h4>\n
 CREATE TABLE `t1` (\n   `ID` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT \\'\u81ea\u589eID\\',\n   `t1` int(10) NOT NULL COMMENT \\'\\',\n   `t2` int(10) NOT NULL COMMENT \\'\\',\n   `order_no` varchar(64) NOT NULL DEFAULT \\'\\' COMMENT \\'order no\\',\n   `status` int(10) NOT NULL COMMENT \\'\u72b6\u6001\\',\n   `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT \\'\u53d1\u751f\u65f6\u95f4\\',\n   PRIMARY KEY (`ID`),\n   KEY `idx_order_no` (`order_no`),\n   KEY `idx_status_createtime` (`status`, `createtime`)\n ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=\\'\\';\n\u8868\u4e2d\u67093\u4e2a\u7d22\u5f15\uff0c\u5176\u4e2d\u4e00\u4e2a\u4e3b\u952e\u7d22\u5f15\uff0c\u4e24\u4e2a\u8f85\u52a9\u7d22\u5f15\u3002\n\u63d2\u5165\u4e00\u884c\u8bb0\u5f55\nINSERT INTO `t1` (`ID`, `t1`, `t2`, `order_no`, `status`, `createtime`) VALUES (\\'1\\', \\'1\\', \\'1\\', \\'123456\\', \\'0\\', \\'2020-04-24 12:10:00\\');\nmysql> select ID,order_no, status, createtime from t1 where order_no=\\'123456\\';\n  +---------+-------------------+---------------------+\n  | ID      | order_no | status | createtime          |\n  +---------+-------------------+---------------------+\n  | 1       | 123456   |   0    | 2020-04-24 12:10:00 |\n  +---------+-------------------+---------------------+\n  1 row in set (0.00 sec)<\/pre>\n
3. \u6b7b\u9501\u65e5\u5fd7\u5206\u6790<\/h4>\n
3.1 \u5f53\u65f6\u7684\u6b7b\u9501\u65e5\u5fd7\u7c7b\u4f3c\u4e0b\u9762\u7684\u5185\u5bb9<\/p>\n
  2020-04-24T12:18:06.804155+08:00 4106 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.\n  2020-04-24T12:18:06.804185+08:00 4106 [Note] InnoDB:\n  *** (1) TRANSACTION:\n  TRANSACTION 18912896, ACTIVE 3 sec starting index read\n  mysql tables in use 1, locked 1\n  LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)\n  MySQL thread id 4108, OS thread handle 140149994698496, query id 31983724 localhost root Searching rows for update\n  UPDATE t1 SET status = 5 WHERE status = 0 AND (`createtime` BETWEEN DATE_SUB(NOW(),INTERVAL 90 MINUTE) AND DATE_SUB(NOW(),INTERVAL 60 MINUTE))\n  2020-04-24T12:18:06.804257+08:00 4106 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:\n  RECORD LOCKS space id 260 page no 3 n bits 72 index PRIMARY of table `sbtest`.`t1` trx id 18912896 lock_mode X locks rec but not gap waiting\n  Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0\n   0: len 4; hex 00000001; asc ;;\n   1: len 6; hex 000001209381; asc ;;\n   2: len 7; hex 45000001d825b8; asc E % ;;\n   3: len 4; hex 80000001; asc ;;\n   4: len 4; hex 80000001; asc ;;\n   5: len 6; hex 313233343536; asc 123456;;\n   6: len 4; hex 80000001; asc ;;\n   7: len 4; hex 5ea26698; asc ^ f ;;\n  2020-04-24T12:18:06.805410+08:00 4106 [Note] InnoDB: *** (2) TRANSACTION:\n  TRANSACTION 18912129, ACTIVE 42 sec updating or deleting\n  mysql tables in use 1, locked 1\n  4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1\n  MySQL thread id 4106, OS thread handle 140149998397184, query id 31985074 localhost root updating\n  update t1 set status=1 where order_no=\\'123456\\'\n  2020-04-24T12:18:06.805467+08:00 4106 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):\n  RECORD LOCKS space id 260 page no 3 n bits 72 index PRIMARY of table `sbtest`.`t1` trx id 18912129 lock_mode X locks rec but not gap\n  Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0\n   0: len 4; hex 00000001; asc ;;\n   1: len 6; hex 000001209381; asc ;;\n   2: len 7; hex 45000001d825b8; asc E % ;;\n   3: len 4; hex 80000001; asc ;;\n   4: len 4; hex 80000001; asc ;;\n   5: len 6; hex 313233343536; asc 123456;;\n   6: len 4; hex 80000001; asc ;;\n   7: len 4; hex 5ea26698; asc ^ f ;;\n  2020-04-24T12:18:06.805835+08:00 4106 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:\n  RECORD LOCKS space id 260 page no 5 n bits 72 index idx_status_createtime of table `sbtest`.`t1` trx id 18912129 lock_mode X locks rec but not gap waiting\n  Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0\n   0: len 4; hex 80000000; asc ;;\n   1: len 4; hex 5ea26698; asc ^ f ;;\n   2: len 4; hex 00000001; asc ;;\n  2020-04-24T12:18:06.806005+08:00 4106 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1)<\/pre>\n
3.2 \u58f0\u660e\uff1a\u6b7b\u9501\u65e5\u5fd7\u4e2d\u7684TRANSACTION 18912129 \u8bb0\u4e3a \u4e8b\u52a1\u4e00 \u6b7b\u9501\u65e5\u5fd7\u4e2d\u7684TRANSACTION 18912896 \u8bb0\u4e3a \u4e8b\u52a1\u4e8c \u5206\u6790\u6b7b\u9501\u7684\u65f6\u5019\uff0c\u6211\u4e60\u60ef\u628a\u4e8b\u52a1\u7f16\u53f7\u5c0f\u7684\u5b9a\u4e49\u4e3a\u4e8b\u52a1\u4e00<\/p>\n
3.3 \u4e8b\u52a1\u4e00\u7684\u4fe1\u606f\uff1a<\/p>\n
  \u4e8b\u52a1\u4e00\u7684SQL\u8bed\u53e5\uff1a\n      update t1 set status=1 where order_no=\\'123456\\'\n  \u6301\u6709\u7684\u9501\u4fe1\u606f\uff1a\n      HOLDS THE LOCK(S) --\u7528\u6765\u8868\u793a\u8fd9\u4e2a\u4e8b\u52a1\u6301\u6709\u54ea\u4e9b\u9501\n      index PRIMARY of table `sbtest`.`t1` --\u8868\u793a\u52a0\u9501\u662f\u5728\u52a0\u5728\u8868t1 \u7684\u7d22\u5f15PRIMARY \u4e0a\n      n_fields 8: --\u8868\u793a\u8fd9\u4e2a\u8bb0\u5f55\u662f8\u5217\n      0: len 4; hex 00000001; asc ;; --\u662f\u7b2c\u4e00\u4e2a\u5b57\u6bb5\uff0c\u4e5f\u5c31\u662f\u4e3b\u952e\u5b57\u6bb5 ID, 1\u4ece16\u8fdb\u5236\u8f6c\u6362\u4e3a10\u8fdb\u5236, \u5f97\u5230\u7684\u503c\u4e3a1\n  \u6240\u4ee5\u4e8b\u52a1\u4e00\u6301\u6709\u7684\u9501: PRIMARY: record lock: [1]\n  \u5728\u7b49\u5f85\u7684\u9501\u4fe1\u606f\uff1a\n      index idx_status_createtime of table `sbtest`.`t1` --\u8868\u793a\u5728\u7b49\u7684\u662f\u8868t1 \u7684\u8f85\u52a9\u7d22\u5f15idx_status_createtime \u4e0a\u9762\u7684\u9501\n      lock_mode X locks rec but not gap waiting --\u8868\u793a\u9700\u8981\u52a0\u4e00\u4e2a\u6392\u4ed6\u9501\uff08\u5199\u9501\uff09\uff0c\u5f53\u524d\u7684\u72b6\u6001\u662f\u7b49\u5f85\u4e2d\n      Record lock --\u8868\u793a\u8fd9\u662f\u4e00\u4e2a\u8bb0\u5f55\u9501\n      n_fields 3 --\u8868\u793a\u8f85\u52a9\u7d22\u5f15idx_status_createtime\u7684\u8bb0\u5f55\u662f3\u5217, \u6839\u636e\u7d22\u5f15\u7684\u5b58\u50a8\u7ed3\u6784, 3\u5217\u4f9d\u6b21\u4e3a status\u3001createtime\u3001ID\n      0: len 4; hex 80000000; asc ;; --status\u5b57\u6bb5, \u503c\u4e3a 0\n      1: len 4; hex 5ea26698; asc ^ f ;; --createtime\u5b57\u6bb5, 5ea26698 \u4ece16\u8fdb\u5236\u8f6c\u6362\u4e3a10\u8fdb\u5236, \u5f97\u5230\u65f6\u95f4\u62331587701400, \u8f6c\u6362\u4e3a\u5177\u4f53\u7684\u65e5\u671f: 2020-04-24 12:10:00\n      2: len 4; hex 00000001; asc ;; --ID\u5b57\u6bb5, 1\u4ece16\u8fdb\u5236\u8f6c\u6362\u4e3a10\u8fdb\u5236, \u5f97\u5230\u7684\u503c\u4e3a1\n   \u6240\u4ee5\u5728\u7b49\u5f85\u4e8b\u52a1\u4e8c \u8f85\u52a9\u7d22\u5f15idx_status_createtime (status=0,createtime=\\'2020-04-24 12:10:00\\',ID=1) \u8fd9\u4e00\u884c\u7684\u8bb0\u5f55\u9501<\/pre>\n
3.4 \u4e8b\u52a1\u4e8c\u7684\u4fe1\u606f<\/p>\n
  \u4e8b\u52a1\u4e8c\u7684SQL\u8bed\u53e5\n      UPDATE t1 SET status = 5 WHERE status = 0 AND (`createtime` BETWEEN DATE_SUB(NOW(),INTERVAL 90 MINUTE) AND DATE_SUB(NOW(),INTERVAL 60 MINUTE));\n  \u6301\u6709\u7684\u9501\u4fe1\u606f\n      \u6839\u636e\u4e8b\u52a1\u4e00\u5728\u7b49\u5f85\u4e8b\u52a1\u4e8c\u4e0a\u8868t1\u8f85\u52a9\u7d22\u5f15idx_nStatus_CreateTime \u4e0a\u7684\u884c\u9501\uff0c\u6240\u4ee5\u63a8\u5bfc\u51fa\u4e8b\u52a1\u4e8c\u6301\u6709\u8868t1\u8f85\u52a9\u7d22\u5f15idx_nStatus_CreateTime \u4e0a\u7684\u884c\u9501\n      \u6240\u4ee5\u4e8b\u52a1\u4e8c\u6301\u6709\u7684\u9501: idx_status_createtime: record lock: (status=0,createtime=\\'2020-04-24 12:10:00\\',ID=1)\n  \u5728\u7b49\u5f85\u7684\u9501\u4fe1\u606f\n      index PRIMARY of table `sbtest`.`t1` --\u8868\u793a\u5728\u7b49\u7684\u662f\u8868t1 \u7684\u4e3b\u952e\u7d22\u5f15 \u4e0a\u9762\u7684\u9501\n      lock_mode X locks rec but not gap waiting --\u8868\u793a\u9700\u8981\u52a0\u4e00\u4e2a\u6392\u4ed6\u9501\uff08\u5199\u9501\uff09\uff0c\u5f53\u524d\u7684\u72b6\u6001\u662f\u7b49\u5f85\u4e2d\n      Record lock --\u662f\u4e00\u4e2a\u8bb0\u5f55\u9501\n      0: len 4; hex 00000001; asc ;; --\u662f\u7b2c\u4e00\u4e2a\u5b57\u6bb5\uff0c\u4e5f\u5c31\u662f\u4e3b\u952e\u5b57\u6bb5 ID, \u503c\u4e3a 1\n   \u6240\u4ee5\u5728\u7b49\u5f85\u4e8b\u52a1\u4e00\u7684: PRIMARY: record lock\uff1a(ID=1) \u3002<\/pre>\n
3.5 \u6839\u636e\u6b7b\u9501\u4fe1\u606f\u5206\u6790\u51fa\u7684\u4e24\u4e2a\u4e8b\u52a1\u7684\u52a0\u9501\u89c4\u5219\u548c\u6b7b\u9501\u6210\u56e0<\/p>\n\n\n\n\n\n\n\n\n
\u65f6\u95f4\u70b9<\/td>\n\u4e8b\u52a1\u4e00<\/td>\n\u4e8b\u52a1\u4e8c<\/td>\n<\/tr>\n
T1<\/span><\/td>\n\u6301\u6709\u4e3b\u952e\u7d22\u5f15 ID=1 \u7684\u8bb0\u5f55\u9501, \u9501\u7684\u6a21\u5f0f\u4e3a\u6392\u4ed6\u9501<\/td>\n<\/td>\n<\/tr>\n
T2<\/td>\n<\/td>\n\u6301\u6709\u8f85\u52a9\u7d22\u5f15:
idx_status_createtime: record lock:\u00a0status=0\uff0ccreatetime=\\’2020-04-24 12:10:00\\’, ID=1) \u7684\u8bb0\u5f55\u9501\uff0c\u9501\u7684\u6a21\u5f0f\u4e3a\u6392\u4ed6\u9501<\/td>\n<\/tr>\n
T3<\/td>\n<\/td>\n\u00a0\u5728\u7b49\u4e3b\u952e\u7d22\u5f15 ID=1 \u7684\u8bb0\u5f55\u9501<\/td>\n<\/tr>\n
T4<\/td>\n\u5728\u7b49\u8f85\u52a9\u7d22\u5f15idx_status_createtime:\u00a0
\u00a0 \u00a0record lock: (status=0\uff0c createtime=\\’2020-04-24 12:10:00\\’\uff0cID=1)\u7684\u8bb0\u5f55\u9501<\/td>\n		<\/td>\n<\/tr>\n
T3\u88abT1\u963b\u585e\uff0cT4\u88abT2\u963b\u585e\uff0c\u56e0\u6b64\u9501\u8d44\u6e90\u8bf7\u6c42\u5f62\u6210\u4e86\u73af\u8def\uff0c\u8fdb\u800c\u89e6\u53d1\u6b7b\u9501\u68c0\u6d4b, \u56e0\u6b64\u5bfc\u81f4\u4e86\u6b7b\u9501\u3002<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
4. \u6839\u636e\u4e0a\u9762\u7684\u6b7b\u9501\u5206\u6790\u548c\u4e1a\u52a1\u903b\u8f91\u590d\u73b0\u8be5\u6b7b\u9501<\/h4>\n\n\n\n\n\n\n\n\n\n\n\n
\u65f6\u95f4\u70b9<\/td>\n\u4e8b\u52a1\u4e00<\/td>\n\u4e8b\u52a1\u4e8c<\/td>\n<\/tr>\n
<\/td>\nbegin;
\nselect status from t1 where\u00a0
\u00a0order_no=\\’123456\\’ for update;\u00a0<\/span><\/td>\n		<\/td>\n<\/tr>\n
T1<\/td>\n\u6301\u6709\u4e3b\u952e\u7d22\u5f15 ID=1 \u7684\u8bb0\u5f55\u9501, \u9501\u7684\u6a21\u5f0f\u4e3a\u6392\u4ed6\u9501<\/td>\n<\/td>\n<\/tr>\n
<\/td>\n<\/td>\nUPDATE t1 SET status = 5 WHERE\u00a0status = 0 AND (createtime BETWEEN DATE_SUB(NOW(),INTERVAL 90 MINUTE)\u00a0\u00a0AND DATE_SUB(NOW(),INTERVAL 60 MINUTE));<\/td>\n<\/tr>\n
T2<\/td>\n<\/td>\n\u6301\u6709\u8f85\u52a9\u7d22\u5f15:<\/span>
idx_status_createtime: (record lock: status=0, createtime=\\’2020-04-24 12:10:00\\’, ID=1) \u7684\u8bb0\u5f55\u9501\uff0c\u9501\u7684\u6a21\u5f0f\u4e3a\u6392\u4ed6\u9501<\/span><\/td>\n<\/tr>\n
T3<\/td>\n<\/td>\n\u00a0\u5728\u7b49\u4e3b\u952e\u7d22\u5f15 ID=1 \u7684\u8bb0\u5f55\u9501<\/td>\n<\/tr>\n
<\/td>\nupdate t1 set status=1 where order_no=\\’123456\\’;<\/td>\n<\/td>\n<\/tr>\n
T4<\/td>\n\u5728\u7b49\u8f85\u52a9\u7d22\u5f15idx_status_createtime:\u00a0
\u00a0 \u00a0record lock: (status=0, createtime=\\’2020-04-24 12:10:00\\’\uff0cID=1)\u7684\u8bb0\u5f55\u9501<\/span><\/td>\n		<\/td>\n<\/tr>\n
T3\u88abT1\u963b\u585e\uff0cT4\u88abT2\u963b\u585e\uff0c\u56e0\u6b64\u9501\u8d44\u6e90\u8bf7\u6c42\u5f62\u6210\u4e86\u73af\u8def\uff0c\u8fdb\u800c\u89e6\u53d1\u6b7b\u9501\u68c0\u6d4b, MySQL\u4f1a\u628a\u6267\u884c\u4ee3\u4ef7\u6700\u5c0f\u7684\u4e8b\u52a1\u56de\u6eda\u6389\uff0c\u8ba9\u5176\u5b83\u4e8b\u52a1\u5f97\u4ee5\u7ee7\u7eed\u8fdb\u884c\u3002<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\u67e5\u770b informationschema \u4e0b\u7684\u4e24\u4e2a\u89c6\u56fe innodblocks \u548c innodblockwaits \u786e\u8ba4T1\u3001T2\u3001T3\u65f6\u523b\u6301\u6709\u7684\u9501\u548c\u5728\u7b49\u5f85\u7684\u9501\u7684\u8be6\u60c5<\/p>\n
   mysql> select * from information_schema.innodb_locks\\\\G\n  *************************** 1. row ***************************\n    lock_id: 18912896:260:3:2 --\u8bf7\u6c42\u7684\u9501\n    lock_trx_id: 18912896 --\u88ab\u963b\u585e\u7684\u4e8b\u52a1ID\n    lock_mode: X --\u6392\u4ed6\u9501\n    lock_type: RECORD --\u9501\u7c7b\u578b \u8bb0\u5f55\u9501\n   lock_table: `sbtest`.`t1` --\u8868\n   lock_index: PRIMARY --\u7d22\u5f15\u4e3a\u4e3b\u952e\u7d22\u5f15\n   lock_space: 260\n    lock_page: 3\n     lock_rec: 2\n    lock_data: 1 --\u8bf7\u6c42\u52a0\u9501\u7684row data\uff0cID=1\u8fd9\u884c\u8bb0\u5f55\n  *************************** 2. row ***************************\n      lock_id: 18912129:260:3:2 --\u6301\u6709\u7684\u9501\n  lock_trx_id: 18912129 --\u6301\u6709\u9501\u7684\u4e8b\u52a1ID\n    lock_mode: X --\u6392\u4ed6\u9501 LOCK_X\n    lock_type: RECORD --\u9501\u7c7b\u578b \u8bb0\u5f55\u9501\n   lock_table: `sbtest`.`t1` --\u8868\n   lock_index: PRIMARY --\u7d22\u5f15\u4e3a\u4e3b\u952e\u7d22\u5f15\n   lock_space: 260 --table space id\n    lock_page: 3 --page no\n     lock_rec: 2 --heap no\n    lock_data: 1 --\u88ab\u52a0\u9501\u7684row data\uff0cID=1\u8fd9\u884c\u8bb0\u5f55\n  2 rows in set (0.00 sec)<\/pre>\n
trxid \u4e3a 18912129 \u5411\u8868 t1 \u52a0\u4e86\u4e00\u4e2a X \u7684\u8bb0\u5f55\u9501\uff0c trxid \u4e3a 18912896 \u7684\u4e8b\u52a1\u5411\u8868 t1 \u7533\u8bf7\u4e86\u4e00\u4e2a X \u7684\u8bb0\u5f55\u9501\uff0clock_data \u7684\u503c\u4e3a1 \u5373\u8bb0\u5f55\u7684\u4e3b\u952e\u503c\u4e3a1\uff0c\u7533\u8bf7\u4e86\u76f8\u540c\u7684\u8d44\u6e90\uff0c\u56e0\u6b64\u8fd9\u91cc\u4f1a\u6709\u7b49\u5f85\u3002<\/p>\n
\u4e8b\u52a1\u4e8c\u66f4\u65b0\u8bed\u53e5\u7684WHERE\u6761\u4ef6\u67e5\u8be2\u5230\u7684\u8bb0\u5f55\u662f\u4e3a\u7a7a\u7684, \u4f46\u662f\u4e3a\u4ec0\u4e48\u4f1a\u6301\u6709\u8f85\u52a9\u7d22\u5f15idxstatuscreatetime\u4e0a\u9762\u7684\u9501\u5462\uff1f<\/p>\n
mysql> select * from t1 WHERE status = 0 AND (`createtime` BETWEEN DATE_SUB(NOW(),INTERVAL 90 MINUTE) AND DATE_SUB(NOW(),INTERVAL 60 MINUTE));\nEmpty set (0.00 sec)<\/pre>\n
\u539f\u56e0\uff1a\u56e0\u4e3a\u8fd9\u91cc\u662f\u8f85\u52a9\u7d22\u5f15\u7684\u8303\u56f4\u67e5\u8be2\u66f4\u65b0\uff0c\u8303\u56f4\u67e5\u8be2\u5c31\u4f1a\u5f80\u540e\u67e5\u627e\uff0c\u521a\u597d\u627e\u5230 (status<\/span>=<\/span>0<\/span>,<\/span>createtime<\/span>=<\/span>\\'2020-04-24 12:10:00\\'<\/span>)<\/span><\/code>\u7684\u8bb0\u5f55\u624d\u4f1a\u505c\u4e0b\u6765, \u56e0\u6b64\u6301\u6709\u884c\u9501\uff1a(status=0,createtime=\\'2020-04-24 12:10:00\\',ID=1)<\/code><\/p>\n
 <\/p>\n
MySQL8.0 \u7248\u672cinformationschema\u4e2d\u7684innodblocks\u548cinnodblockwaits\u8868\u88ab\u79fb\u9664\uff0c\u53d6\u800c\u4ee3\u4e4b\u7684\u662fperformanceschema\u4e2d\u7684data<\/span>locks\u548cdatalock<\/span>waits\u8868\uff0c\u901a\u8fc7 data_locks \u53ef\u4ee5\u770b\u5230\u6570\u636e\u52a0\u9501\u7684\u8be6\u60c5\u3002\u540c\u65f6\uff0c\u4e0b\u6587\u4f1a\u628a\u8fd9\u4e2a\u6848\u4f8b\u5728 MySQL 8.0.19 \u4e0b\u505a\u6d4b\u8bd5\uff0c \u56e0\u4e3a MySQL 8.0\u8bb0\u5f55\u4e86\u66f4\u8be6\u7ec6\u7684\u6b7b\u9501\u65e5\u5fd7\uff0c\u5206\u6790\u6b7b\u9501\u4f1a\u66f4\u52a0\u8f7b\u677e\u3002<\/p>\n
  mysql> select * from information_schema.innodb_lock_waits\\\\G;\n  *************************** 1. row ***************************\n  requesting_trx_id: 18912896 --\u8bf7\u6c42\u9501\u8d44\u6e90\u7684\u4e8b\u52a1ID\n  requested_lock_id: 18912896:260:3:2 --\u7533\u8bf7\u7684\u9501\u7684ID\n  blocking_trx_id: 18912129 --\u6301\u6709\u9501\u7684\u4e8b\u52a1ID\n  blocking_lock_id: 18912129:260:3:2 --\u6301\u6709\u7684\u9501\u7684ID<\/pre>\n
5. \u89e3\u51b3\u672c\u6848\u4f8b\u6b7b\u9501\u7684\u529e\u6cd5\u4e4b\u4e00<\/h4>\n
\u4e8b\u52a1\u4e00\u7684 UPDATE\u8bed\u53e5\u53ef\u4ee5\u4e0d\u653e\u5728\u663e\u793a\u58f0\u660e\u7684\u4e8b\u52a1\u5185\u6267\u884c, \u7136\u540e\u628a\u66f4\u65b0\u8bed\u53e5\u79fb\u51fa\u663e\u793a\u58f0\u660e\u7684\u4e8b\u52a1\u4e2d\u5c31\u53ef\u4ee5\u907f\u514d\u672c\u6848\u4f8b\u7684\u6b7b\u9501\u3002<\/p>\n
6. MySQL 8.0.19 \u7248\u672c\u6d4b\u8bd5\u672c\u6848\u4f8b<\/h4>\n
\u73af\u5883<\/p>\n
  mysql> select version();\n  +-----------+\n  | version() |\n  +-----------+\n  | 8.0.19 |\n  +-----------+\n  1 row in set (0.00 sec)\n  mysql> select @@session.transaction_isolation;\n  +---------------------------------+\n  | @@session.transaction_isolation |\n  +---------------------------------+\n  | READ-COMMITTED                  |\n  +---------------------------------+\n  1 row in set (0.00 sec)<\/pre>\n
\u4e8b\u52a1\u7684\u6267\u884c\u987a\u5e8f<\/strong><\/p>\n\n\n\n\n\n\n
\u65f6\u95f4\u70b9<\/td>\n\u4e8b\u52a1\u4e00<\/td>\n\u4e8b\u52a1\u4e8c<\/td>\n<\/tr>\n
T1<\/td>\nbegin;
\nselect status from t1 where\u00a0
\u00a0order_no=\\’123456\\’ for update;\u00a0<\/span><\/td>\n		<\/td>\n<\/tr>\n
T2<\/td>\n<\/td>\nUPDATE t1 SET status = 5 WHERE\u00a0status = 0 AND (`createtime` BETWEEN DATE_SUB(NOW(),INTERVAL 90 MINUTE)\u00a0\u00a0AND DATE_SUB(NOW(),INTERVAL 60 MINUTE));<\/td>\n<\/tr>\n
T3<\/td>\nupdate t1 set status=1 where order_no=\\’123456\\’;<\/td>\n\nERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\u67e5\u770b informationschema.innodblocks \u786e\u8ba4T1\u3001T2 \u65f6\u523b\u6301\u6709\u7684\u9501\u548c\u5728\u7b49\u5f85\u7684\u9501\u7684\u8be6\u60c5<\/p>\n
mysql> select ENGINE_LOCK_ID,ENGINE_TRANSACTION_ID,THREAD_ID,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;\n+----------------------------------------+-----------------------+-----------+-------------+-----------------------+-----------+---------------+-------------+------------------+\n| ENGINE_LOCK_ID                         | ENGINE_TRANSACTION_ID | THREAD_ID | OBJECT_NAME | INDEX_NAME            | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA        |\n+----------------------------------------+-----------------------+-----------+-------------+-----------------------+-----------+---------------+-------------+------------------+\n| 140472041388912:1072:140471938672632   |                212055 |     30483 | t1          | NULL                  | TABLE     | IX            | GRANTED     | NULL             |\n| 140472041388912:15:6:2:140471938669688 |                212055 |     30483 | t1          | idx_status_createtime | RECORD    | X,REC_NOT_GAP | GRANTED     | 0, 0x5EA26698, 1 |\n| 140472041388912:15:4:2:140471938670032 |                212055 |     30483 | t1          | PRIMARY               | RECORD    | X,REC_NOT_GAP | WAITING     | 1                |\n| 140472041388040:1072:140471938666488   |                212052 |     30481 | t1          | NULL                  | TABLE     | IX            | GRANTED     | NULL             |\n| 140472041388040:15:5:2:140471938663448 |                212052 |     30481 | t1          | idx_order_no          | RECORD    | X,REC_NOT_GAP | GRANTED     | \\'123456\\', 1      |\n| 140472041388040:15:4:2:140471938663792 |                212052 |     30481 | t1          | PRIMARY               | RECORD    | X,REC_NOT_GAP | GRANTED     | 1                |\n+----------------------------------------+-----------------------+-----------+-------------+-----------------------+-----------+---------------+-------------+------------------+\n6 rows in set (0.00 sec)<\/pre>\n
\u4e8b\u52a1\u4e00\u5728T1\u65f6\u523b\u6301\u6709\u7684\u9501\uff1aINDEXNAME=PRIMARY\uff0cLOCKTYPE=RECORD\uff0cLOCKSTATUS=GRANTED\uff0cLOCKDATA=1 : PRIMARY:recordlock:[1]<\/code>INDEXNAME=idxorderno\uff0cLOCKTYPE=RECORD\uff0cLOCKSTATUS=GRANTED\uff0cLOCKDATA=\\’123456\\’, 1 : idx_order_no<\/span>:<\/span>record<\/span>lock<\/span>:<\/span>(<\/span>order<\/span>=<\/span>\\'123456\\'<\/span>,<\/span>ID<\/span>=<\/span>1<\/span>)<\/span><\/code>\u4e8b\u52a1\u4e8c\u6301\u6709\u7684\u9501\uff1aINDEXNAME=idx<\/span>statuscreatetime \uff0cLOCK<\/span>TYPE=RECORD\uff0cLOCKSTATUS=GRANTED\uff0cLOCK<\/span>DATA=\\’0, 0x5EA26698, 1\\’: (status=0,createtime=\\'2020-04-24 12:10:00\\',ID=1)<\/code>\u4e8b\u52a1\u4e8c\u5728\u7b49\u5f85\u7684\u9501\uff1aINDEXNAME=PRIMARY\uff0cLOCK<\/span>TYPE=RECORD\uff0cLOCKSTATUS=WAITING, LOCK<\/span>DATA=1 \u8868\u793a \u4e8b\u52a1\u4e8c\u5728\u7b49\u7684\u9501\u4e3a: PRIMARY:recordlock:[1]<\/code><\/p>\n
\u6b7b\u9501\u65e5\u5fd7<\/strong><\/p>\n
  2020-04-24 12:15:36 0x7fc1947ea700\n  *** (1) TRANSACTION:\n  TRANSACTION 212055, ACTIVE 1 sec starting index read\n  mysql tables in use 1, locked 1\n  LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)\n  MySQL thread id 30432, OS thread handle 140468864067328, query id 135057 localhost root Searching rows for update\n  UPDATE t1 SET status = 5 WHERE status = 0 AND (`createtime` BETWEEN DATE_SUB(NOW(),INTERVAL 90 MINUTE) AND DATE_SUB(NOW(),INTERVAL 60 MINUTE))\n  *** (1) HOLDS THE LOCK(S): --\u6301\u6709\u7684\u9501\n  RECORD LOCKS space id 15 page no 6 n bits 72 index idx_status_createtime of table `sbtest`.`t1` trx id 212055 lock_mode X locks rec but not gap\n  Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0\n   0: len 4; hex 80000000; asc ;;\n   1: len 4; hex 5ea26698; asc ^ f ;;\n   2: len 4; hex 00000001; asc ;;\n  *** (1) WAITING FOR THIS LOCK TO BE GRANTED: --\u5728\u7b49\u5f85\u7684\u9501\n  RECORD LOCKS space id 15 page no 4 n bits 72 index PRIMARY of table `sbtest`.`t1` trx id 212055 lock_mode X locks rec but not gap waiting\n  Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0\n   0: len 4; hex 00000001; asc ;;\n   1: len 6; hex 000000033c54; asc <T;;\n   2: len 7; hex 0200000ff80e6a; asc j;;\n   3: len 4; hex 80000001; asc ;;\n   4: len 4; hex 80000001; asc ;;\n   5: len 6; hex 313233343536; asc 123456;;\n   6: len 4; hex 80000001; asc ;;\n   7: len 4; hex 5ea26698; asc ^ f ;;\n  *** (2) TRANSACTION:\n  TRANSACTION 212052, ACTIVE 68 sec updating or deleting\n  mysql tables in use 1, locked 1\n  LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1\n  MySQL thread id 30430, OS thread handle 140468857743104, query id 135061 localhost root updating\n  update t1 set status=1 where order_no=\\'123456\\'\n  *** (2) HOLDS THE LOCK(S): --\u6301\u6709\u7684\u9501\n  RECORD LOCKS space id 15 page no 4 n bits 72 index PRIMARY of table `sbtest`.`t1` trx id 212052 lock_mode X locks rec but not gap\n  Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0\n   0: len 4; hex 00000001; asc ;;\n   1: len 6; hex 000000033c54; asc <T;;\n   2: len 7; hex 0200000ff80e6a; asc j;;\n   3: len 4; hex 80000001; asc ;;\n   4: len 4; hex 80000001; asc ;;\n   5: len 6; hex 313233343536; asc 123456;;\n   6: len 4; hex 80000001; asc ;;\n   7: len 4; hex 5ea26698; asc ^ f ;;\n  *** (2) WAITING FOR THIS LOCK TO BE GRANTED: --\u5728\u7b49\u5f85\u7684\u9501\n  RECORD LOCKS space id 15 page no 6 n bits 72 index idx_status_createtime of table `sbtest`.`t1` trx id 212052 lock_mode X locks rec but not gap waiting\n  Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0\n   0: len 4; hex 80000000; asc ;;\n   1: len 4; hex 5ea26698; asc ^ f ;;\n   2: len 4; hex 00000001; asc ;;\n  *** WE ROLL BACK TRANSACTION (1)<\/pre>\n
\u53ef\u4ee5\u770b\u5230\uff0c MySQL 8.0\u7248\u672c\u7684\u6b7b\u9501\u65e5\u5fd7\u66f4\u52a0\u5b8c\u6574\u4e86\uff1a\u628a\u4e8b\u52a1\u6301\u6709\u7684\u9501\u548c\u5728\u7b49\u5f85\u7684\u9501\u7684\u8be6\u60c5\u90fd\u8bb0\u5f55\u4e0b\u6765\u4e86\u3002<\/p>\n
7. \u5c0f\u7ed3<\/h3>\n
\u6b7b\u9501\u4e00\u822c\u53ef\u4ee5\u7ed3\u5408\u6b7b\u9501\u65e5\u5fd7\u3001\u52a0\u9501\u89c4\u5219\u548c\u4e1a\u52a1\u573a\u666f\u6765\u505a\u76f8\u5173\u7684\u5206\u6790 \u8f85\u52a9\u7d22\u5f15\u7684\u8303\u56f4\u67e5\u8be2\u66f4\u65b0\u52a0\u9501\uff0c\u9700\u8981\u5f80\u540e\u627e\u5230\u4e00\u6761\u6ee1\u8db3\u6761\u4ef6\u7684\u8bb0\u5f55\u624d\u4f1a\u505c\u6b62\u626b\u63cf \u52a0\u9501\u662f\u5728\u52a0\u5728\u7d22\u5f15\u4e0a\u7684\uff0c\u5f53\u8868\u4e2d\u6709\u591a\u4e2a\u7d22\u5f15\uff0c\u53ea\u4f1a\u5bf9\u5fc5\u8981\u7684\u7d22\u5f15\u52a0\u9501\uff0c\u4f8b\u5982\u672c\u6848\u4f8b\u4e2d\u7684\u8868t1\u67093\u4e2a\u7d22\u5f15\uff0c\u5206\u522b\u4e3a PRIMARY KEY\u3001idxorder<\/span>no\u548cidxstatus<\/span>createtime\uff0c\u5f53\u6267\u884c select * from t1 where orderno=\\’123456\\’ for update; \u8bed\u53e5\u540e\uff0c\u4f1a\u5bf9\u7d22\u5f15 PRIMARY KEY\u3001idx<\/span>orderno \u7684\u8bb0\u5f55\u8fdb\u884c\u52a0\u9501\uff0c\u5e76\u4e0d\u4f1a\u5bf9\u7d22\u5f15 idx<\/span>statuscreatetime \u7684\u8bb0\u5f55\u8fdb\u884c\u52a0\u9501\u3002MySQL 8.0\u7248\u672c\u901a\u8fc7 performance<\/span>schema.data_locks \u53ef\u4ee5\u770b\u5230\u4e8b\u52a1\u6301\u6709\u7684\u9501\u5217\u8868\u548c\u5728\u7b49\u5f85\u7684\u9501\u7684\u5217\u8868\u3002\u540c\u65f6\uff0cMySQL 8.0\u7248\u672c\u8bb0\u5f55\u7684\u6b7b\u9501\u65e5\u5fd7\u66f4\u52a0\u5b8c\u6574\u4e86\uff0c\u4e0d\u518d\u9700\u8981\u6839\u636e\u6b7b\u9501\u65e5\u5fd7\u4e2d\u7684\u9501\u7b49\u5f85\u7684\u8bb0\u5f55\u4fe1\u606f\u63a8\u5bfc\u51fa\u53e6\u4e00\u4e2a\u4e8b\u52a1\u6301\u6709\u7684\u9501\u4fe1\u606f\uff0c\u5206\u6790\u6b7b\u9501\u4f1a\u66f4\u52a0\u8f7b\u677e\u3002<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
1. MySQL\u4e8b\u52a1\u9694\u79bb\u7ea7\u522b \u4e8b\u52a1\u9694\u79bb\u7ea7\u522b\u4e3aRC\u8bfb\u5df2\u63d0\u4ea4 MySQL\u7248\u672c\u4e3a 5.7.26 2. \u8868\u7ed3\u6784\u548c\u6570\u636e\u7684 …<\/p>\n
  MySQL\u6b7b\u9501\u7684\u8fc7\u7a0b\u5206\u6790<\/span> \u67e5\u770b\u5168\u6587 »<\/a><\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":""},"categories":[90],"tags":[],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false,"mailpoet_newsletter_max":false,"woocommerce_thumbnail":false,"woocommerce_single":false,"woocommerce_gallery_thumbnail":false,"shop_catalog":false,"shop_single":false,"shop_thumbnail":false},"uagb_author_info":{"display_name":"weijing","author_link":"http:\/\/www.weijing.co\/?author=4"},"uagb_comment_info":0,"uagb_excerpt":"1. MySQL\u4e8b\u52a1\u9694\u79bb\u7ea7\u522b \u4e8b\u52a1\u9694\u79bb\u7ea7\u522b\u4e3aRC\u8bfb\u5df2\u63d0\u4ea4 MySQL\u7248\u672c\u4e3a 5.7.26 2. \u8868\u7ed3\u6784\u548c\u6570\u636e\u7684…","_links":{"self":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/1245"}],"collection":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1245"}],"version-history":[{"count":0,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/1245\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1245"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1245"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1245"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}