I need to perform a delicate operation to my table in which I will solely
select upon all of my rows and no God may interfere with the table during this operation: the table will be in an inconsistent state and any concurrent MySQL session shall not be allowed to modify it until
The use of
SELECT ... FOR UPDATE | LOCK IN SHARE MODE is not suitable because, while it may potentially be used to lock all the rows in the table, it won’t prevent the insertion of further rows by a concurrent session. Basically, I need to
LOCK TABLES my_table WRITE within the body of a transaction.
The table contains about 20,000 rows and a master-slave, mixed-format replication is in place over a slow connection so, for any workaround, I’d prefer to avoid using temporary tables which may faint the slave, and the amount of data dumped into the binlog should ideally be minimized.
The engine is InnoDB on MySQL 5.6, for both master and slave.
The Slave should be irrelevant, unless you are using (misusing) some form of parallel execution on the Slave.
GET_LOCK()/RELEASE_LOCK() may provide a sufficient “lock”; but all writers to that table(s) would need to use it.
Do not turn on auto-reconnect in your connection. An network glitch will mess with both the transaction and