{"id":10217,"date":"2024-06-18T09:49:26","date_gmt":"2024-06-18T01:49:26","guid":{"rendered":"https:\/\/nj.transwarp.cn:8180\/?p=10217"},"modified":"2025-05-15T10:30:11","modified_gmt":"2025-05-15T02:30:11","slug":"kundb-kill-process%e8%a7%a3%e5%86%b3%e6%ad%bb%e9%94%81%e9%97%ae%e9%a2%98","status":"publish","type":"post","link":"https:\/\/kbwp.transwarp.cn\/?p=10217","title":{"rendered":"kill process\u89e3\u51b3kundb\u6b7b\u9501\u95ee\u9898"},"content":{"rendered":"<h3>\u6982\u8981\u63cf\u8ff0<\/h3>\n<hr \/>\n<p>KunDB \u4e2d\uff0c\u5f53\u4e24\u4e2a\u6216\u591a\u4e2a\u4e8b\u52a1\u6c38\u4e45\u963b\u585e\u5e76\u7b49\u5f85\u5bf9\u65b9\u91ca\u653e\u9501\u65f6\uff0c\u4f1a\u53d1\u751f\u6b7b\u9501\u3002\u6b64\u65f6\u53ef\u4ee5\u9009\u62e9\u7ec8\u6b62\u5176\u4e2d\u4e00\u4e2a\u4e8b\u52a1\u6765\u89e3\u9664\u6b7b\u9501\u3002<\/p>\n<p>\u672c\u6587\u4ecb\u7ecd\u4e00\u79cd\u6839\u636e\u8868\u540d\u67e5\u627e\u4e8b\u52a1\uff0c\u5e76\u901a\u8fc7 kill process \u89e3\u51b3\u6b7b\u9501\u95ee\u9898\u7684\u4e00\u79cd\u65b9\u6cd5<\/p>\n<h3>\u8be6\u7ec6\u4ecb\u7ecd<\/h3>\n<hr \/>\n<h4>1\u3001\u6784\u9020\u573a\u666f<\/h4>\n<p>\u521b\u5efa\u4e00\u5f20\u6709\u4e3b\u952e\u7684\u8868<\/p>\n<pre><code class=\"language-sql\">CREATE TABLE <code>testdum<\/code> (\n  <code>id<\/code> int NOT NULL,\n  <code>name<\/code> varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL,\n  PRIMARY KEY (<code>id<\/code>)\n) ENGINE=InnoDB;<\/code><\/pre>\n<table>\n<thead>\n<tr>\n<th>session1<\/th>\n<th>session2<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>1<\/td>\n<td>begin;<\/td>\n<td><\/td>\n<\/tr>\n<tr>\n<td>2<\/td>\n<td>insert into testdum (id ,name ) values (1,&#8217;aaaaaa&#8217;) on duplicate key update id=values(id),name=values(name);<\/td>\n<td><\/td>\n<\/tr>\n<tr>\n<td>3<\/td>\n<td><\/td>\n<td>begin<\/td>\n<\/tr>\n<tr>\n<td>4<\/td>\n<td><\/td>\n<td>insert into testdum (id ,name ) values (1,&#8217;bbbbb&#8217;) on duplicate key update id=values(id),name=values(name);<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u6b64\u65f6\uff0csession2\u4e2d\u7684\u8fd9\u6761<code>insert<\/code>\u6267\u884c\u5361\u4f4f\uff0c\u7b49\u5f85\u4e00\u4e2a<code>innodb_lock_wait_timeout<\/code>\u65f6\u957f\u4e4b\u540e\u4f1a\u62a5\u9519\uff1a<\/p>\n<p><font color=red>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction (errno 1205) (sqlstate HY000) during query: insert into testdum (id ,name ) values (1,&#8217;bbbbb&#8217;) on duplicate key update id=values(id),name=values(name)<\/font><\/p>\n<h4>2\u3001\u89e3\u51b3\u65b9\u6848<\/h4>\n<p><strong>\u6700\u7b80\u4fbf\u7684\u89e3\u51b3\u65b9\u6848\uff08\u63a8\u8350\uff09\uff1a<\/strong><\/p>\n<pre><code class=\"language-sql\">select * from sys.innodb_lock_waits where locked_table_schema='db1' and locked_table_name='testdum';<\/code><\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/nj.transwarp.cn:8180\/wp-content\/uploads\/2024\/06\/image-1747204292413.png\" alt=\"file\" \/><\/p>\n<p>\u76f4\u63a5\u62f7\u8d1d sql_kill_blocking_connection \u7684\u5185\u5bb9\u8fdb\u884c kill\u5373\u53ef\u3002<\/p>\n<blockquote>\n<p>\u6ce8\u610f\uff01 \u9ad8\u53ef\u7528kundb\u53ef\u4ee5\u5728\u5916\u5c42\u76f4\u63a5kill\uff1b \u5206\u5e03\u5f0fkundb\u9700\u8fde\u63a5\u5230\u5e95\u5c42shard master \u6267\u884ckill \uff08\u5148\u6267\u884c\u524d\u9762\u7684select\u67e5\u8be2\uff0c\u786e\u8ba4\u5728\u8be5shard \u624d\u9700\u8981\u6267\u884c\u3002\uff09<\/p>\n<\/blockquote>\n<hr \/>\n<p>\u6216\u8005\u53ef\u4ee5\u8003\u8651 \u53c2\u8003\u5982\u4e0bsql\u8bed\u53e5\uff0c\u83b7\u53d6thread_id<\/p>\n<blockquote>\n<p>\u6ce8\u610f\uff0c\u53ef\u4ee5\u6839\u636e<code>a.OBJECT_SCHEMA = &#039;db1&#039; and a.OBJECT_NAME = &#039;testdum&#039;<\/code>\u8fc7\u6ee4\u8868\u540d<\/p>\n<\/blockquote>\n<pre><code class=\"language-sql\">SELECT\n    NOW( 6 ) curr_dt,\n    a.thread_id,\n    b.PROCESSLIST_ID PROCESSLIST_ID,\n    ENGINE_TRANSACTION_ID trx_id,\n    object_name,\n    INDEX_NAME,\n    lock_type,\n    lock_mode,\n    lock_status,\n    LOCK_DATA,\n    c.BLOCKING_ENGINE_TRANSACTION_ID blk_trx_id,\n    c.BLOCKING_THREAD_ID blk_thd_id \nFROM\n    PERFORMANCE_SCHEMA.data_locks a\n    LEFT JOIN PERFORMANCE_SCHEMA.threads b ON a.thread_id = b.thread_id\n    LEFT JOIN PERFORMANCE_SCHEMA.data_lock_waits c ON a.ENGINE_TRANSACTION_ID = c.REQUESTING_ENGINE_TRANSACTION_ID \n    AND a.thread_id = c.REQUESTING_THREAD_ID \nWHERE\n    a.thread_id = b.thread_id \n    AND a.OBJECT_SCHEMA = 'db1'\n    AND a.OBJECT_NAME = 'testdum' \nORDER BY\n    thread_id,\n    trx_id;<\/code><\/pre>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2023\/05\/image-1683712252662.png\" alt=\"file\" \/><\/p>\n<p>\u53ef\u4ee5\u770b\u5230<code>WAITING<\/code>\u72b6\u6001\u7684\u8fd9\u6761sql\uff0c\u88ab<code>BLOCKING_THREAD_ID<\/code>\u4e3a2704\u7684\u7ebf\u7a0b\u963b\u585e\uff0c\u800c2704\u7684<code>PROCESSLIST_ID<\/code>\u4e3a2646\uff0c<\/p>\n<p>\u6b64\u65f6\uff0c\u53ef\u4ee5\u6267\u884c<code>kill<\/code>\u547d\u4ee4\u7ec8\u6b62\u8be5\u8fdb\u7a0b\u3002<\/p>\n<pre><code class=\"language-sql\">kill 2646;<\/code><\/pre>\n<p>\u6b64\u65f6session1\u5df2\u5904\u4e8e\u6740\u6b7b\u72b6\u6001\uff0c\u65e0\u6cd5\u518d\u6267\u884csql\uff0c\u518d\u6267\u884c\u4f1a\u62a5\u9519 \uff1a<br \/>\n<font color=red>ERROR 2013 (HY000): EOF (errno 2013) (sqlstate HY000) during query: show tables kunErr(kungate: <a href=\"http:\/\/XXX:25001\/\">http:\/\/XXX:25001\/<\/a>)<\/font><\/p>\n<p>session2\u518d\u6b21\u6267\u884c\u53ef\u4ee5\u8fd0\u884c\u6210\u529f\u3002<\/p>\n<h4>FAQ<\/h4>\n<p><strong>sys.innodb_lock_waits \u89c6\u56fe\u7684\u5217\u53ca\u5176\u542b\u4e49\u5982\u4e0b\uff1a<\/strong><\/p>\n<blockquote>\n<p>sys.innodb_lock_waits\uff1a\u5728MySQL 5.7\u4e2d\uff0c\u8fd9\u4e2a\u89c6\u56fe\u7b80\u5316\u4e86information_schema.innodb_lock_waits\u7684\u4f7f\u7528\uff0c\u901a\u8fc7\u8fde\u63a5\u5176\u4ed6\u51e0\u4e2a\u8868\u6765\u63d0\u4f9b\u66f4\u76f4\u89c2\u7684\u9501\u7b49\u5f85\u4fe1\u606f\u3002\u5728MySQL 8.0\u4e2d\uff0c\u5b83\u8fdb\u4e00\u6b65\u7b80\u5316\u4e86\u89c6\u56fe\u5b9a\u4e49\uff0c\u5e76\u4e14\u4f7f\u7528\u4e86performance_schema\u4e2d\u7684\u8868\u6765\u63d0\u4f9b\u66f4\u8be6\u7ec6\u7684\u9501\u7b49\u5f85\u4fe1\u606f\u3002<\/p>\n<\/blockquote>\n<p>wait_started: \u9501\u7b49\u5f85\u5f00\u59cb\u7684\u65f6\u95f4\u3002<br \/>\nwait_age: \u9501\u7b49\u5f85\u7684\u6301\u7eed\u65f6\u95f4\uff08\u4ee5\u79d2\u4e3a\u5355\u4f4d\uff09\u3002<br \/>\nobject_schema: \u6d89\u53ca\u7684\u6570\u636e\u5e93\u540d\u3002<br \/>\nobject_name: \u6d89\u53ca\u7684\u8868\u540d\u3002<br \/>\nindex_name: \u6d89\u53ca\u7684\u7d22\u5f15\u540d\u3002<br \/>\nlock_type: \u9501\u7c7b\u578b\uff08\u5982 RECORD\u3001TABLE \u7b49\uff09\u3002<br \/>\nlock_mode: \u9501\u6a21\u5f0f\uff08\u5982 S\u3001X\u3001IS\u3001IX \u7b49\uff09\u3002<br \/>\nlock_status: \u9501\u72b6\u6001\uff08\u5982 GRANTED\u3001WAITING\uff09\u3002<br \/>\nwaiting_trx_id: \u8bf7\u6c42\u9501\u7684\u4e8b\u52a1 ID\u3002<br \/>\nwaiting_query: \u8bf7\u6c42\u9501\u7684 SQL \u67e5\u8be2\u3002<br \/>\nwaiting_pid: \u8bf7\u6c42\u9501\u7684\u8fdb\u7a0b ID\u3002<br \/>\nwaiting_lock_id: \u8bf7\u6c42\u7684\u9501 ID\u3002<br \/>\nwaiting_lock_type: \u8bf7\u6c42\u7684\u9501\u7c7b\u578b\u3002<br \/>\nwaiting_lock_mode: \u8bf7\u6c42\u7684\u9501\u6a21\u5f0f\u3002<br \/>\nblocking_trx_id: \u6301\u6709\u9501\u7684\u4e8b\u52a1 ID\u3002<br \/>\nblocking_query: \u6301\u6709\u9501\u7684 SQL \u67e5\u8be2\u3002<br \/>\nblocking_pid: \u6301\u6709\u9501\u7684\u8fdb\u7a0b ID\u3002<br \/>\nblocking_lock_id: \u88ab\u8bf7\u6c42\u7684\u9501 ID\u3002<br \/>\nblocking_lock_type: \u6301\u6709\u7684\u9501\u7c7b\u578b\u3002<br \/>\nblocking_lock_mode: \u6301\u6709\u7684\u9501\u6a21\u5f0f\u3002<br \/>\nsql_kill_blocking_query: \u53ef\u4ee5\u7528\u6765\u7ec8\u6b62\u6301\u6709\u9501\u7684\u67e5\u8be2\u7684 SQL \u547d\u4ee4\u3002<br \/>\nsql_kill_blocking_connection: \u53ef\u4ee5\u7528\u6765\u7ec8\u6b62\u6301\u6709\u9501\u7684\u8fde\u63a5\u7684 SQL \u547d\u4ee4\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u6982\u8981\u63cf\u8ff0 KunDB \u4e2d\uff0c\u5f53\u4e24\u4e2a\u6216\u591a\u4e2a\u4e8b\u52a1\u6c38\u4e45\u963b\u585e\u5e76\u7b49\u5f85\u5bf9\u65b9\u91ca\u653e\u9501\u65f6\uff0c\u4f1a\u53d1\u751f\u6b7b\u9501\u3002\u6b64\u65f6\u53ef\u4ee5\u9009\u62e9\u7ec8\u6b62\u5176\u4e2d\u4e00\u4e2a\u4e8b\u52a1 ..<\/p>\n<div class=\"clear-fix\"><\/div>\n<p><a href=\"https:\/\/kbwp.transwarp.cn\/?p=10217\" title=\"read more...\">Read more<\/a><\/p>\n","protected":false},"author":12,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[40],"tags":[],"class_list":["post-10217","post","type-post","status-publish","format-standard","hentry","category-sql_issue"],"acf":[],"_links":{"self":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/10217","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/users\/12"}],"replies":[{"embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=10217"}],"version-history":[{"count":4,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/10217\/revisions"}],"predecessor-version":[{"id":16584,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/10217\/revisions\/16584"}],"wp:attachment":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=10217"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=10217"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=10217"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}