Consistent read with savepoints.

gistfile1.txt

# Setup: t1, t2, t3 (a INT PRIMARY KEY, b INT) with rows (1,1),(2,2)

# con1
mysql> SELECT @@version;
+-----------------------------------+
| @@version                         |
+-----------------------------------+
| 5.5.32-dev-valgrind-max-debug-log |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> SET sql_log_bin = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

mysql> START TRANSACTION WITH CONSISTENT SNAPSHOT;
Query OK, 0 rows affected (0.00 sec)

mysql> SAVEPOINT sp;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t1;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |    2 |
+---+------+
2 rows in set (0.00 sec)

# con2
OPTIMIZE TABLE t1;

# con3
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t2 VALUES (3,3);
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM t3 WHERE a = 2;
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

# con1
mysql> ROLLBACK TO sp;
Query OK, 0 rows affected (0.00 sec)

mysql> SAVEPOINT sp;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t2;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |    2 |
+---+------+
2 rows in set (0.00 sec)

mysql> ROLLBACK TO sp;
Query OK, 0 rows affected (0.00 sec)

mysql> SAVEPOINT sp;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t3;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |    2 |
+---+------+
2 rows in set (0.00 sec)

mysql> ROLLBACK TO sp;
Query OK, 0 rows affected (0.00 sec)