Improvements for XA in MySQL 5.7

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 | REPEATABLE READ | ACTIVE | 0x0101 | PREPARED | READ WRITE  |
+-----------------+-----------------+--------+--------+----------+-------------+
5 rows in set (0.00 sec)

This is not only more readable but allows you to use functions on the output. It is also much more complete as all transactions are listed here, not only XA transactions.
 
Update: It's not only possible with performance_schema, In 5.7 there is also a new option to print the xid in hex for XA RECOVER:

mysql> XA RECOVER;
+----------+--------------+--------------+------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+------+
|        1 |            2 |            0 |      |
+----------+--------------+--------------+------+
1 row in set (0.00 sec)

mysql> XA RECOVER CONVERT XID;
+----------+--------------+--------------+--------+
| formatID | gtrid_length | bqual_length | data   |
+----------+--------------+--------------+--------+
|        1 |            2 |            0 | 0x0001 |
+----------+--------------+--------------+--------+
1 row in set (0.00 sec)

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

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
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 3, OS thread handle 0x7ff0c8727700, query id 202 localhost msandbox init
show engine innodb status
---TRANSACTION 8972, ACTIVE 1313 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 2, OS thread handle 0x7ff0c8768700, query id 106 localhost msandbox update
insert into t1 values(4)
------- TRX HAS BEEN WAITING 1313 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 18 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`t1` trx id 8972 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

------------------
---TRANSACTION 8970, ACTIVE 1652 sec
4 lock struct(s), heap size 1184, 10 row lock(s), undo log entries 9
MySQL thread id 1, OS thread handle 0x7ff0c87a9700, query id 21 localhost msandbox cleaning up

So there are 10 row locks held by transaction 8970 and transaction 8972 is waiting on record lock on test.t1. That's nothing new, that information is also in INNODB_LOCKS and INNODB_LOCK_WAITS.

But if we set  innodb_status_output_locks to ON this changes.

mysql [information_schema] > set global innodb_status_output_locks=ON;
Query OK, 0 rows affected (0.00 sec)

mysql [information_schema] > show engine innodb status\G
...some output removed...
------------
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
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 3, OS thread handle 0x7ff0c8727700, query id 204 localhost msandbox init
show engine innodb status
---TRANSACTION 8972, ACTIVE 1544 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 2, OS thread handle 0x7ff0c8768700, query id 106 localhost msandbox update
insert into t1 values(4)
------- TRX HAS BEEN WAITING 1544 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 18 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`t1` trx id 8972 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

------------------
TABLE LOCK table `test`.`t1` trx id 8972 lock mode IX
RECORD LOCKS space id 18 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`t1` trx id 8972 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

---TRANSACTION 8970, ACTIVE 1883 sec
4 lock struct(s), heap size 1184, 10 row lock(s), undo log entries 9
MySQL thread id 1, OS thread handle 0x7ff0c87a9700, query id 21 localhost msandbox cleaning up
TABLE LOCK table `test`.`t1` trx id 8970 lock mode IX
RECORD LOCKS space id 18 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`t1` trx id 8970 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000313; asc       ;;
 1: len 6; hex 000000001f4e; asc      N;;
 2: len 7; hex bd000001750110; asc     u  ;;
 3: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000314; asc       ;;
 1: len 6; hex 000000001f4e; asc      N;;
 2: len 7; hex bd00000175011e; asc     u  ;;
 3: len 4; hex 80000002; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000315; asc       ;;
 1: len 6; hex 000000001f4e; asc      N;;
 2: len 7; hex bd00000175012c; asc     u ,;;
 3: len 4; hex 80000003; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000406; asc       ;;
 1: len 6; hex 000000002113; asc     ! ;;
 2: len 7; hex 92000001800110; asc        ;;
 3: len 4; hex 80000004; asc     ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 00000000040c; asc       ;;
 1: len 6; hex 000000002116; asc     ! ;;
 2: len 7; hex 95000001590110; asc     Y  ;;
 3: len 4; hex 80000004; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000412; asc       ;;
 1: len 6; hex 00000000211c; asc     ! ;;
 2: len 7; hex 990000015c0110; asc     \  ;;
 3: len 4; hex 80000004; asc     ;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000419; asc       ;;
 1: len 6; hex 00000000211e; asc     ! ;;
 2: len 7; hex 9b0000014d0110; asc     M  ;;
 3: len 4; hex 80000004; asc     ;;

Record lock, heap no 9 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000500; asc       ;;
 1: len 6; hex 00000000230a; asc     # ;;
 2: len 7; hex 8a0000013c0110; asc     <  ;;
 3: len 4; hex 80000004; asc     ;;

TABLE LOCK table `tmp`.`#sql2cfe_1_0` trx id 8970 lock mode IX
RECORD LOCKS space id 18 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`t1` trx id 8970 lock_mode X locks rec but not gap
Record lock, heap no 9 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000500; asc       ;;
 1: len 6; hex 00000000230a; asc     # ;;
 2: len 7; hex 8a0000013c0110; asc     <  ;;
 3: len 4; hex 80000004; asc     ;;
