Dump with SERIALIZABLE more consistent than `--single-transaction` (REPEATABLE READ) in MySQL

Question

I could dump all databases to backup.sql with --single-transaction as shown below:

mysqldump -u john -p -A --single-transaction > backup.sql

Actually according to the doc below, --single-transaction uses REPEATABLE READ which is less consistent than SERIALIZABLE because REPEATABLE READ cannot prevent lost update and write skew while SERIALIZABLE can:

This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data.

So now, are there any solutions to dump all databases to backup.sql with SERIALIZABLE?

mysqldump -u john -p -A ????? > backup.sql

Answer 1

Mysqldump does not have an option to use SERIALIZABLE transaction isolation level. It is hard-coded to use REPEATABLE READ whenever you use the --single-transaction option:

https://github.com/mysql/mysql-server/blob/trunk/client/mysqldump.cc#L5402-L5404

msql_query_with_error_report(mysql_con, nullptr,
                                    "SET SESSION TRANSACTION ISOLATION "
                                    "LEVEL REPEATABLE READ")

(No other code appears in mysqldump.cc to set the isolation level.)

But you don't need to use SERIALIZABLE, and doing so would not be a good idea.

The anomalies you mention occur when two sessions have issues with timing their respective updates — i.e. both sessions are executing updates.

Mysqldump only reads data, it does not update data. Therefore you don't need to worry about either lost updates or write skew.

The reason it wouldn't be a good idea is that SERIALIZABLE in MySQL is implemented simply as if you had run all queries as locking reads (e.g. SELECT ... FOR SHARE). Mysqldump would then be querying every table serially, acquiring locks on each one as it got to it. Under these conditions, it would be possible that other concurrent sessions doing their own updates would be blocked by the locks held by mysqldump, and they would wait. If they already held locks on any table that mysqldump has yet to read, then mysqldump would be blocked by their locks. This would cause a deadlock.

The way to prevent a deadlock is to use pessimistic locking, in other words acquire lock on all resources as one atomic action, before any of the reading begins.

Mysqldump does have an option for this: --lock-all-tables. This will of course block all other sessions from running queries while the mysqldump is running, but it prevents deadlocks.