MySQL：在事务中截断表？
我有一个 InnoDB 表，需要每十分钟重新填充 60k 到 200k 条记录。到目前为止，我们的方法如下：
- 关闭自动提交
- 截断表
- 执行选择查询和查询附加计算（使用 PHP）
- 插入新记录
- 提交
不过，执行截断操作后，数据会立即删除，并且不再可从用户界面中获取。对于我们的用户来说，这非常令人不安，即使在大约 30 秒左右的时间内脚本遇到了提交操作并且表被重新填充。
我想也许我可以将整个操作（包括截断）包装在一个事务中，这样可能会缩短表显示为空的时间长度给用户。因此，我将 SET AUTOCOMMIT=0 更改为 START TRANSCATION。
哎呀！这与预期效果相反！现在，TRUNCATE 操作仍然发生在脚本的开头，但需要更长的时间才能在事务中实际执行 INSERT 操作，因此当COMMIT操作发生并且表中的数据再次可用时，已经过去了近十分钟！
导致这个？说实话，我根本没想到会有任何改变，因为我的印象是启动事务基本上只是关闭自动提交？
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:
- Turn off Autocommit
- Truncate the table
- Perform Select Queries & additional Calculations (using PHP)
- Insert new records
- 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.
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.
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!
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??
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
http://dev.mysql.com/doc/refman/5.1 /en/truncate-table.html
根据此 URL，从 MySQL 5.1.32 开始，
TRUNCATE TABLE是 DDL，而不是像 DELETE 那样的 DML。这意味着TRUNCATE TABLE将在事务块中间导致隐式COMMIT。因此，在需要清空的表上使用DELETE FROM而不是TRUNCATE TABLE。甚至 DELETE FROM tblname; 也可以回滚。回滚可能需要一段时间，因此请确保 InnoDB 已正确调整以处理此类回滚可能性的事务时间。
http://dev.mysql.com/doc/refman/5.1/en/truncate-table.html
According to this URL, as of MySQL 5.1.32,
TRUNCATE TABLEis DDL and NOT DML like DELETE. This means thatTRUNCATE TABLEwill cause an implicitCOMMITin the middle of a transaction block. So, useDELETE FROMon a table you need to empty instead ofTRUNCATE TABLE.Even
DELETE FROM tblname;can be rolled back. It could take a while to rollback, so make sure InnoDB is properly tuned to handle the transaction time for such rollback possibilities.完成此操作的更好方法可能是将数据插入到新表中，然后使用 在两个表上重命名以交换它们。交换只需要一次重命名，这是一个原子操作，这意味着用户甚至无法检测到它发生了，除了显示的新数据。然后您可以截断/删除旧数据。
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.
根据您的描述，我无法真正解释您的时差。唯一想到的是，您实际上并没有将插入包装到一个事务中，而是循环它。
SET AUTOCOMMIT=0 的主要区别在于，如果它已经为 0，则不会执行任何操作，而与 START TRANSACTION 一样，您将在当前事务中启动子事务。
From your description I can't really explain your time difference. The only thing that comes to mind is that you don't actually wrap the inserts into one transaction, but loop it.
The key difference with SET AUTOCOMMIT=0 is that if it's already 0, it won't do anything, where as with START TRANSACTION you will initiate a sub transaction within the current transaction.
TRUNCATE意味着COMMIT，因此该事务不是 ACID 事务。这已在上面指出。--
我在 MySQL 中使用您的方法通过电子邮件地址模拟两个表的
OUTER JOIN。结果保留在一个表中，我稍后可以快速INNER JOIN。您的方法已经包含过时的数据（因为您需要
DELETE）。因此，这是另一种方法，它也使用过时的数据，但完全放弃事务。更少的锁定 FTW。只需
INSERT...ON DUPLICATE KEY UPDATE并标记“更新时间”。在脚本末尾，DELETE任何具有旧“更新时间”的内容。TRUNCATEimpliesCOMMITso that transaction is not an ACID transaction. This is noted above.--
I use your approach in MySQL to emulate an
OUTER JOINof two tables by email address. The result stays in a table I can quicklyINNER JOINlater.Your approach is already having out-of-date data (since you require
DELETE). So here is another approach which also uses out-of-date data, but foregoes transactions entirely. Less locking FTW.Just
INSERT... ON DUPLICATE KEY UPDATEand mark an "update time". At end of your script,DELETEanything with an old "update time".