MySQL – Handling Concurrent Inserts with Unique Non-Primary Key

MySQLtransaction

What is the correct implementation of handling concurrent SQL inserts while ensuring a non-primary key column (i.e. wid) unique and auto-incremental?

Note: There was a design constraint that led this field (i.e. wid) in not becoming the primary key. A separate auto-increment PK is included in the column called id. Changing the wid to PK is not feasible as the database design is now heavily coupled to multiple software projects. So I need to devise a workaround.

Some more information:
The column wid follows a predefined format of concatenation of branch id and sequence number.

Sample rows:
ID - wid
1 - 11000001
2 - 11000002
3 - 10000001
4 - 11000003
5 - 10000002

I'm thinking of 3 possible ways:

1.) Reserving the wid by executing a transaction and proceeding with other SQL statements which should have been included in the same transaction. The reservation is executed in a separate transaction to make the locking as short as possible. However, if the subsequent transaction failed (e.g. due to database connection issues), then the invalidated row containing the reserved wid will remain in the table.
(note: the minimum and maximum threshold used in wid is the concatenation of branch id and sequence number)

START TRANSACTION;
SET @wid_d = 0;
SELECT COALESCE(MAX(`wid`), 0) INTO @wid_d FROM table
WHERE `wid >= 10000000 AND `wid <= 10999999 FOR UPDATE;
INSERT INTO table (`wid`) VALUES (IF (@wid_d = 0, 10000001, @wid_d+1) )
COMMIT;

2.) Including the reservation of wid in the entire transaction to eliminate the problem of insertion of invalidated row, should the transaction fail but this approach prolongs the locking of tables.

3.) Rely on the failure of transaction calls for re-executing the transaction until it performs successfully; should the transaction fail due to the race condition of inserting the same MAX(wid) + 1. This approach will prevent insertion of invalidated rows but the problem is the number of times the transaction should be re-run. There is also no distinction of knowing the problem that is caused by the conflicting wid or other problems, hence, this is a problematic approach.

Best Answer

All three options you are looking at are bound to perform poorly in a concurrent environment. Wrapping SELECT (MAX(wid)) ... in a transaction won't prevent race conditions; you'd need to write-lock the entire table for that.

If you don't need the sequence numbers to start from 1 for every branch, as long as the values are increasing and unique, you might consider creating a separate table with an auto-incerement column to generate the wid values, something like this:

create table gen (
  branch_id int not null,
  seq int not null auto_increment,
    index (seq),
    unique (branch_id, seq)
);

You can then simply insert the base branch_id into that table and retrieve the generated sequence value with last_insert_id().

Related Question