Bug #11743 Mixing engines breaks atomic commits
Submitted: 5 Jul 2005 19:25 Modified: 14 Sep 2005 18:02
Reporter: Kristian Nielsen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:mysql-5.0.8-beta-nightly-20050618 OS:i386 Linux (Debian)
Assigned to: Paul DuBois CPU Architecture:Any

[5 Jul 2005 19:25] Kristian Nielsen
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.
[14 Sep 2005 18:02] Paul DuBois
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).