In the previous blog posts I've talked about transactions which block other transactions but don't do anything and about some possible solutions. In this post I will show you how to get even more information about what is locked by a transaction. As you might have noticed the information_schema.innodb_locks table doesn't show all locks. This is what the documentation says: "The INNODB_LOCKS table contains information about each lock that an InnoDB transaction has requested but not yet acquired, and each lock that a transaction holds that is blocking another transaction." So if would like to know all locks held by a transaction this doesn't help. There is lots of information in the output of " SHOW ENGINE INNODB STATUS\G " in the section about TRANSACTIONS. ------------ TRANSACTIONS ------------ Trx id counter 8991 Purge done for trx's n:o < 8991 undo n:o < 0 state: running but idle History list length 50 ...

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, ...

When I queried information_schema.innodb_trx (introduced in 5.1 with the InnoDB Plugin) I noticed there were a few transactions in LOCK WAIT state. Example: mysql [information_schema] > select trx_id,trx_state  -> from information_schema.innodb_trx; +--------+-----------+ | trx_id | trx_state | +--------+-----------+ | 7974 | LOCK WAIT | | 7962 | RUNNING | +--------+-----------+ 2 rows in set (0.00 sec) Then I made a query to join a few information_schema and performance_schema tables to find out which query was blocking my transactions. It turned out that the blocking transaction had a trx_query=NONE. So my query was block by a transaction doing nothing. That's not really helpful. Let's try to recreate the situation and see exactly what happened. I'll use two sessions for the transactions and a third to monitor the server. The first session: mysql [test] > create table t1 (id int); Query OK, 0 rows affected (0.01 sec) mysql [test] > insert...

It is possible to disallow users from using old_passwords=1. This can be done by adding ' maximum-old_passwords=0 ' to your my.cnf This prevents users from generating passwords hashes in pre-4.1 format. In MySQL 5.7 old_passwords=1 is already disabled, so this is only useful on 5.6 and earlier. Be aware that this also restricts old_passwords=2 which is needed for sha256 hashes. mysql> select @@old_passwords; +-----------------+ | @@old_passwords | +-----------------+ | 0 | +-----------------+ 1 row in set (0.00 sec) mysql> set old_passwords=1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+----------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------+ | Warning | 1292 | Truncated incorrect old_passwords value: '1' | +---------+------+----------------------------------------------+ 1 r...