Hi, I have an application where I would like the ability to issue a SELECT statement to a MySQL master, retrieve the query result, and then observe subsequent writes within the binlog that could potentially modify the result set of this SELECT statement. (Essentially, I want to be able to identify all writes within the binlog that have occurred “after” the SELECT statement read snapshot.) I will be issuing many such SELECT statements, so I would like to issue them in as performant a manner as possible. (I would not want to take a table lock when when issuing each SELECT statement, for example.) I will have gtid replication enabled, using InnoDB with a recent MySQL version.
It seems like one possible avenue of approaching this might be to, within one transaction, issue the SELECT statement, and also query the mysql.gtid_executed table to check the gtid execution status. I have read in [1] that the mysql.gtid_executed table is updated “at transaction commit time”. However, I am not clear on whether the update to the mysql.gtid_executed table is transactionally consistent with the transaction itself or if it occurs just after the transaction. I understand from [2] that the gtid values in the gtid_executed system variable (rather than the mysql.gtid_executed table) are “externalized non-atomically (very shortly after the transaction is committed)”. I also understand from [1] that InnoDB has a thread that “collects GTIDs in groups, flushes them to the mysql.gtid_executed table, then compresses the table.”
It would be great if you could help me determine:
- Is it feasible to read from the mysql.gtid_executed table as described in the paragraph above to identify exactly those transactions that have occurred “before” a SELECT statement read snapshot?
- Are there any other methods of issuing a SELECT statement and identifying exactly those transactions that have occurred “before” the statement read snapshot? (I’m particularly interested in knowing what the most performant options available would be.)
I also understand that other software exists that can be used for monitoring data change events. At the moment I am specifically interested in the feasibility of doing so using the MySQL binlog (and I have experience reading, parsing, and interpreting events from the binlog).
Thank you for your assistance!
[1]
https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-concepts.html
[2]
https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-lifecycle.html