...some output removed...

Now we get to see the full information about the InnoDB locks.

There is one more thing in the SHOW ENGINE INNODB STATUS output you should pay attention to: deadlocks.

As you can see the record locks use the GEN_CLUST_INDEX index which is the index that is used if no primary key is specified. For some locking issues adding one or more indexes can fix the issue as InnoDB then doesn't need to take as many row locks.

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.

XA Transactions between TokuDB and InnoDB

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';
Query OK, 0 rows affected (0.00 sec) 

This show a transaction with a transaction ID of 'demo01'.    

XA START Starts the transaction and puts it in ACTIVE state.  
XA END Ends the transaction and puts it IDLE state.
XA PREPARE Will prepare the transaction and puts it in PREPARED state.  
Then XA COMMIT can be used to COMMIT the transaction or XA ROLLBACK can be used to rollback the transaction.    

Demo 2: XA Transaction between 2 nodes:

mysql55-tokudb6> XA START 'tr01';
Query OK, 0 rows affected (0.00 sec)

mysql56-innodb> XA START 'tr01';
Query OK, 0 rows affected (0.01 sec)

mysql55-tokudb6> INSERT INTO xatest(name) VALUES('tr01');
Query OK, 1 row affected (0.00 sec)

mysql56-innodb> INSERT INTO xatest(name) VALUES('tr01');
Query OK, 1 row affected (0.00 sec)

mysql55-tokudb6> XA END 'tr01';
Query OK, 0 rows affected (0.00 sec)

mysql56-innodb> XA END 'tr01';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> XA PREPARE 'tr01';
Query OK, 0 rows affected (0.00 sec)

mysql56-innodb> XA PREPARE 'tr01';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> XA COMMIT 'tr01';
Query OK, 0 rows affected (0.00 sec)

mysql56-innodb> XA COMMIT 'tr01';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> SELECT * FROM xatest;
+----+------+
| id | name |
+----+------+
|  1 | tr01 |
+----+------+
1 row in set (0.00 sec)

mysql56-innodb> SELECT * FROM xatest;
+----+------+
| id | name |
+----+------+
|  1 | tr01 |
+----+------+
1 row in set (0.00 sec) 

Demo 3: XA Transaction with rollback:

mysql55-tokudb6> XA START 'tr02';
Query OK, 0 rows affected (0.00 sec)

mysql56-innodb> XA START 'tr02';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> INSERT INTO xatest(name) VALUES('tr02');
Query OK, 1 row affected (0.00 sec)

mysql56-innodb> INSERT INTO xatest(name) VALUES('tr02');
Query OK, 1 row affected (0.00 sec)

mysql55-tokudb6> XA END 'tr02';
Query OK, 0 rows affected (0.00 sec)

mysql56-innodb> XA END 'tr02';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> XA PREPARE 'tr02';
Query OK, 0 rows affected (0.00 sec)

mysql56-innodb> XA PREPARE 'tr02';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> XA ROLLBACK 'tr02';
Query OK, 0 rows affected (0.00 sec)

mysql56-innodb> XA ROLLBACK 'tr02';
Query OK, 0 rows affected (0.00 sec)

mysql56-innodb> XA ROLLBACK 'tr02';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> SELECT * FROM xatest;
+----+------+
| id | name |
+----+------+
|  1 | tr01 |
+----+------+
1 row in set (0.00 sec)

mysql56-innodb> SELECT * FROM xatest;
+----+------+
| id | name |
+----+------+
|  1 | tr01 |
+----+------+
1 row in set (0.00 sec)

These transactions are between two MySQL instances: One with InnoDB and one with TokuDB. It's possible to run TokuDB and InnoDB both in one database instance, but separating them between instances (and hosts) might be needed for performance or some other reason.

It's possible to run a transaction with TokuDB and a PostgreSQL database:

mysql55-tokudb6> XA START 'tr03';
Query OK, 0 rows affected (0.00 sec)

xatest=# BEGIN;
BEGIN

mysql55-tokudb6> INSERT INTO xatest(name) VALUES('tr03');
Query OK, 1 row affected (0.00 sec)

mysql55-tokudb6> DELETE FROM xatest WHERE name='tr02';
Query OK, 0 rows affected (0.00 sec)

xatest=# INSERT INTO xatest(name) VALUES('tr03');
INSERT 0 1

mysql55-tokudb6> XA END 'tr03';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> XA PREPARE 'tr03';
Query OK, 0 rows affected (0.00 sec)

xatest=# PREPARE TRANSACTION 'tr03';
PREPARE TRANSACTION

mysql55-tokudb6> XA COMMIT 'tr03';
Query OK, 0 rows affected (0.00 sec)

xatest=# COMMIT PREPARED 'tr03';
COMMIT PREPARED

For PostgreSQL this only works if max_prepared_transactions is set to a non-zero value.

Transactions can not only run between databases but applications, filesystems and many other components can also be a member of the transactions. They need to support two-phase commit.

An XA Transaction is coordinated by a transaction coordinator which can be an application on a application server.