2

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;
  • 1
    InnoDB doesn't require the columns referenced by a foreign key constraint to be the primary key -- only that they be in an index together, in the correct order, as the leftmost columns of the index in the parent table. This suggests you're going to a lot of unnecessary trouble, and you could just stick with ordinary auto-increment primary keys. – Michael - sqlbot Mar 7 '15 at 9:18
  • We can't really make the auto-incrementing column the primary key. We plan on being able to shuffle around "tenants", which means occasionally bringing in rows sequenced in one database into another database. – HardlyKnowEm Mar 9 '15 at 17:13
3

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.
| improve this answer | |
  • This is great news! I finally found the documentation confirming this. I think the confusion with the places I've looked before is that most people assume you can only auto-increment primary keys. – HardlyKnowEm Mar 9 '15 at 17:12
  • I assumed that for years, before discovering the whole truth. – Rick James Mar 9 '15 at 17:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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