READ COMMITTED isolation level doesn't work (at least with InnoDB)

[Stewart Smith]

Relatively simple test to see if READ COMMITTED actually does, well, READ COMMITTED fails:

(result file generated on mysql 5.1.47)

--- r/trx_isolation_read_committed.result 2010-05-31 09:07:47.483026351 +0300
+++ r/trx_isolation_read_committed.reject 2010-05-31 09:08:38.632945301 +0300
@@ -6,23 +6,16 @@
 BEGIN;
 SELECT * from t1;
 a
-1
 INSERT INTO t1 values (2);
 SELECT * from t1;
 a
-1
-2
 COMMIT;
 BEGIN;
 SELECT * FROM t1;
 a
-1
-2
 COMMIT;
 SELECT * from t1 order by a;
 a
-1
-2
 COMMIT;
 BEGIN;
 SELECT * from t1 order by a;

[Stewart Smith (stewart)]

It turns out that this isn't tested by the mysql test suite either, but it does work there.

InnoDB starts a statement (i.e. gets a consistent snapshot for the statement) for statements in READ COMMITTED isolation level transactions by setting sql_stat_start to TRUE in Cursor::external_lock().

This is mirrored in Embedded InnoDB Cursor by calling the Embedded InnoDB ib_cursor_stmt_begin() (which encapsulates the exact same thing).... except that it takes a cursor, so must be done as part of the Cursor, not the Storage Engine (so we can't call it in startStatement) - the same limitation as the innobase handler has.

then... later on, during execution, InnoDB finally actually assigns the snapshot.

anyway.... READ COMMITTED is broken and it shouldn't be.