MySQL – Handling Concurrency with Autoincrement Triggers

innodbMySQLtrigger

I am updating an old application to implement virtual tenancy, which involves adding a tenant_id column to the primary key of every single table. The application uses auto-incrementing columns heavily, which presents a problem with InnoDB, since InnoDB doesn't allow auto-incrementing columns in multi-partite primary keys.

The standard solution to this problem seems to be "use triggers": e.g., https://stackoverflow.com/questions/18120088/defining-composite-key-with-auto-increment-in-mysql. At this point, I cannot find the reference I modeled my original solution from.

I know only enough about databases to know that I am in huge danger of shooting myself in the foot here. In particular, I am very concerned about concurrency, both in terms of making sure we don't start generating duplicate primary keys by accident (correctness), but also in terms of making sure that we don't start generating all sorts of locks that would slow down the application.

So my question has two parts:

  1. Given the scheme below, how can I be sure that the code that selects the next value in a sequence for a table and tenant_id is atomic? Is it possible for two instances of the trigger to search for the same row at the same time?

  2. How will this sequencing scheme behave during transactions? Will the sequence be rolled back if the transaction is rolled back? (We don't necessarily want that.) Will the row containing the sequence be locked for the entirety of the transaction? If so, is there some way to exclude the sequence table from the transaction?

Note that I am entirely willing to accept that this scheme is a terrible idea. We have even thought about doing the sequence generation at the application level. But before we commit to that kind of work, I wanted to figure out whether our current approach is useable.

The sequence table is defined as possible:

CREATE TABLE IF NOT EXISTS `MyApplicationSequence` (
    `table_name` VARCHAR(50) DEFAULT NULL,
    `tenant_id` SMALLINT NOT NULL,
    `sequence` INT NOT NULL,
    PRIMARY KEY (`tenant_id`, `table_name`)
);

And for each table that requires it, we create a trigger like the following:

CREATE TRIGGER `SEQ_MyApplicationTable1`
BEFORE INSERT ON `MyApplicationTable1` FOR EACH ROW
BEGIN
    UPDATE
        `MyApplicationSequence`
    SET
        `sequence` = `sequence` + 1,
        NEW.`original_primary_key` = `sequence`
    WHERE
      `table_name` = 'MyApplicationTable1' AND
       `tenant_id` = NEW.`tenant_id`
    LIMIT 1;

    -- If we there was no sequence already, start a new one at 1
    IF ROW_COUNT() = 0 THEN
        INSERT INTO `MyApplicationSequence` (`table_name`, `tenant_id`, `sequence`)
        VALUES ('MyApplicationTable1', NEW.`tenant_id`, 1);
        SET NEW.`original_primary_key` = 1;
    END IF;
END;

Best Answer

You can avoid the whole problem by using this pattern. Old tables had

id MEDIUMINT UNSIGNED AUTO_INCREMENT,
PRIMARY KEY(id)

New table has

tenant_id SMALINT UNSIGNED,
id INT UNSIGNED AUTO_INCREMENT,
PRIMARY KEY(tenant_id, id),
INDEX(id)

Notes:

  • INDEX(id) is sufficient to handle AUTO_INCREMENT.
  • The ids for a given tenant will have a lot of gaps and will grow faster than before. So you may need a larger datatype.
  • Nothing prevents you from inserting a duplicate id, but that would not happen in ordinary programming.
Related Question