Details
-
New Feature
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
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
- duplicates
-
MDEV-26097 Constraints: support deferred constraints
-
- Closed
-
- relates to
-
MDEV-15250 UPSERT during ALTER-TABLE results in 'Duplicate entry' error for alter
-
- Closed
-
-
-
- Open
-
-
-
- Open
-
-
-
- Open
-
Activity
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:
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.
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.
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.