Bug #65146 WITH CONSISTENT SNAPSHOT does not work with isolation level SERIALIZABLE
Submitted: 29 Apr 2012 12:12 Modified: 10 Jul 2013 17:36
Reporter: Elena Stepanova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.1, 5.5, 5.6 OS:Any
Assigned to: CPU Architecture:Any

[29 Apr 2012 12:12] Elena Stepanova
Description:
The manual (http://dev.mysql.com/doc/refman/5.5/en/commit.html) says about WITH CONSISTENT SNAPSHOT: 

<quote>
The effect is the same as issuing a START TRANSACTION followed by a SELECT from any InnoDB table. <..> The WITH CONSISTENT SNAPSHOT option does not change the current transaction isolation level, so it provides a consistent snapshot only if the current isolation level is one that permits consistent read (REPEATABLE READ or SERIALIZABLE).
</quote>

However, it works as described only for REPEATABLE READ, but not for SERIALIZABLE. The test case provided in 'How to repeat' section produces the following output:

connect  con1,localhost,root,,;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
connection default;
INSERT INTO t1 VALUES(1);
connection con1;
SELECT * FROM t1;
a
1
COMMIT;

By definition, SELECT should not have returned any rows. For REPEATABLE READ the result is empty, as expected.

How to repeat:
--source include/have_innodb.inc
#--enable_connect_log

connect (con1,localhost,root,,);
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
START TRANSACTION WITH CONSISTENT SNAPSHOT;

connection default;
INSERT INTO t1 VALUES(1);

connection con1;
SELECT * FROM t1;
COMMIT;
[29 Apr 2012 13:19] Valeriy Kravchuk
Thank you for the bug report. Verified with 5.5.20 on Mac OS X. Looks like this is an old problem...
[10 Jul 2013 17:36] Bugs System
Added a changelog entry for 5.5.34, 5.6.14, 5.7.2:

"The documentation incorrectly stated that "START TRANSACTION WITH
CONSISTENT SNAPSHOT" provides a consistent snapshot only if the current
isolation level is "REPEATABLE READ" or "SERIALIZABLE". "START TRANSACTION
WITH CONSISTENT SNAPSHOT" only works with "REPEATABLE READ". All other
isolation levels are ignored. The documentation has been revised and a
warning is now generated whenever the "WITH CONSISTENT SNAPSHOT" clause is
ignored."

Revised documentation on the following pages. The updates will appear soon, in the next published documentation build:

http://dev.mysql.com/doc/refman/5.1/en/commit.html
http://dev.mysql.com/doc/refman/5.5/en/commit.html
http://dev.mysql.com/doc/refman/5.6/en/commit.html
http://dev.mysql.com/doc/refman/5.7/en/commit.html
[24 Sep 2013 14:57] Laurynas Biveinis
5.5$ bzr log -r 4412
------------------------------------------------------------
revno: 4412
committer: Annamalai Gurusami <annamalai.gurusami@oracle.com>
branch nick: mysql-5.5
timestamp: Wed 2013-07-10 10:49:17 +0530
message:
  Bug #14017206 WITH CONSISTENT SNAPSHOT DOES NOT WORK WITH ISOLATION LEVEL
  SERIALIZABLE
  
  Problem:
  
  The documentation claims that WITH CONSISTENT SNAPSHOT will work for both
  REPEATABLE READ and SERIALIZABLE isolation levels.  But it will work only
  for REPEATABLE READ isolation level.  Also, the clause WITH CONSISTENT
  SNAPSHOT is silently ignored when it is not applicable to the given isolation
  level.  
  
  Solution:
  
  Generate a warning when the clause WITH CONSISTENT SNAPSHOT is ignored.
  
  rb#2797 approved by Kevin.
  
  Note: Support team wanted to push this to 5.5+.