原子地将一个MySQL表复制到另一个表上？

I am trying to copy one table over another one "atomically". Basically I want to update a table periodically, such that a process that reads from the table will not get an incomplete result if another process is updating the table.

我试图将一个表复制到另一个表“原子地”。基本上我想定期更新表,这样如果另一个进程正在更新表,从表中读取的进程将不会得到不完整的结果。

To give some background info, I want a table that acts as a leaderboard for a game. This leaderboard will update every few minutes via a separate process. My thinking is as follows:

为了给出一些背景信息,我想要一个充当游戏排行榜的桌子。该排行榜将通过单独的流程每隔几分钟更新一次。我的想法如下:

Table SCORES contains the publicly-viewable leaderboard that will be read from when a user views the leaderboard. This table is updated every few minutes. The process that updates the leaderboard will create a SCORES_TEMP table that contains the new leaderboard. Once that table is created, I want to copy all of its contents over to SCORES "atomically". I think what I want to do is something like:

表SCORES包含可在公众可见的排行榜,当用户查看排行榜时将从该排行榜中读取。此表每隔几分钟更新一次。更新排行榜的过程将创建包含新排行榜的SCORES_TEMP表。创建该表后,我想将其所有内容“原子地”复制到SCORES。我想我想做的是:

TRUNCATE TABLE SCORES;
INSERT INTO SCORES SELECT * FROM SCORES_TEMP;

I want to replace everything in SCORES. I don't need to maintain my primary keys or auto increment values. I just want to bring in all the data from SCORES_TEMP. But I know that if someone views the scores before these 2 statements are done, the leaderboard will be blank. How can I do this atomically, such that it will never show blank or incomplete data? Thanks!

我想替换SCORES中的所有内容。我不需要维护主键或自动增量值。我只是想从SCORES_TEMP中获取所有数据。但我知道,如果有人在完成这两个陈述之前查看了分数,那么排行榜将是空白的。如何以原子方式执行此操作,以便它永远不会显示空白或不完整的数据?谢谢!

4 个解决方案

#1

Use rename table

使用重命名表

RENAME TABLE old_table TO backup_table, new_table TO old_table;

It's atomic, works on all storage engines, and doesn't have to rebuild the indexes.

它是原子的,适用于所有存储引擎,并且不必重建索引。

#2

In MySQL, because of the behavior of TRUNCATE I think you'll need to:

在MySQL中,由于TRUNCATE的行为,我认为你需要:

BEGIN TRANSACTION;
DELETE FROM SCORES;
INSERT INTO SCORES SELECT * FROM SCORES_TEMP;
COMMIT TRANSACTION;

I'm not sure there's a way to make what is always effectively a DDL operation transaction safe.

我不确定是否有办法使有效的DDL操作事务安全。

#3

You may use transactions (for InnoDB),

您可以使用交易(对于InnoDB),

BEGIN TRANSACTION;
DELETE FROM SCORES;
INSERT INTO SCORES SELECT * FROM SCORES_TEMP;
COMMIT;

or LOCK TABLES (for MyISAM):

或LOCK TABLES(用于MyISAM):

LOCK TABLES;
DELETE FROM SCORES;
INSERT INTO SCORES SELECT * FROM SCORES_TEMP;
UNLOCK TABLES;

#4

I don't know hot MySQL deals with transaction, but in T-SQL you could write

我不知道热门的MySQL处理事务,但在T-SQL中你可以编写

BEGIN TRAN
DELETE FROM SCORES
INSERT INTO SCORES SELECT * FROM SCORES_TEMP
COMMIT TRAN

This way your operation would be "atomic", but not instantaneous.

这样你的操作将是“原子的”,但不是瞬间的。

#1