MySQL Concurrency – Prevent Unique Constraint Violations from Concurrent Inserts

concurrencyinsertMySQL

I have a situation where I need to insert a unique record into the database:

SELECT user FROM users WHERE id = 'some_randomly_generated_id'
If user does not exist, INSERT INTO users WHERE id = 'some_randomly_generated_id'

The problem is, a matching record can be added after the SELECT query but before the INSERT query, causing a unique constraint error. How would I prevent/go about this? These are my current thoughts after some research:

Run these queries under a transaction and use SELECT user FROM users WHERE ... FOR SHARE to lock the row so no new record could be inserted (I might be wrong on this, please correct me).
Handle the unique constraint error in the application and pretend that the record is inserted successfully. (No transaction, could be better since it allows greater concurrency and is trivial to implement)
Run only the INSERT IGNORE query instead, which silently ignores the unique constraint error. (Similar to the 2nd approach in my opinion, just that the error is not handled in the application).

Thanks in advance. My dialect is MySQL and isolation level is REPEATABLE READS if that helps make answering this question easier.

Best Answer

Failure to wrap in a transaction is causing your problem.

do
    compute random number
    BEGIN;
    SELECT ... FOR UPDATE;
    if row exists, then start loop over
    INSERT ...;
    COMMIT;
until success

And don't worry about transaction isolation mode.

Or...

do
    compute random number
    INSERT ...;
until success

with autocommit = ON; no worry about isolation mode.

Or... Use an AUTO_INCREMENT column -- MySQL will deal with guaranteeing a unique id every time. (Caveat: A little more code is needed on multi-master topologies.)

Related Solutions

MySQL – How to Get Next Unique Value Without Auto Increment

What is interesting about this situation can be solved using the MyISAM storage.

I answedred a question like this back in April 2012 : How can you have two auto-incremental columns in one table? You need to create one table whose sole purpose is the create sequences of work order for each site

CREATE TABLE site_workorder_seq
(
    SiteID int not null,
    SiteWorkorderNum int not null auto_increment,
    PRIMARY KEY (SiteID,SiteWorkorderNum)
) ENGINE=MyISAM;

Here is a sample loading into this table:

mysql>     DROP DATABASE david;
Query OK, 1 row affected (0.01 sec)

mysql>     CREATE DATABASE david;
Query OK, 1 row affected (0.00 sec)

mysql>     USE david
Database changed
mysql>     CREATE TABLE site_workorder_seq
    ->     (
    ->         SiteID int not null,
    ->         SiteWorkorderNum int not null auto_increment,
    ->         PRIMARY KEY (SiteID,SiteWorkorderNum)
    ->     ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql>     INSERT INTO site_workorder_seq (SiteID) VALUES
    ->     (1),(1),(2),(3),(3),(3),(3),(4),(4),(4),
    ->     (5),(5),(4),(2),(2),(2);
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql>     SELECT * FROM site_workorder_seq;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                1 |
|      1 |                2 |
|      2 |                1 |
|      2 |                2 |
|      2 |                3 |
|      2 |                4 |
|      3 |                1 |
|      3 |                2 |
|      3 |                3 |
|      3 |                4 |
|      4 |                1 |
|      4 |                2 |
|      4 |                3 |
|      4 |                4 |
|      5 |                1 |
|      5 |                2 |
+--------+------------------+
16 rows in set (0.00 sec)

mysql>

Let's look at the the last WorkorderNum from each site

mysql> SELECT SiteID,MAX(SiteWorkorderNum) SiteWorkorderNum
    -> FROM site_workorder_seq GROUP BY SiteID;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                2 |
|      2 |                4 |
|      3 |                4 |
|      4 |                4 |
|      5 |                2 |
+--------+------------------+
5 rows in set (0.05 sec)

mysql>

Now, suppose you want to get the next SiteWorkorderNum for SiteID 3. You could do this:

INSERT INTO site_workorder_seq (SiteID) VALUES (3);
SELECT MAX(SiteWorkorderNum) INTO @nextworkordernum
FROM site_workorder_seq WHERE SiteID=3;
SELECT @nextworkordernum;

Let's run this and see what happens

mysql> INSERT INTO site_workorder_seq (SiteID) VALUES (3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT MAX(SiteWorkorderNum) INTO @nextworkordernum
    -> FROM site_workorder_seq WHERE SiteID=3;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @nextworkordernum;
+-------------------+
| @nextworkordernum |
+-------------------+
|                 5 |
+-------------------+
1 row in set (0.03 sec)

mysql> SELECT * FROM site_workorder_seq;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                1 |
|      1 |                2 |
|      2 |                1 |
|      2 |                2 |
|      2 |                3 |
|      2 |                4 |
|      3 |                1 |
|      3 |                2 |
|      3 |                3 |
|      3 |                4 |
|      3 |                5 |
|      4 |                1 |
|      4 |                2 |
|      4 |                3 |
|      4 |                4 |
|      5 |                1 |
|      5 |                2 |
+--------+------------------+
17 rows in set (0.00 sec)

mysql> SELECT SiteID,MAX(SiteWorkorderNum) SiteWorkorderNum
    -> FROM site_workorder_seq GROUP BY SiteID;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                2 |
|      2 |                4 |
|      3 |                5 |
|      4 |                4 |
|      5 |                2 |
+--------+------------------+
5 rows in set (0.00 sec)

mysql>

As long as you use this one MyISAM apart from all InnoDB tables, you can generate workordernums per site to your hearts content.

SQL Server Concurrency – Handle Concurrency When Insertion Depends on Reading

The problem is that there could be values present at the end of A's transaction - the ones inserted by B - that were not present when A first checked. This is known as a phantom

A phantom is a row that matches the search criteria but is not initially seen.

The way to prevent phantoms is to use the Serializable isolation level. This is the only isolation level which prevents phantoms.

There are other ways to serialize a workload without using that isolation level. One is to obtain an exclusive lock on the whole table using TABLOCKX. When used within an explicit transaction (BEGIN TRANSACTION) this will take an exclusive lock on the table and hold it until the transaction is committed or rolled back. Therefore B will not be able to insert until A has committed. The table lock affects all work, however, including simple queries which would not induce interval overlaps.

A finer-grained approach would be to use sp_getapplock. This allows your code to generate its own lock with its own semantics. It acts as a MUTEX. When workload A starts it acquires this applock. It can then go on to preform the check and insert confident it alone has rights to do so. If workload B were to start concurrently it would attempt to acquire the applock and be blocked because that applock is owned by A. You have to be careful that all work that could be subject to the overlapping interval problem must start by acquiring this custom applock and finish by releasing it. Writes that do not suffer from the interval overlap problem and all reads can proceed without acquiring the applock or being blocked by it.

Best Answer

Related Solutions

MySQL – How to Get Next Unique Value Without Auto Increment

SQL Server Concurrency – Handle Concurrency When Insertion Depends on Reading

Related Question