Bug #57978 PREPARED transactions forget locks on server restart

• Submitted: 4 Nov 2010 13:07
• Reporter: Marko Mäkelä
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S4 (Feature request)
• Version: 5.0+
• OS: Any
• Assigned to: Assigned Account
• CPU Architecture: Any
• Tags: 2PC, innodb, locks, prepared, prepared XA

[4 Nov 2010 13:07] Marko Mäkelä

Description:
In InnoDB, only implicit record locks (the locks on records that were modified by a transaction) are durable. Table locks and record locks are lost on crash recovery. This may break the exceptations of users of two-phase commit.

How to repeat:
-- acquire implicit locks
T1: BEGIN; SELECT * FROM t FOR UPDATE; PREPARE;
-- This would block before a server restart, but not after a server restart.
-- After a restart, only the records modified by T1 would remain locked.
T2: BEGIN; SELECT * FROM t FOR UPDATE;
T1: COMMIT;

Suggested fix:
Not sure. Maybe just document this deficiency.

[20 Oct 2015 9:41] Andrei Elkin

A rather severe implication of not durable gap locks are seen in XA case.
A prepared XA trx survives the server restart. And while it does not let
a concurrent trx (INSERT below) to run before the server restart, the blocking ceases after the server restart.
That indicates improper isolation at post-restart.

--connection one
CREATE TABLE IF NOT EXISTS t (a INT) ENGINE=InnoDB;
INSERT INTO t VALUES(1);
XA START '1';
DELETE FROM t;
XA END '1';
XA PREPARE '1';

--connection two
--error 1205
INSERT INTO t VALUES(2);
SELECT count(*) as 'one' from t;

--source include/restart_mysqld.inc

INSERT INTO t VALUES(2);
SELECT count(*) as 'two' from t;