MySQL Forums
Forum List  »  Replication

Monitoring query resultset changes using binlog events
Posted by: Drew Finlayson
Date: May 04, 2021 07:49PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Monitoring query resultset changes using binlog events
162
May 04, 2021 07:49PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.