You may not realize it, but in MariaDB 5.2 and earlier and in MySQL up to version 5.5, START TRANSACTION WITH CONSISTENT SNAPSHOT does not give any guarantees of consistency between different storage engines.
For example, suppose you have two transactions which run in parallel:
Transaction T1:
BEGIN;
SET @t = NOW();
UPDATE xtradb_table SET a= @t WHERE id = 5;
UPDATE pbxt_table SET b= @t WHERE id = 5;
COMMIT;
Transaction T2:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
SELECT t1.a, t2.b
FROM xtradb_table t1 INNER JOIN pbxt_table t2 ON t1.id=t2.id
WHERE t1.id = 5;
In the above case, it is possible, even with a "consistent" snapshot, to see the changes in a transaction only in InnoDB/XtraDB tables, and not in PBXT tables.
Naturally, it would be much better if the changes were visible no matter the …
[Read more]