Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-592

LP #1064326: Fake changes blocking real changes due to locking

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: High
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      **Reported in Launchpad by Laurynas Biveinis last update 16-11-2012 05:53:31

      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;

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            lpjirasync lpjirasync (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Smart Checklist