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

Modify wsrep_row_upd_check_foreign_constraints() to remove the check for DELETE

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Pending Release
    • Priority: High
    • Resolution: Fixed
    • Affects Version/s: 5.7.28-31.41, 8.0.18-9.3
    • Fix Version/s: 8.0.NEXT, 5.7.NEXT
    • Component/s: None
    • Labels:

      Description

      When deleting/updating from a child table in a FK relationship, if a parent table has referenced row(s) locked, the operation on a child table will get locked and fail as soon as the parent table is unlocked. For UPDATE case, it's important to touch a column, which is a part of a constraint.

      Reproduction:

      setup
      CREATE TABLE parent_t ( id INT AUTO_INCREMENT PRIMARY KEY );
      CREATE TABLE child_t ( id INT AUTO_INCREMENT PRIMARY KEY, parent_id INT,
      CONSTRAINT `FK_child_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `parent_t` (`id`));
      INSERT INTO parent_t VALUES (), (), (), (), ();
      INSERT INTO child_t (parent_id) SELECT id FROM parent_t;
      
      Session 1
      BEGIN;
      SELECT * FROM parent_t WHERE id = 1 FOR UPDATE;
      
      Session 2
      DELETE FROM child_t WHERE parent_id = 1;
      
      Session 1
      COMMIT;--(ROLLBACK;, abort, doesn't matter)
      
      Session 2
      ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
      

      The timing of the actions doesn't matter (only the order), as don't matter the settings for lock timeouts.

      The issue does not reproduce on a regular Percona Server 5.7.29.

      There are two problems here:

      1. DELETE on a child table gets locked, which doesn't happen on a regular PS. Maybe there's a reason for that.
      2. DELETE/UPDATE locked on a child table will always get ERROR 1205. Effectively, once the operation is locked in this case, it'll never finish without an error.

      Could be introduced by this commit https://github.com/percona/percona-xtradb-cluster/commit/fd8b7c0f151b97980de3378f7f52c2bfe0236867

      Reproduces on PXC 5.7.28 and PXC 8.0.18. Doesn't reproduce on PXC 5.7.14, where the DELETE doesn't block at all.

        Smart Checklist

          Attachments

            Activity

              People

              Assignee:
              venkatesh.prasad Venkatesh Prasad
              Reporter:
              sergey.kuzmichev Sergey Kuzmichev
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - Not Specified
                  Not Specified
                  Logged:
                  Time Spent - 1 week, 2 days, 6 hours, 30 minutes
                  1w 2d 6h 30m