Percona Server with XtraDB

Fake changes blocking real changes due to locking

Reported by Laurynas Biveinis on 2012-10-09
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Server
High
Laurynas Biveinis
5.1
High
Laurynas Biveinis
5.5
High
Laurynas Biveinis

Bug Description

Source:
https://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook/5.1/revision/3771 (and https://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook/5.1/revision/3775)

Since fake changes may S-lock the touched rows, any concurrent updates that need to X lock the same rows will be blocked.

A testcase (adapted from Facebook branch rev 3771):

--source include/have_innodb_plugin.inc

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1);

SET autocommit=0;
SET innodb_fake_changes=1;

--echo # Confirm that fake UPDATE doesn't hold a lock
BEGIN;
SELECT * FROM t1 FOR UPDATE;

--connect (conn2,localhost,root,,)
--connection conn2
BEGIN;
UPDATE t1 SET a=3 WHERE a=1;
ROLLBACK;

--connection default
ROLLBACK;

SET innodb_fake_changes=default;
DROP TABLE t1;

One way to fix this is to follow FB implementation in revs 3771 and 3775.

An alternative perhaps would be to fix the issue addressed in rev 3775 (fake secondary index update failing in case no locks due to concurrent real update deleting the row) by making faking sec index updates to always succeed.

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers