MySQL Bugs: #7850: mysqldump --single-transaction should force transaction isolation level

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

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

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

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