Gtrid is a global transaction identifier.An XID value has from one to three parts: | formatID | gtrid_length | bqual_length | data | We can get the list of all XA Prepared transactions by running XA RECOVER To commit or rollback these XA transactions we need to know the XID of each XA transaction mysql> XA RECOVER TRANSACTION 28992918, ACTIVE (PREPARED) 1072517 sec recovered trx5 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 8-TRANSACTION 234688, ACTIVE (PREPARED) 1072517 sec recovered trx3 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 6-TRANSACTION 25949, ACTIVE (PREPARED) 1072517 sec recovered trx1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 1 We could see from the Show Engine InnoDB Status Output, that the transaction has reached the Prepared state,\. XA transactions will enter into a PREPARED state when the transactions are either not committed (XA COMMIT ) or not rolled back (XA ROLLBACK) XA Transaction Flow Flow chart of XA transaction Hence, the local transaction which we are trying to initiate is not able to acquire the lock as they are mutually exclusive. In MySQL 8.0.28 and earlier, XA transactions and local (non-XA) transactions are mutually exclusive. ( XA Changes in 8.0.29 ) For example, if XA START has been issued to begin an XA transaction, a local transaction cannot be started until the XA transaction has been committed or rolled back. In our case, the XA transaction has already in a PREPARED state(neither committed nor rolled back), holding the lock. mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME, LOCK_TYPE,LOCK_STATUS,SOURCE from performance_tadata_locks\G Let us view the metadata locks from the Performance Schema. On debugging it is one of the XA transactions is holding the shared write lock and causing metadata lock on all the other local transactions.
Let me provide a few backgrounds.įor one of our clients, when we try to drop a table, it is waiting for metadata lock. This is one of the exciting troubleshooting related to XA transactions let’s dive in.
0 kommentar(er)
