2

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.

0

2 Answers 2

3

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().

0
-2

I think the process of finding a max number is a huge process when your table has millions of records you don't need that when you have table lock just get the last record. although this is not a proper approach when there are hundreds of concurrent access, the latency is getting much terrible.

I think for this one creating the new table is reasonable and use the auto-increment primary key as a sequential field. but there may be a gap between sequences if you delete one record.

1
  • MySQL, which is the subject of the question, does not have sequences.
    – mustaccio
    Aug 1, 2021 at 14:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.