Why is a mysqldump with single-transaction more consistent than a one without?

Question

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?

Solution 1

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. 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.

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.

Solution 2

I used to run into problems where mysqldump without the --single-transaction parameter would consistently fail due to data being changed during the dump. As far as I can figure, when you run it within a single transaction, it is preventing any changes that occur during the dump from causing a problem. Essentially, when you issue the --single-transaction, it is taking a snapshot of the database at that time and dumping it rather than dumping data that could be changing while the utility is running.

Solution 3

This can be important for backups because it means you get all the data, exactly as it is at one point in time.

So for example, imagine a simple blog database, and a typical bit of activity might be

1. Create a new user
2. Create a new post by the user
3. Delete a user which deletes the post

Now when you backup your database, the backup may backup the tables in this order

1. Posts
2. Users

What happens if someone deletes a User, which is required by the Posts, just after your backup reaches #1?

When you restore your data, you'll find that you have a Post, but the user doesn't exist in the backup.

Putting a transaction around the whole thing means that all the updates, inserts and deletes that happen on the database during the backup, aren't seen by the backup.