-
Enhancement
-
Resolution: Done
-
Major
-
None
-
None
When using the default SnapshotLockingMode, the FLUSH TABLES WITH READ LOCK statement must wait for all existing reads and writes to finish. While it's attempting to obtain that lock, new SELECT queries are blocked.
In our use of Debezium we read from dedicated read-only replicas, but we are sharing those replicas with traditional ETL jobs which run long running SELECT * FROM table WHERE <window> queries, which either block Debezium's MySQL extractor, or are blocked by it.
This issue (affecting all MySQL derivatives) is documented on the Percona Blog. An alternative locking type, which is specific to the Percona Server fork, is documented here.
MariaDB implements a similar feature with a different syntax.