MySQL:在事务内截断表? [英] MySQL: Truncate Table within Transaction?
问题描述
我有一个InnoDB表，需要每10分钟在60k到200k记录中的任何地方重新填充一次.到目前为止，我们的方法如下:
I have an InnoDB table that needs to be re-populated every ten minutes within anywhere from 60k to 200k records. Our approach up to this point has been as follows:
- 关闭自动提交
- 截断表格
- 执行选择查询和其他计算(使用PHP)
- 插入新记录
- 提交
但是，在执行截断"操作后，数据将立即被删除，并且不再可从用户界面中获得.对于我们的用户来说，即使在大约30秒左右的时间内脚本遇到了Commit操作并重新填充了表，这还是令人非常不安的.
After the Truncate operation is performed though, the data is immediately deleted, and is no longer available from the User Interface. To our users, this has been pretty disconcerting, even though within about 30 seconds or so the script encounters the Commit operation and the table is repopulated.
我以为我可以将整个操作(包括 > Truncate)包装在一个事务中，这可能会减少用户对该表显示为空的时间长度.所以我将SET AUTOCOMMIT=0更改为START TRANSCATION.
I thought that perhaps I could wrap the whole operation, including the Truncate, in a transaction, and that this might cut down on the length of time during which the table appears empty to users. So I changed SET AUTOCOMMIT=0 to START TRANSCATION.
赞！与预期效果相反！现在，TRUNCATE操作仍然发生在脚本的开头，但是在事务内实际执行INSERT操作需要更长的时间，因此，到COMMIT操作执行时位置，表格中的数据再次可用，已经快了 十分钟！
Yikes! This had the opposite of the desired effect! Now the TRUNCATE operation still occurs at the beginning of the script, but it takes much longer to actually execute the INSERT operations within the transaction, so that by the time the COMMIT operation takes place and the data in the table is available again, it has been nearly ten minutes!
这可能导致什么?说实话，我根本没想到会有任何变化，因为我的印象是，发起交易基本上只会关闭Autocommit?
What could possibly cause this? Truthfully, I wasn't expecting any change at all, because I was under the impression that initiating a transaction basically just turns off Autocommit anyway??
推荐答案
A better way to accomplish this might be to insert the data into a new table, and then use rename on both tables in order to swap them. A single rename is all that's needed for the swap, and this is an atomic action, which means the users won't even be able to detect that it happened, except for the new data showing up. You can then truncate/delete the old data.
这篇关于MySQL:在事务内截断表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
