Purpose
You can use this statement to terminate a session.
Note
If you have the PROCESS privilege, you can view all sessions. If you have the SUPER privilege, you can terminate all sessions and statements. Otherwise, you can view and terminate only the sessions and statements that you have initiated.
Syntax
KILL [CONNECTION | QUERY] 'session_id'
Parameters
| Parameter | Description |
|---|---|
| KILL CONNECTION | Like the KILL statement without a modifier, this statement can terminate a thread with the specified thread_id. |
| KILL QUERY | Terminates the ongoing statement of the connection but retains the current status of the connection. |
| session_id | The unique ID of the session. |
Examples
If business times out for a long period and an unknown long transaction in a session holds the lock. This blocks the execution of other transactions. In this case, you must find and terminate the long transaction.
Based on the transaction time, find the
trans_idof the unfinished transaction that takes the longest time to execute.obclient> SELECT * FROM __all_virtual_trans_lock_stat ORDER BY ctx_create_time LIMIT 5\G *************************** 1. row *************************** tenant_id: 1002 trans_id: {hash:6605492148156030705, inc:3284929, addr:"xxx.xxx.xx.xxx:xxxx", t:1600440036535233} svr_ip: xxx.xxx.xx.xxx svr_port: xxxx partition: {tid:1101710651081554, partition_id:0, part_cnt:0} table_id: 1101710651081554 rowkey: table_id=1101710651081554 hash=779dd9b202397d7 rowkey_object=[{"VARCHAR":"pk", collation:"utf8mb4_general_ci"}] session_id: 3221577520 proxy_id: NULL ctx_create_time: 2020-09-18 22:41:03.583285 expired_time: 2020-09-19 01:27:16.534919Find all locks held by the transaction based on its
trans_id, and identify the transaction to be stopped based on therowkey.In the following example, the
rowkeyof the first row is the same as that queried above. Therefore, the corresponding transaction is holding the lock.obclient> SELECT * FROM __all_virtual_trans_lock_stat WHERE trans_id LIKE '%hash:6605492148156030705, inc:3284929%'\G *************************** 1. row *************************** tenant_id: 1002 trans_id: {hash:6605492148156030705, inc:3284929, addr:"xxx.xxx.xx.xxx:xxxx", t:1600440036535233} svr_ip: xxx.xxx.xx.xxx svr_port: xxxx partition: {tid:1101710651081554, partition_id:0, part_cnt:0} table_id: 1101710651081554 rowkey: table_id=1101710651081554 hash=779dd9b202397d7 rowkey_object=[{"VARCHAR":"pk", collation:"utf8mb4_general_ci"}] session_id: 3221577520 proxy_id: NULL ctx_create_time: 2020-09-18 22:41:03.583285 expired_time: 2020-09-19 01:27:16.534919 *************************** 2. row *************************** tenant_id: 1002 trans_id: {hash:6605492148156030705, inc:3284929, addr:"xxx.xxx.xx.xxx:xxxx", t:1600440036535233} svr_ip: xxx.xxx.xx.xxx svr_port: xxxx partition: {tid:1101710651081554, partition_id:0, part_cnt:0} table_id: 1101710651081554 rowkey: table_id=1101710651081554 hash=89413aecf767cd7 rowkey_object=[{"VARCHAR":"ob", collation:"utf8mb4_general_ci"}] session_id: 3221577520 proxy_id: NULL ctx_create_time: 2020-09-18 22:41:03.583285 expired_time: 2020-09-19 01:27:16.534919 2 rows in setConfirm the
session_idof the transaction to be stopped, and stop the corresponding session.obclient> KILL 3221577520; Query OK, 0 rows affected
