KILL

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 your own sessions and statements.

Syntax

KILL [CONNECTION  QUERY] 'session_id'

Parameters

Examples

A service uses a large timeout event, and an unknown long-running transaction in a session holds the lock, blocking the execution of other transactions. Therefore, you must find and stop the long-running transaction.

1. Based on the transaction time, find the trans_id of 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.534919
   

2. Find all locks held by the transaction based on its trans_id, and identify the transaction to be stopped based on the rowkey.

   In the following example, the rowkey of 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 set (0.05 sec)
   

3. Confirm the session_id of the transaction to be stopped, and stop the corresponding session.

   obclient> KILL 3221577520;
   Query OK, 0 rows affected (0.00 sec)