MySQL Bugs: #62104: mysqldump --single_transaction option and READ COMMITTED isolation mode

Bug #62104	mysqldump --single_transaction option and READ COMMITTED isolation mode
Submitted:	6 Aug 2011 7:10	Modified:	11 Mar 2013 17:19
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	any	OS:	Any
Assigned to:	John Russell	CPU Architecture:	Any

Description:
In my understanding there is no guarantee that --single_tranasaction will ensure a consistent snapshot if GLOBAL transaction isolation mode is READ_COMMITTED.  Also documentation confirm this I think.

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

" This option issues a BEGIN SQL statement before dumping data from the server"

" This option 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 BEGIN was issued without blocking any applications. 

 When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state. 

 While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail. 
..
 This option is not supported for MySQL Cluster tables; the results cannot be guaranteed to be consistent due to the fact that the NDBCLUSTER storage engine supports only the READ_COMMITTED transaction isolation level. You should always use NDB backup and restore instead."

How to repeat:
See above.

Suggested fix:
1) First a small detail.  Even though they are functionally identically the two passages contradict each others a little.  One says that "BEGIN" and the other say "START TRANSACTION" is executed

2) More important: Since introduction of server SESSION variables in 5.0 the situation can (with InnoDB and in my understanding) be improved by issuing a "SET SESSION tx_isolation = 'repeatable-read';" before starting the transaction. So I think that either mysqldump should have an additional parameter doing so or (simpler and preferred) mysqldump should always do if --single-transaction is specified.

(but not sure if this restriction will be lifted though - I don't think so:  
"While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.")

typo fix!

So, is this a documentation request to clearly describe current behavior of mysqldump, or a feature request for mysqldump to always use REPEATABLE READ isolation level, or request for mysqldump to provide option to specify isolation level? Please, clarify.

It is a request to either 
1) always use REPEATABLE READ for the SESSION if --single_transaction is specified
2) or alternatively provide one more option that lets the user do so.

It should have been added in 5.0 I think as it became possible with SESSION variables. There is an avoidable risk that an inconsistent shapshot is created if server is running READ_COMMITED globally.

Thank you for the report.

mysqldump actually adds REPEATABLE READ at the start of session when --single-transaction is specified. You can ensure that if run SET GLOBAL log_output='table', general_log=1;, then run mysqldump and examine its content:

mysql> select * from mysql.general_log\G
...
*************************** 5. row ***************************
  event_time: 2011-08-03 19:47:11
   user_host: root[root] @ localhost []
   thread_id: 17196
   server_id: 51
command_type: Query
    argument: SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
*************************** 6. row ***************************
  event_time: 2011-08-03 19:47:11
   user_host: root[root] @ localhost []
   thread_id: 17196
   server_id: 51
command_type: Query
    argument: START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */

Could you please reopen and change category to 'documentation'?

Documentation *does not* tell this.  It only tells that it will execute START TRANSACTION or BEGIN (and that small inconsistence should be fixed in docs as well).

In addition to what docs tells now something like this should be added "Also if --single_transaction is specified mysqldump will set transaction isolation mode to REPEATEBLE_READ"

forgot a detail:

".. will set transaction isolation mode to REPEATEBLE_READ for the SESSION"

Thank you for the feedback.

Agreed and done.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated text:

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.