Why is a mysqldump with single-transaction more consistent than a one without?
mysql
Solution
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`
- You start the dump without a single transaction.
- Another process inserts a row into the `Orders` table.
- Another process inserts a row into the `OrderLines` table.
- The dump processes the `OrderLines` table.
- Another process deletes the `Orders` and `OrderLines` records.
- The dump processes the `Orders` table.
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.
Problem
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? Here is what I read: 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." Can some elaborate on this?