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?
The --single-transaction flag will start a transaction before running. Rather than lock the entire database, this will let mysqldump read the database in the current state at the time of the transaction, making for a consistent data dump.
The exception for this is mysqldump, which does not have a parallel option and only runs in a single-threaded mode.
single transaction means a transaction other than a transaction concluded in the course of a business relationship; and where the value of the transaction is not less than the amount prescribed, except in the case of anonymous clients and clients acting under false or fictitious names.
The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server.
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
Orders
table.OrderLines
table.OrderLines
table.Orders
and OrderLines
records.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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With