Prevent read when updating the table
database, mysql
Solution
Assuming you use a transactional engine (Usually Innodb), clear and refill the table in the same transaction.
Be sure that your readers use READ_COMMITTED or higher transaction isolation level (the default is REPEATABLE READ which is higher).
That way readers will continue to be able to read the old contents of the table during the update.
There are a few things to be careful of:
- If the table is so big that it exhausts the rollback area - this is possible if you update the whole of (say) a 1M row table. Of course this is tunable but there are limits
- If the transaction fails part way through and gets rolled back - rolling back big transactions is VERY inefficient in InnoDB (it is optimised for commits, not rollbacks)
- Be careful of deadlocks and lock wait timeouts, which are more likely if you use big transactions.
Problem
In MySQL: Every one minute I empty the table and fill it with a new data. Now I want that users should not read data during the fill process, before or after is ok. How do I achieve this? Is transaction the way?