Databases Reference
In-Depth Information
• Line 4: the current transaction identifier, which is a system variable that increments
for each new transaction.
• Line 5: the transaction ID to which InnoDB has purged old MVCC row versions.
You can see how many old versions haven't yet been purged by looking at the
difference between this value and the current transaction ID. There's no hard and
fast rule as to how large this number can safely get. If nothing is updating any data,
a large number doesn't mean there's unpurged data, because all the transactions
are actually looking at the same version of the database. On the other hand, if many
rows are being updated, one or more versions of each row is staying in memory.
The best policy for reducing overhead is to ensure that transactions commit when
they're done instead of staying open a long time, because even an open transaction
that doesn't do any work keeps InnoDB from purging old row versions.
Also in line 5: the undo log record number InnoDB's purge process is currently
working on, if any. If it's “0 0”, as in our example, the purge process is idle.
• Line 6: the history list length, which is the number of pages in the undo space in
InnoDB's data files. When a transaction performs updates and commits, this num-
ber increases; when the purge process removes the old versions, it decreases. The
purge process also updates the value in line 5.
• Line 7: the number of lock structs. Each lock struct usually holds many row locks,
so this is not the same as the number of rows locked.
The header is followed by a list of transactions. Current versions of MySQL don't
support nested transactions, so there's a maximum of one transaction per client con-
nection at a time, and each transaction belongs to only a single connection. Each trans-
action has at least two lines in the output. Here's a sample of the minimal information
you'll see about a transaction:
1 ---TRANSACTION 0 3793494, not started, process no 5488, OS thread id 1141152064
2 MySQL thread id 15, query id 479 localhost baron
The first line begins with the transaction's ID and status. This transaction is “not
started,” which means it has committed and not issued any more statements that affect
transactions; it's probably just idle. Then there's some process and thread information.
The second line shows the MySQL process ID, which is also the same as the
Id
column
in
SHOW FULL PROCESSLIST
. This is followed by an internal query number and some
connection information (also the same as what you can find in
SHOW FULL PROCESSLIST
).
Each transaction can print much more information than that, though. Here's a more
complex example:
1 ---TRANSACTION 0 80157600, ACTIVE 4 sec, process no 3396, OS thread id 1148250464,
thread declared inside InnoDB 442
2 mysql tables in use 1, locked 0
3 MySQL thread id 8079, query id 728899 localhost baron Sending data
4 select sql_calc_found_rows * from b limit 5
5 Trx read view will not see trx with id>= 0 80157601, sees <0 80157597
