更新表时禁止读取

Question

在MySQL中：

每隔一分钟，我清空该表，并用新数据填充它。现在我希望用户不应该在填充过程中读取数据，之前或之后都可以。

我该如何实现这一点？事务是解决问题的方法吗？

Answer 1

假设您使用事务引擎(通常是Innodb)，在同一事务中清除并重新填充表。

确保您的读取器使用READ_COMMITTED或更高的事务隔离级别(缺省值是更高的REPEATABLE READ )。

这样，在更新期间，读取器将继续能够读取表的旧内容。

有几件事需要注意：

• 如果表太大以至于耗尽了回滚区-如果您更新(比方说)整个1M行表，这是可能的。当然，这是可调的，但如果事务在中途失败并被回滚，
• 会受到限制-在InnoDB中，回滚大型事务的效率非常低(它针对提交而不是回滚进行了优化)
• 要小心死锁和锁定等待超时，如果您使用大型事务，则更有可能发生这些情况。

Answer 2

您可以在操作期间对表执行LOCK操作：

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

表锁仅针对其他会话的不适当读取或写入提供保护。持有锁的会话，即使是读锁，也可以执行表级操作，比如DROP TABLE。截断操作不是事务安全的，因此，如果会话在活动事务期间或持有表锁时尝试执行截断操作，则会发生错误。

我不太了解MySql的内部行版本控制机制(实际上，如果有的话)，但是其他数据库(Oracle、Postgresql和最近的Sql Server)已经投入了大量的精力来允许写入器而不是块读取器，前提是读取器可以访问更新/写入过程开始之前存在的行的版本。一旦提交了更新，行的该版本就变成了对所有读取器可用的版本，从而避免了MySql中的上述行为将带来的瓶颈。

此策略确保表锁定是无死锁的。但是，关于该策略还需要注意其他一些事情:如果对表使用LOW_PRIORITY写锁，这意味着MySQL只会等待这个特定的锁，直到没有其他会话需要读锁为止。当会话获得写锁并等待获得锁表列表中下一个表的锁时，所有其他会话都会等待释放写锁。如果这成为您的应用程序的一个严重问题，您应该考虑将某些表转换为事务安全的表。

Answer 3

您可以将数据加载到影子表中，只要您喜欢，然后立即将影子和实际数据与重命名表互换：

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

rename语句是原子的。中间名" temp“用于帮助交换temp和active的名称。

这应该适用于所有的存储引擎。

Rename table - MySQL Manual