Prevent read when updating the table

In MySQL:

Every one minute I empty the table and fill it with a new开发者_StackOverflow 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?

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.

You can LOCK your table for the duration of your operation:

http://dev.mysql.com/doc/refman/5.1/en/lock-tables.html

A table lock protects only against inappropriate reads or writes by other sessions. The session holding the lock, even a read lock, can perform table-level operations such as DROP TABLE. Truncate operations are not transaction-safe, so an error occurs if the session attempts one during an active transaction or while holding a table lock.

I don't know enough about the internal row-versioning mechanisms of MySql (or indeed, if there is one), but other databases (Oracle, Postgresql, and more recently, Sql Server) have invested a lot of effort into allowing writers to not block readers, in so far as readers have access to the version of the rows that existed immediately before the update/write process started. Once the update is committed, that version of the row becomes the one made availabe to all readers, thereby avoiding a bottleneck that the above behaviour in MySql will introduce.

This policy ensures that table locking is deadlock free. There are, however, other things you need to be aware of about this policy: If you are using a LOW_PRIORITY WRITE lock for a table, it means only that MySQL waits for this particular lock until there are no other sessions that want a READ lock. When the session has gotten the WRITE lock and is waiting to get the lock for the next table in the lock table list, all other sessions wait for the WRITE lock to be released. If this becomes a serious problem with your application, you should consider converting some of your tables to transaction-safe tables.

You can load your data into a shadow table as slowly as you like, then instantly swap the shadow and actual with RENAME TABLE:

truncate table shadow;     # make sure it is clean to start with
insert into shadow .....;  # lots of inserts etc against shadow table
rename table active to temp, shadow to active, temp to shadow;
truncate table shadow;     # throw away the old active data

The rename statement is atomic. An intermediate name "temp" is used to help swap the names of temp and active.

This should work with all storage engines.

Rename table - MySQL Manual