Handling locking & kill the connection in MySQL
Generate the Lock Wait
--create table
Create table t (a varchar(1),b varchar(1),c varchar(1));
-- Run the following query from one session
BEGIN;
SELECT a FROM t FOR UPDATE;
SELECT SLEEP(1000);
-- run the insert query from another session
mysql> insert into t values (1,1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Check the locking occurred
You get the blocking PID from column value from the following query.
mysql> SELECT waiting_trx_id, waiting_pid, waiting_query, blocking_trx_id, blocking_pid, blocking_query FROM sys.innodb_lock_waits;
+----------------+-------------+------------------------------+-----------------+--------------+--------------------+ | waiting_trx_id | waiting_pid | waiting_query | blocking_trx_id | blocking_pid | blocking_query | +----------------+-------------+------------------------------+-----------------+--------------+--------------------+ | 2272 | 54 | insert into t values (1,1,1) | 2271 | 52 | SELECT SLEEP(1000) | +----------------+-------------+------------------------------+-----------------+--------------+--------------------+ 1 row in set (0.00 sec)
Note: blocking PID is thread id for the session.
Check the process list
show processlist;
mysql> show processlist; +----+-----------------+-----------------+------+---------+--------+------------------------+--------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------------+------+---------+--------+------------------------+--------------------+ | 4 | event_scheduler | localhost | NULL | Daemon | 357289 | Waiting on empty queue | NULL | | 47 | root | localhost:54316 | NULL | Sleep | 445 | | NULL | | 51 | root | localhost:54764 | NULL | Sleep | 445 | | NULL | | 52 | root | localhost:59923 | test | Query | 75 | User sleep | SELECT SLEEP(1000) |> | 54 | root | localhost:59933 | test | Sleep | 55 | | NULL | | 55 | root | localhost:59937 | test | Query | 0 | starting | show processlist | +----+-----------------+-----------------+------+---------+--------+------------------------+--------------------+ 6 rows in set (0.00 sec)
Kill the Session and connection in MySQL
--Kill the complete connection.
KILL thread_id;
KILL CONNECTION thread_id;
--Kill the current executing statement, but leave the connection.
KILL QUERY thread_id;
Example:
mysql> kill 52;
Query OK, 0 rows affected (0.00 sec)
Check the connection is lost & query is executed
---One session query is executed
mysql> insert into t values (1,1,1);
Query OK, 1 row affected (34.36 sec)
--Another session is lost
mysql> SELECT a FROM t FOR UPDATE;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select a from t;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 56
Current database: test
Smart way of Technology 
Pingback: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction | Smart way of Technology