MySQL: Is the read committed isolation level safe with mixed binary log format?

Question

When using the MIXED format with the READ COMMITTED isolation level, am I safe?

The docs state (http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_read-committed)

If you use READ COMMITTED or enable innodb_locks_unsafe_for_binlog, you must use row-based binary logging

In the list of When running in MIXED logging format, the server automatically switches from statement-based to row-based logging under the following conditions

.. the isolation level is not mentioned: http://dev.mysql.com/doc/refman/5.5/en/binary-log-mixed.html

Furthermore, the following bug from 2006/2007 talks about adding an warning or error message if the isolation level is used with the incompatible log format. See link to bug in the comment section

I have tested the combination in MySQL 5.5 and I don't get any warning. So I assume that the row format is used if the isolation is used. Am I right?

Update

The following suggests that it is safe (that ROW will be used when having MIXED). Link to manual in the comments (I can't post more than 2 links)

If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMMITTED, only row-based logging can be used. It is possible to change the logging format to STATEMENT, but doing so at runtime leads very rapidly to errors because InnoDB can no longer perform inserts.

• Bell: Link to bug: bugs.mysql.com/bug.php?id=23051
• Rick James: Why do you need "Read committed"?
• Bell: I need it in order to prevent gap locking when using select .. for update. I select specific ids. Link to manual, quote from the update: dev.mysql.com/doc/refman/5.5/en/binary-log-setting.html

Answer 1

According to the MySQL Documentation : Mixed Binary Logging Format

A given storage engine can support either or both logging formats. The following table lists the formats supported by each engine.

In the table mentioned under that sentence on that page

+--------------+---------------------+----------------------------------+
|Storage Engine|Row Logging Supported|Statement Logging Supported |
+--------------+---------------------+----------------------------------+
|ARCHIVE |Yes |Yes |
+--------------+---------------------+----------------------------------+
|BLACKHOLE |Yes |Yes |
+--------------+---------------------+----------------------------------+
|CSV |Yes |Yes |
+--------------+---------------------+----------------------------------+
|EXAMPLE |Yes |No |
+--------------+---------------------+----------------------------------+
|FEDERATED |Yes |Yes |
+--------------+---------------------+----------------------------------+
|HEAP |Yes |Yes |
+--------------+---------------------+----------------------------------+
|InnoDB |Yes |Yes when the transaction |
| | |isolation level is REPEATABLE | <== NOTE THIS
| | |READ or SERIALIZABLE; No otherwise|
+--------------+---------------------+----------------------------------+
|MyISAM |Yes |Yes |
+--------------+---------------------+----------------------------------+
|MERGE |Yes |Yes |
+--------------+---------------------+----------------------------------+
|NDB |Yes |No |
+--------------+---------------------+----------------------------------+

Thus, binlog_format=MIXED is not supported for READ COMMITTED.