为什么具有单事务的mysqldump比没有事务的mysqldump更一致? [英] Why is a mysqldump with single-transaction more consistent than a one without?

问题描述

我已经阅读了手册，并提到每个事务在开始进行转储之前都会添加一个BEGIN语句.有人可以用更容易理解的方式阐述这一点吗?

I have gone through the manual and it was mentioned that every transaction will add a BEGIN statement before it starts taking the dump. Can someone elaborate this in a more understandable manner?

这是我读到的:

此选项在从服务器转储数据之前发出BEGIN SQL语句.它仅对事务表(如InnoDB和BDB)有用，因为那样 在发出BEGIN时转储数据库的一致状态，而不会阻止任何应用程序."

This option issues a BEGIN SQL statement before dumping data from the server. It is useful only with transactional tables such as InnoDB and BDB, because then it dumps the consistent state of the database at the time when BEGIN was issued without blocking any applications."

可以详细说明一下吗?

推荐答案

由于转储在一个事务中，因此您可以获得数据库中所有表的一致视图.最好用一个反例来解释.假设您转储具有两个表Orders和OrderLines

Since the dump is in one transaction, you get a consistent view of all the tables in the database. This is probably best explained by a counterexample. Say you dump a database with two tables, Orders and OrderLines

1. 您无需进行单个交易即可开始转储.
2. 另一个进程在Orders表中插入一行.
3. 另一个进程在OrderLines表中插入一行.
4. 转储处理OrderLines表.
5. 另一个进程将删除Orders和OrderLines记录.
6. 转储处理Orders表.

1. You start the dump without a single transaction.
2. Another process inserts a row into the Orders table.
3. Another process inserts a row into the OrderLines table.
4. The dump processes the OrderLines table.
5. Another process deletes the Orders and OrderLines records.
6. The dump processes the Orders table.

在此示例中，您的转储将具有OrderLines的行，但没有Orders的行.如果Orders和OrderLines之间有外键，则数据将处于不一致状态，并且还原时将失败.

In this example, your dump would have the rows for OrderLines, but not Orders. The data would be in an inconsistent state and would fail on restore if there were a foreign key between Orders and OrderLines.

如果您是在单个事务中完成的，则转储将既没有顺序也没有行(但是将是一致的)，因为在事务开始之后，两者都被插入然后被删除了.

If you had done it in a single transaction, the dump would have neither the order or the lines (but it would be consistent) since both were inserted then deleted after the transaction began.

这篇关于为什么具有单事务的mysqldump比没有事务的mysqldump更一致?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！