Sample Header Ad - 728x90

Write-lock a whole table during transaction

3 votes
1 answer
606 views
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.
Asked by davide (131 rep)
Apr 27, 2015, 04:19 AM
Last activity: Jul 13, 2025, 11:09 PM