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.