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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.