Bug #7850 mysqldump --single-transaction should force transaction isolation level
Submitted: 12 Jan 2005 23:19 Modified: 16 Jan 2005 22:09
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version: OS:
Assigned to: Guilhem Bichot CPU Architecture:Any

[12 Jan 2005 23:19] Guilhem Bichot
Description:
If MySQL server is running with its default isolation level (REPEATABLE READ), no bug.
If it's running with a lower level, then mysqldump's transaction will not use one but several consistent reads (one per SELECT) which makes an inconsistent backup. This is an old bug.

How to repeat:
see description

Suggested fix:
mysqldump should issue:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Note that START TRANSACTION WITH CONSISTENT SNAPSHOT, which mysqldump uses sometimes, will be fixed separately. But the SET above is still needed when mysqldump uses plain BEGIN (which it does to talk to old MySQL servers when --master-data is not used, for backward compatibility).
[12 Jan 2005 23:31] Guilhem Bichot
Remove "Note that START TRANSACTION WITH CONSISTENT SNAPSHOT, which mysqldump uses sometimes, will be fixed separately. " from my comments. START... will not be changed (see BUG#7849).
[16 Jan 2005 22:09] Guilhem Bichot
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fixed in 4.1.10 and 5.0.3 in
ChangeSet@1.2117.31.50, 2005-01-13 00:44:13+01:00, guilhem@mysql.com
  Fix for BUG#7850: force the transaction isolation level to REPEATABLE READ when --single-transaction