MySQL Bugs: #65146: WITH CONSISTENT SNAPSHOT does not work with isolation level SERIALIZABLE

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

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;

Thank you for the bug report. Verified with 5.5.20 on Mac OS X. Looks like this is an old problem...

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

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