Home » Finding Mysql blocking queries and Deadlocks

Finding Mysql blocking queries and Deadlocks

  • by

Blocked queries:

Blocked queries can occur because a transaction in InnoDB is waiting for another transaction to release a lock. Queries can also be blocked because of uncommitted transactions (can appear as NULL). Follow the steps below to identify the query or session that might be blocking your query.

How to find the blocking queries:

Identify uncommitted transactions
1. View currently running transactions by executing the following query against the INNODB_TRX table:

select * from information_schema.innodb_trx\G
2. Run the following query to see which transactions are waiting and which transactions are blocking them:

SELECT
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_trx_id;

The blocked transaction can’t proceed until the other transaction commits or rolls back.

When identifying blocking transactions, a NULL value is reported for the blocking query if the session that issued the query has become idle. In this case, use the query in Step 2 to find the blocking_thread processlist ID.

 

3. From MySQL 5.7 onwards, run the following query to determine the THREAD_ID of the blocking transaction by the substituting blocking_thread processlist ID:

SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = blocking_thread;


4. Use the THREAD_ID to query the performance schema events_statements_current table. This determines the last query executed by the thread.

Make sure to substitute the THREAD_ID with value returned in Step 3.

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID = THREAD_ID;
5. After you identify the blocking session or thread-ID, stop the transaction by running the following procedure:

Understanding Kill statement:

Each connection to mysqld runs in a separate thread. You can see which threads are running with the SHOW PROCESSLIST statement and kill a thread with the KILL thread_id statement. KILL allows the optional CONNECTION or QUERY modifier:

KILL CONNECTION is the same as KILL with no modifier: It terminates the connection associated with the given thread or query id.
KILL QUERY terminates the statement that the connection thread_id is currently executing, but leaves the connection itself intact.
KILL QUERY ID (introduced in MariaDB 10.0.5) terminates the query by query_id, leaving the connection intact.
If a connection is terminated that has an active transaction, the transaction will be rolled back. If only a query is killed, the current transaction will stay active. See also idle_transaction_timeout.

If you have the PROCESS privilege, you can see all threads. If you have the SUPER privilege, or, from MariaDB 10.5.2, the CONNECTION ADMIN privilege, you can kill all threads and statements. Otherwise, you can see and kill only your own threads and statements.

Stopping or rolling back a long running operation can be time-consuming and IO intensive in DBMS.

Deadlocks:

In a database, a deadlock is an unwanted situation in which two or more transactions are waiting indefinitely for one another to give up .

We can simulate a deadlock by below transactions

From connection on mysql run below commands

Start Transaction;

UPDATE employees SET birth_date='1953-09-23' where emp_no=10001;

DO Sleep(20);

Update salaries SET salary=80000 where emp_No=10001 and from_date='1986-06-26';

 

From another connection on mysql run below commands

Start Transaction;

Update salaries SET salary=90000 where emp_No=10001 and from_date='1986-06-26';

DO Sleep(20);

UPDATE employees SET birth_date='1953-09-24' where emp_no=10001;

To View Deadlocks:

show processlist;

Show Engine INNODB STATUS\G

SELECT * FROM performance_schema.events_statements_history WHERE thread_id = (SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = ID) \G

To enable Deadlocks information to error log

SET GLOBAL innodb_print_all_deadlocks=ON;

To view the log File

cat /var/log/mysqld.log

Leave a Reply

Your email address will not be published.