Handle parallel identical insertion in MYSQL table

Question

We are developing a Spring Boot application backed by a MySQL database and I have a question to which I couldn't find an answer, though there are several similar questions here on SO. The problem is to keep only one insert in the database if there are several parallel identical insert statements ran.

For example, if my Entity has the attributes (Id, parent_id and status), we don't want to allow more than one Entity with the same parent_id and status = 1 - even though they come in this form from the client, which spams our endpoint with the same request. I know that other db vendors allow for something called conditional unique key, i.e. that is a unique key like (parent_id, status = 1), but MySql does not. Is there any way to achieve this with an index, or even with Stored Procedures?

Similar questions (for reference):

1. conditional unique constraint
2. SQL can I have a "conditionally unique" constraint on a table?
3. https://dba.stackexchange.com/questions/7443/function-based-index-as-a-conditional-unique-key
4. Can I conditionally enforce a uniqueness constraint?
5. https://dba.stackexchange.com/questions/43/how-to-create-a-conditional-index-in-mysql

Answer 1

you can do it with a little trick.

include one more virtual persitant column with a IF() function. this will be store the parent_id if the status = 1 else NULL so you can create a UNIQUE KEY on this field

CREATE TABLE `cond` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `cond_status` int(11) GENERATED ALWAYS AS (if(`status` = 1,`parent_id`,NULL)) STORED,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_cond_status` (`cond_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

sample

MariaDB [Bernd]> select * from cond;
Empty set (0.10 sec)

MariaDB [Bernd]> INSERT INTO cond (parent_id,`status`) VALUES(1234, 0);
Query OK, 1 row affected (0.05 sec)

MariaDB [Bernd]> INSERT INTO cond (parent_id,`status`) VALUES(1235, 0);
Query OK, 1 row affected (0.01 sec)

MariaDB [Bernd]> INSERT INTO cond (parent_id,`status`) VALUES(1235, 2);
Query OK, 1 row affected (0.00 sec)

MariaDB [Bernd]> INSERT INTO cond (parent_id,`status`) VALUES(1235, 2);
Query OK, 1 row affected (0.02 sec)

MariaDB [Bernd]> INSERT INTO cond (parent_id,`status`) VALUES(1235, 1);
Query OK, 1 row affected (0.22 sec)

MariaDB [Bernd]> INSERT INTO cond (parent_id,`status`) VALUES(1235, 4);
Query OK, 1 row affected (0.01 sec)

MariaDB [Bernd]> INSERT INTO cond (parent_id,`status`) VALUES(1235, 1);
ERROR 1062 (23000): Duplicate entry '1235' for key 'idx_cond_status'
MariaDB [Bernd]> 

MariaDB [Bernd]> select * from cond;
+----+-----------+--------+-------------+
| id | parent_id | status | cond_status |
+----+-----------+--------+-------------+
|  1 |      1234 |      0 |        NULL |
|  2 |      1235 |      0 |        NULL |
|  3 |      1235 |      2 |        NULL |
|  4 |      1235 |      2 |        NULL |
|  5 |      1235 |      1 |        1235 |
|  6 |      1235 |      4 |        NULL |
+----+-----------+--------+-------------+
6 rows in set (0.08 sec)

MariaDB [Bernd]> 

NOTE: the create table is for MariaDB, but is nearly the same in MySQL