Description:
By mixing tables from two different transaction-capable engines
(eg. InnoDB and DBD), it is possible to break the atomicity of
commits. That is, transaction T2 in session B does two changes in a
single commit, and transaction T1 in session A sees only one of them.
This is a serious breakage of the transaction concept, but it is
difficult/impossible (I think...) to fix, and occurs only under pretty
unusual circumstances, so I think it should just be properly documented,
hence this is marked as a documentation bug.
There really is no way to get transaction consistency across
engines. In an engine based on consistent read snapshots, consistency is
ensured by making visible (only) the changes committed before this
transaction starts. In a lock-based engine, consistency is based on
changes committed when this transaction ends. There seems to be no way
to unify that.
But if someone was to get the "clever" idea to use multiple
transactional engines in a single transaction, a warning about the risk
of this kind of inconsistency would be in order.
A partial workaround is to use START TRANSACTION WITH CONSISTENT
SNAPSHOT. However, that only works with InnoDB at present, according to
the manual.
How to repeat:
To reproduce (note that both innodb and bdb engines must be enabled in
the mysql binary):
Preparation:
drop table if exists balance1;
drop table if exists balance2;
create table balance1 (id varchar(20) primary key, amount int) engine=innodb;
create table balance2 (id varchar(20) primary key, amount int) engine=bdb;
insert into balance1 values ('a', 0);
insert into balance2 values ('a', 100);
Session A:
set session transaction isolation level repeatable read;
begin;
select * from balance1 where id = 'a';
+----+--------+
| id | amount |
+----+--------+
| a | 0 |
+----+--------+
Session B:
begin; update balance2 set amount = amount - 50 where id = 'a'; update balance1 set amount = amount + 50 where id = 'a'; commit;
Session A:
select * from balance2 where id = 'a';
+----+--------+
| id | amount |
+----+--------+
| a | 50 |
+----+--------+
select * from balance1 where id = 'a';
+----+--------+
| id | amount |
+----+--------+
| a | 0 |
+----+--------+
Session A now sees a 0 in balance1 and a 50 in balance2, so 50 units
have been lost. It should have been either 0/100 or 50/50.
Suggested fix:
I think this problem should be mentioned in the manual (I could not find
it mentioned).
I suggest
1. A warning about mixing engines within transactions in chapter
14, "MySQL Storage Engines and Table Types".
2. A mention in section 13.4.1, "START TRANSACTION, COMMIT, and ROLLBACK
Syntax" that engines have separate transaction concepts, and that
consistency is not guaranteed in-between them.
3. Perhaps a mention of the problem in section 15.11.3, "InnoDB and
TRANSACTION ISOLATION LEVEL".
I can suggest wording if desired.