Debugging Transactional And Locking Issues in MySQL

Introduction

Database transactions are a logical unit of work or a group of related changes executed against a database. Transactions enable us to execute reliable units of work against a database and recover in case of failures. Transactions are “all-or-nothing” i.e they enable us to either execute all changes within the logical group or have no effect on the database at all. This enables us to keep the database in a consistent state even in the case of failures. A database transaction must be ACID i.e. atomic, consistent, isolated and durable. Most applications will want to surround database access/changes within transactions to maintain a consistent database state.

Occasionally one comes across a situation where applications creates erroneous transactions which leads to corrupted data and/or database locking issues. Database locking issues often cause the application to hang. Debugging transactional and locking issues in a database can be tricky business. In this blog post I will introduce some basic MySQL tools to enable one to identify and debug transaction and locking issues. Note I am assuming that you are using InnoDB as your MySQL engine.

MySQL Process List

In order to debug any transactional issue we first need to have a list of all active connections to our target database. List of all active connections can found by executing the following query:

Select * from information_schema.processlist;

If the output from the above query is overwhelming you can always filter the result set by using the DB and/or USER column. I often use the DB column and will execute the following query:

Select ID from information_schema.processlist where DB='MY_TARGET_DB_NAME';

MySQL General Log

Next we want to access to a log of all queries executed against a particular database. MySQL provides a global query log which can be enabled by turning on MySQL's general log. The MySQL general log captures all queries executed against the server. An import point to keep in mind is that MySQL writes queries to the general log in the order that it receives them which might be different that the order it actually executes them in. The general log can either be written to a log file or to a table. I prefer writing to a table as it is easier to filter the log based on one’s current needs. This also has the added benefit of not needing access to the machine where mysql is installed. The general_log table is created during installation in the MySQL database and can be queried using the query below:

Select * from mysql.general_log;

Note prior to MySQL 5.1.6 the general log could only be outputted to log files.

General logging to a table can be enabled by executing the following queries:

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

I generally like to filter the general log using a database name. The general log logs every query executed against your MySQL installation and thus information provided by the general log is often overwhelming. I use the query below to filter queries by database name.

SELECT 
    *
FROM
    mysql.general_log
WHERE
    thread_id IN (SELECT 
            ID
        FROM
            information_schema.processlist
        WHERE
            DB = 'YOUR_DB_NAME');

SET global general_log = 'OFF'; 

Deletion of existing log entries can be achieved using:

TRUNCATE table mysql.general_log;

MySQL Active Transactions and Locks

Next we want a list of active transactions and locks that are currently executing against our target database. MySQL captures transaction and lock information in the INNODB_TRX, INNODB_LOCKS, and INNODB_LOCK_WAITS INFORMATION_SCHEMA tables. INNODB_TRX provides us with information on the currently executing transactions. INNODB_LOCKS gives us information on current lock while the INNODB_LOCK_WAITS provides information on who is actually waiting for locks. The query below enables one to see active locks filtered by a particular database:

SELECT 
    tw_ps.DB waiting_trx_db,
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    bt_ps.DB blocking_trx_db,
    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
		INNER JOIN information_schema.processlist tw_ps ON tw_ps.ID = r.trx_id
        		INNER JOIN information_schema.processlist bt_ps ON bt_ps.ID = b.trx_id
WHERE  r.trx_id in (Select ID FROM information_schema.processlist where DB='YOUR_DB_NAME') ;               

The above query is extremely useful as it outputs both the blocking and blocked query which is helpful in debugging locking issues.

Conclusion

A good application debugger, patience and the the above queries will enable you to get to the bottom of most transactional and locking issues.