How can auto-Incrementing be maintained when concurrent transactions occur on a compound key In MYSQL?

Question

I recently encountered an error in my application with concurrent transactions. Previously, auto-incrementing for compound key was implemented using the application itself using PHP. However, as I mentioned, the id got duplicated, and all sorts of issues happened which I painstakingly fixed manually afterward.

Now I have read about related issues and found suggestions to use trigger.

So I am planning on implementing a trigger somewhat like this.

DELIMITER $$
CREATE TRIGGER auto_increment_my_table
    BEFORE INSERT ON my_table FOR EACH ROW
        BEGIN
            SET NEW.id = SELECT MAX(id) + 1 FROM my_table WHERE type = NEW.type;
        END $$
DELIMITER ;

But my doubt regarding concurrency still remains. Like what if this trigger was executed concurrently and both got the same MAX(id) when querying?

Is this the correct way to handle my issue or is there any better way?

Answer 1

I have managed to solve this issue.

The answer was somewhat in the direction of Akina's Answer. But not quite exactly.

The way I solved it did indeed involved an additional table but not like the way He suggested.

I created an additional table to store meta data about transactions.

Eg: I had table_key like this

CREATE TABLE `journals` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `type` smallint NOT NULL DEFAULT '0',
  `trans_no` bigint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `transaction` (`type`,`trans_no`)
)

So I created a meta_journals table like this

CREATE TABLE `meta_journals` (
  `type` smallint NOT NULL,
  `next_trans_no` bigint NOT NULL,
  PRIMARY KEY (`type`),
)

and seeded it with all the different types of journals and the next sequence number.

And whenever I insert a new transaction to the journals I made sure to increment the next_trans_no of the corresponding type in the meta_transactions table. This increment operation is issued inside the same database TRANSACTION, i.e. inside the BEGIN AND COMMIT

This allowed me to use the exclusive lock acquired by the UPDATE statement on the row of meta_journals table. So when two insert statement is issued for the journal concurrently, One had to wait until the lock acquired by the other transaction is released by COMMITing.

Answer 2

An example - how to solve autoincrementing in compound index.

CREATE TABLE test ( id INT,
                    type VARCHAR(192),
                    value INT,
                    PRIMARY KEY (id, type) );

-- create additional service table which will help

CREATE TABLE test_sevice ( type VARCHAR(192),
                           id INT AUTO_INCREMENT,
                           PRIMARY KEY (type, id) ) ENGINE = MyISAM;

-- create trigger which wil generate id value for new row

CREATE TRIGGER tr_bi_test_autoincrement
BEFORE INSERT
ON test
FOR EACH ROW
BEGIN
    INSERT INTO test_sevice (type) VALUES (NEW.type);
    SET NEW.id = LAST_INSERT_ID();
END

db<>fiddle here

---

creating a service table just to auto increment a value seems less than ideal for me. – Mohamed Mufeed

This table is extremely tiny - you may delete all records except one per group with largest autoincremented value in this group anytime. – Akina

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=61f0dc36db25dd5f0cf4647d8970cdee

You may schedule excess rows removing (for example, daily) in service event procedure.