Uploaded image for project: 'Percona XtraDB Cluster'
  1. Percona XtraDB Cluster
  2. PXC-1989

LP #1698825: Deadlock on COMMIT after ALTER TABLE on separate connection

    Details

    • Type: Bug
    • Status: Done
    • Priority: Low
    • Resolution: Invalid
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None

      Description

      **Reported in Launchpad by Romuald Brunet last update 19-06-2017 15:39:42

      Server version: 5.6.20-68.0-56-log Percona XtraDB Cluster (GPL), Release 25.7, wsrep_25.7.r4126

      Using ALTER TABLE to update the default of a column will provoke a deadlock on other connections that did read that table.

      Example:

      CREATE TABLE foobar (
      id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      foo_id INT NOT NULL,
      bar_id INT DEFAULT 1);

      INSERT INTO foobar(foo_id) VALUES (1), (1), (1), (2), (2);

      /* On connection 1 */
      BEGIN;
      SELECT * FROM foobar WHERE foo_id = 1;

      /* On connection 2 */
      ALTER TABLE foobar ALTER COLUMN bar_id SET DEFAULT 2; – instantaneous

      /* On connection 1 */
      COMMIT;-- -> ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

      Tested with MySQL 5.5.54 and MariaDB 10.1.23, their behavior is that the ALTER TABLE locks until the first transaction is commited.

      Our current workaround is to use LOCK TABLE foobar WRITE in the ALTER TABLE so it will wait until the first transaction is commited

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                krunal.bauskar Krunal Bauskar
                Reporter:
                lpjirasync lpjirasync (Inactive)
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated: