Bug #57978 PREPARED transactions forget locks on server restart

Submitted: 4 Nov 2010 13:07
Reporter: Marko Mäkelä Email Updates:
Status: Verified Impact on me:
None 
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;

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.038 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.