Today I was doing some tests with XA transactions in MySQL 5.6. The output of the XA RECOVER command to list transactions was hard to read because of the representation of the data column: The good news is that 5.7 has transaction information in performance_schema: mysql> select trx_id, isolation_level, state, xid, xa_state, access_mode -> from performance_schema.events_transactions_current; +-----------------+-----------------+--------+--------+----------+-------------+ | trx_id | isolation_level | state | xid | xa_state | access_mode | +-----------------+-----------------+--------+--------+----------+-------------+ | NULL | REPEATABLE READ | ACTIVE | x-1 | PREPARED | READ WRITE | | 421476507015704 | REPEATABLE READ | ACTIVE | NULL | NULL | READ WRITE | | NULL | REPEATABLE READ | ACTIVE | foo-1 | ACTIVE | READ WRITE | | NULL | REPEATABLE READ | ACTIVE | NULL | NULL | READ ONLY | | NULL | ...

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

The recently released TokuDB brings many features. One of those features is support for XA Transactions. InnoDB already has support for XA Transactions. XA Transactions are transactions which span multiple databases and or applications. XA Transactions use 2-phase commit, which is also the same method which MySQL Cluster uses. Internal XA Transactions are used to keep the binary log and InnoDB in sync. Demo 1: XA Transaction on 1 node: mysql55-tokudb6> XA START 'demo01'; Query OK, 0 rows affected (0.00 sec) mysql55-tokudb6> INSERT INTO xatest(name) VALUES('demo01'); Query OK, 1 row affected (0.01 sec) mysql55-tokudb6> SELECT * FROM xatest; +----+--------+ | id | name | +----+--------+ | 3 | demo01 | +----+--------+ 1 row in set (0.00 sec) mysql55-tokudb6> XA END 'demo01'; Query OK, 0 rows affected (0.00 sec) mysql55-tokudb6> XA PREPARE 'demo01'; Query OK, 0 rows affected (0.00 sec) mysql55-tokudb6> XA COMMIT 'demo01...