Saturday, December 27, 2014

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)

1 comment:

  1. This is not very common that the every person who has the problem of data for making the new project always select the best data scientist for it. But it’s very important for you that you can easily solve your data problems by hiring the data scientist form this https://activewizards.com/.

    ReplyDelete