Does mysqldump -–single-transaction guarantee data integrity?-CodePudding

--single-transaction

This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.

My doubt is that, it says the isolation is set as REPEATABLE READ, but this may not guarantee a consistent database state.

For example, if we have a table Employee, and a table Hobby, and a table EmployeeHobby which stores employee id and hobby id.

When we use –single-transaction (i.e., REPEATABLE READ) to dump the database. Let's denote the transaction as A.

In A we first dump table Employee, then some concurrent transaction B insert a new employee into Employee, and B adds related hobby into Hobby and EmployeeHobby (this does not violate REPEATABLE_READ since A never reads Employee afterwards), and then A dump table EmployeeHobby and Hobby.

Eventually, the dumped data by A is not consistent, since EmployeeHobby contains the id of a employee that does not exist in Employee.

The dumped data is broken, isn't it?

What the doc says

it dumps the consistent state of the database at the time when START TRANSACTION was issued

seems not to be achievable by setting it to be a REPEATABLE READ transaction.

CodePudding user response：

Yes, using the --single-transaction option with mysqldump guarantees data integrity by creating a consistent snapshot of the database at the time the backup begins. This option causes mysqldump to start a new transaction and perform a consistent snapshot of the database by freezing all non-transactional tables and locking all transactional tables. This ensures that the resulting dump file contains a consistent snapshot of the database, even if the database is being written to during the dump.

Here is an example of using mysqldump with the --single-transaction option:

mysqldump --single-transaction -u [username] -p [database_name] > [dump_file.sql]

In this example, mysqldump connects to the database using the specified username and prompts for the password. It then creates a dump file containing a consistent snapshot of the database. The --single-transaction option causes mysqldump to start a new transaction and create a snapshot of the database by freezing all non-transactional tables and locking all transactional tables. This ensures that the resulting dump file contains a consistent snapshot of the database, even if the database is being written to during the dump.

CodePudding user response：

REPEATABLE READ ensures that the transaction does not read any changes committed by other sessions after the transaction's snapshot is established.

https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction.

This includes any reads against other tables. So once mysqldump's snapshot is established, it immediately applies to all InnoDB tables on that MySQL instance, even tables that it hasn't read yet.