I need to perform a delicate operation to my table in which I will solely insert, delete and 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 commit-ed.
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.
Best Answer
The Slave should be irrelevant, unless you are using (misusing) some form of parallel execution on the Slave.
Try other
transaction_isolation_modes.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
GET_LOCK.