Handle locking & kill the connection in MySQL

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

Advertisements

1 thought on “Handle locking & kill the connection in MySQL

  1. Pingback: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction | Smart way of Technology

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.