Disallow reading when updating a table

Question

In MySQL:

Every one minute I clear the table and fill it with new data. Now I want users to not read data during the filling process, before or after.

How do I achieve this? Is this a transaction?

Answer 1

Assuming you are using a transaction mechanism (usually Innodb), clear and add the table to the same transaction.

Make sure your readers use READ_COMMITTED or a higher level of transaction isolation (the default is REPEATABLE READ, which is higher).

In this way, readers will continue to read the old contents of the table during the update.

There are a few things to keep in mind:

• If the table is so large that it has exhausted the rollback area, this is possible if you update the entire (say) 1M row table. Of course, this is customizable, but there are limitations.
• If the transaction failed and rolls back - rollback of large transactions is VERY inefficient in InnoDB (it is optimized for commit, not rollback).
• Be careful with locks and block waiting expectations, which are more likely if you use large transactions.

Answer 2

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

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

A table lock only protects against incorrect reading or writing by other sessions. A session with a lock, even a read lock, can perform table-level operations such as DROP TABLE. Truncation operations are not a safe transaction, so an error occurs if the session attempts an active transaction or table locks.

I don’t know enough about MySql’s internal version control mechanisms (or indeed, if any), but other databases (Oracle, Postgresql and more recently Sql Server) have put a lot of effort into allowing authors to read not , as readers have access to the version of the strings that existed just before the start of the update / write process. Once the update is complete, this version of the line will be the one made available to all readers, thereby avoiding the bottleneck that will be described above in MySql.

This policy provides table locks without deadlocks. There are, however, other things you should know about this policy: if you use LOW_PRIORITY WRITE for a table, it means that MySQL is waiting for that particular castle until there are other sessions that want to READ the castle. When a WRITE session waits to get a lock for the next table in the table list lock, all other sessions wait for a WRITE lock. If this becomes a serious issue for your application, you should consider converting some of your tables into secure transactions.

Answer 3

You can upload your data to the shadow table as slowly as you like, and then instantly change the shadow and the actual data using 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 operator is atomic. The intermediate name "temp" is used to exchange the names temp and active.

This should work with all storage engines.

Rename a Table - MySQL Guide