When your query is blocked, but there is no blocking query - Part 2

In my previous post I talked about a transaction which blocked other transactions without doing anything. I talked about finding data from the blocking transaction using SYS and performance_schema.

But what are the possible solutions?

The first solution is to (automatically) kill the blocking transactions. Long running transactions can also stall the purging in InnoDB. See this blog post by Mark Leith about a possible solution.

The second solution would be make the application end the transaction sooner and/or to commit more often. Depending on your application this might or might not work. I consider this the best solution.

The third solution is to change the transaction isolation level of the blocking transaction to READ COMMITTED.

mysql [test] > set transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql [test] > start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql [test] > insert into t2 select * from t1;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

This works. The transaction won't need to lock as much as it did with REPEATABLE READ and the INSERT/DELETE on t1 from another transaction won't be blocked. Be careful to set the binlog_format to ROW before trying this as READ COMMITTED requires this to work.

mysql [test] > set session binlog_format=STATEMENT;
ERROR 1559 (HY000): Cannot switch out of the row-based binary log 
format when the session has open temporary tables
mysql [test] > drop table t2;
Query OK, 0 rows affected (0.01 sec)

mysql [test] > set session binlog_format=STATEMENT;
Query OK, 0 rows affected (0.00 sec)

mysql [test] > create temporary table t2 (id int);                              
Query OK, 0 rows affected (0.01 sec)

mysql [test] > set transaction isolation level read committed;                  
Query OK, 0 rows affected (0.00 sec)

mysql [test] > insert into t2 select * from t1;
ERROR 1665 (HY000): Cannot execute statement: impossible to write to 
binary log since BINLOG_FORMAT = STATEMENT and at least one table 
uses a storage engine limited to row-based logging. InnoDB is limited 
to row-logging when transaction isolation level is READ COMMITTED or 
READ UNCOMMITTED.

Setting the transaction isolation can also be done in my.cnf.