Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-26096

Constraints: support deferred constraints [WAS: Make the Unique index ONLY evaluate immediately before the commit (NOT after each UPDATE)]

Details

    Description

      Make the Unique index ONLY evaluate immediately before the commit and NOT after each UPDATE ("Deferred Constraints Check" )

      MariaDB [test]> CREATE TABLE `node` (
          -> `id` int(11) NOT NULL AUTO_INCREMENT,
          -> `parentId` int(11) NULL DEFAULT NULL,
          -> `orderIndex` int(5) NOT NULL,
          -> `label` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
          -> PRIMARY KEY (`id`) USING BTREE,
          -> INDEX `FKparent`(`parentId`) USING BTREE,
          -> CONSTRAINT `FKparent` FOREIGN KEY (`parentId`) REFERENCES `node` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
          -> ) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
      Query OK, 0 rows affected (0.038 sec)
       
      MariaDB [test]> INSERT INTO `node` VALUES (1, NULL, 0, 'root1');
      Query OK, 1 row affected (0.014 sec)
       
      MariaDB [test]> INSERT INTO `node` VALUES (2, 1, 0, 'child1a');
      Query OK, 1 row affected (0.008 sec)
       
      MariaDB [test]> INSERT INTO `node` VALUES (3, 1, 1, 'child1b');
      Query OK, 1 row affected (0.013 sec)
       
      MariaDB [test]> INSERT INTO `node` VALUES (4, 1, 2, 'child1c');
      Query OK, 1 row affected (0.004 sec)
       
      MariaDB [test]> INSERT INTO `node` VALUES (5, NULL, 0, 'root2');
      Query OK, 1 row affected (0.006 sec)
       
      MariaDB [test]> INSERT INTO `node` VALUES (6, 2, 0, 'child2a');
      Query OK, 1 row affected (0.003 sec)
       
      MariaDB [test]> INSERT INTO `node` VALUES (7, 2, 1, 'child2b');
      Query OK, 1 row affected (0.005 sec)
       
      MariaDB [test]> INSERT INTO `node` VALUES (8, 2, 2, 'child2c');
      Query OK, 1 row affected (0.004 sec)
       
      MariaDB [test]> 
      MariaDB [test]> START TRANSACTION;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> update node set orderindex=2 where id = 3;
      Query OK, 1 row affected (0.000 sec)
      Rows matched: 1  Changed: 1  Warnings: 0
       
      MariaDB [test]> update node set orderindex=1 where id = 4;
      Query OK, 1 row affected (0.001 sec)
      Rows matched: 1  Changed: 1  Warnings: 0
       
      MariaDB [test]> COMMIT;
      Query OK, 0 rows affected (0.004 sec)
       
      MariaDB [test]> ALTER TABLE `node`
          -> ADD UNIQUE INDEX `UXorderIndex`(`parentId`, `orderIndex`);
      Query OK, 0 rows affected (0.025 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> START TRANSACTION;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> update node set orderindex=1 where id = 3;
      ERROR 1062 (23000): Duplicate entry '1-1' for key 'UXorderIndex'
      MariaDB [test]> update node set orderindex=2 where id = 4;
      ERROR 1062 (23000): Duplicate entry '1-2' for key 'UXorderIndex'
      MariaDB [test]> COMMIT;
      Query OK, 0 rows affected (0.004 sec)
      

      upstream feature request: https://bugs.mysql.com/bug.php?id=68360

      Also this feature should be implemented as a optional and not by default so if anyone wants to use the old way, they can.

      Attachments

        Issue Links

          Activity

            hholzgra Hartmut Holzgraefe added a comment - - edited

            Looking at other SQL products, e.g. PostgreSQL allows to declare a CONSTRAINT as either IMMEDIATE or DEFERRED, with DEFERRED meaning "check at COMMIT time":

            https://www.postgresql.org/docs/13/sql-set-constraints.htm

            IMMEDIATE checks still being the default behavior so there, DEFERRED checks need to be declared explicitly.

            hholzgra Hartmut Holzgraefe added a comment - - edited Looking at other SQL products, e.g. PostgreSQL allows to declare a CONSTRAINT as either IMMEDIATE or DEFERRED, with DEFERRED meaning "check at COMMIT time": https://www.postgresql.org/docs/13/sql-set-constraints.htm IMMEDIATE checks still being the default behavior so there, DEFERRED checks need to be declared explicitly.

            IMMEDIATE/DEFERRED constraint checks seem to be defined in

            ISO/IEC 9075-2:2008 subclauses 10.8 and 17.4 if I read this correctly:

            https://docs.microsoft.com/en-us/openspecs/sql_standards/ms-tsqliso02/70d6050a-28c7-4fae-a205-200ccb363522

            hholzgra Hartmut Holzgraefe added a comment - IMMEDIATE/DEFERRED constraint checks seem to be defined in ISO/IEC 9075-2:2008 subclauses 10.8 and 17.4 if I read this correctly: https://docs.microsoft.com/en-us/openspecs/sql_standards/ms-tsqliso02/70d6050a-28c7-4fae-a205-200ccb363522

            Duplicated by MDEV-26097

            julien.fritsch Julien Fritsch added a comment - Duplicated by MDEV-26097

            I do not think that this exactly duplicates MDEV-26097. It is related to or part of that task, but not duplicating it.

            marko Marko Mäkelä added a comment - I do not think that this exactly duplicates MDEV-26097 . It is related to or part of that task, but not duplicating it.

            My idea for fixing MDEV-15250 (which is about deferred unique checks during online ALTER TABLE) is to traverse the transaction’s undo log at the time a concurrent DML transaction is being committed.

            I think that deferred unique key checks could be implemented inside InnoDB by simply traversing the undo log at XA PREPARE or COMMIT time. This would require some storage engine API changes, because there currently is no such call. Such API change might also be needed for fixing MDEV-24608.

            marko Marko Mäkelä added a comment - My idea for fixing MDEV-15250 (which is about deferred unique checks during online ALTER TABLE ) is to traverse the transaction’s undo log at the time a concurrent DML transaction is being committed. I think that deferred unique key checks could be implemented inside InnoDB by simply traversing the undo log at XA PREPARE or COMMIT time. This would require some storage engine API changes, because there currently is no such call. Such API change might also be needed for fixing MDEV-24608 .

            There are at least 3 types of constraints:

            • UNIQUE INDEX
            • FOREIGN KEY (like SolidDB had them 30 years ago; doable in InnoDB, but would we want to make this depend on MDEV-22361 to avoid duplicated effort?)
            • deferred CHECK constraints

            As far as I can tell, all these cases would require traversal of the transaction log (binlog or storage engine logs like in MDEV-15250) right before XA PREPARE or COMMIT. A storage engine API would have to be designed for that.

            Implementing a deferred UNIQUE INDEX check would have to be done in every storage engine. There should probably also be some change to the data dictionary to indicate that deferred checks are preferred. But, a global parameter might work as well. When it comes to InnoDB, I think that implementing deferred UNIQUE INDEX or FOREIGN KEY constraint checks should be doable, even without any file format change other than the possible change of the data dictionary. But, the API would have to be designed and implemented first.

            marko Marko Mäkelä added a comment - There are at least 3 types of constraints: UNIQUE INDEX FOREIGN KEY (like SolidDB had them 30 years ago; doable in InnoDB, but would we want to make this depend on MDEV-22361 to avoid duplicated effort?) deferred CHECK constraints As far as I can tell, all these cases would require traversal of the transaction log (binlog or storage engine logs like in MDEV-15250 ) right before XA PREPARE or COMMIT . A storage engine API would have to be designed for that. Implementing a deferred UNIQUE INDEX check would have to be done in every storage engine. There should probably also be some change to the data dictionary to indicate that deferred checks are preferred. But, a global parameter might work as well. When it comes to InnoDB, I think that implementing deferred UNIQUE INDEX or FOREIGN KEY constraint checks should be doable, even without any file format change other than the possible change of the data dictionary. But, the API would have to be designed and implemented first.

            People

              serg Sergei Golubchik
              niljoshi Nilnandan Joshi
              Votes:
              1 Vote for this issue
              Watchers:
              10 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.