Generate a Composite ID for an entity

Question

I have an entity where I want to generate value like this.

like this

id     tenant
1        1
2        1
3        1
1        2
2        2
3        2
1        3
2        3
3        3

Keep in mind that multiple instances are running of the same application. The primary key will be formed using "id" and "tenant" and for every tenant "id" will start from 1.

I thought of using sequences, but will it be thread safe when multiple instances of the same application are trying to create the id?

score 1 · Answer 1 · answered Dec 01 '22 at 17:59

1

If you want to use sequences and start from 1 for each tenant, then you'd have to create a sequence per tenant. Though this isn't typical - usually IDs are unique across all the tenants.

DB sequences are thread safe - you can't get the same value twice even if multiple threads are involved.

answered Dec 01 '22 at 17:59

Stanislav Bashkyrtsev

14,470
7
42
45

Ideally, IDs should be unique across all tenants, but I have to make a composite primary key using ID and TENANT both like I have shown in the example. Is there any better way to achieve this without using sequence? – Nitesh kumar Rai Dec 01 '22 at 18:02
Is there something that you don't like about sequences? Another approach would be to use UUID, but then there won't be any 1,2,3 like you want. – Stanislav Bashkyrtsev Dec 01 '22 at 19:49
But MySQL does not provide any built-in function to create a sequence. – Nitesh kumar Rai Dec 02 '22 at 06:19
MySQL (as some other DBMS) provides another functionality - auto-incrementing columns. They work the same, but you don't have to explicitly _ask_ for the new ID - you just have to INSERT null there and MySQL will increment and insert the new value. – Stanislav Bashkyrtsev Dec 02 '22 at 08:44
I have found a solution that would only work for the engine MyISAM, can you give some equivalent for the InnoDB engine? https://stackoverflow.com/questions/9046971/mysql-equivalent-of-oracles-sequence-nextval CREATE TABLE animals ( grp ENUM('fish','mammal','bird') NOT NULL, id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (grp,id) ) ENGINE=MyISAM; – Nitesh kumar Rai Dec 02 '22 at 10:41
Docs say InnoDB does support auto_increment: https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html – Stanislav Bashkyrtsev Dec 02 '22 at 12:05
Yes, it does, but my requirement is to increase the "id" field from start for every new tenant value. – Nitesh kumar Rai Dec 02 '22 at 12:23
Aah.. right. Well, you can create a table for each tenant - and use that table to generate the next value and get it using this: https://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_last-insert-id. Then use the generated value to insert into the actual table. This is actually quite close to what working with sequences is like. – Stanislav Bashkyrtsev Dec 02 '22 at 13:45
But will this solution be thread safe because there are multiple instances of the same running ? Assume 3 instances perform insert for the same tenant ID at the same time and when the tries to get the last_inserted_id then all three will get the same id. – Nitesh kumar Rai Dec 02 '22 at 15:30
@NiteshkumarRai, well, each of them will need to insert first. And there's another way to get this information actually, a faster one: https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys%28%29 I wonder though what to do with the inserted records.. Either roll back that TX, or maybe commit them and at some point delete extra rows.. Doesn't seem like it's very optimal though. But it'll work. – Stanislav Bashkyrtsev Dec 02 '22 at 17:04
You should check out Hibernate's Identity Generation Strategies. They have more ways to generate IDs. I think there was one with a table and they'd keep last generated IDs in the rows. – Stanislav Bashkyrtsev Dec 02 '22 at 17:08
Can you give me the exact solution? – Nitesh kumar Rai Dec 02 '22 at 19:22

score 0 · Accepted Answer · answered Dec 05 '22 at 09:25

My current table definition where I wanted to get the IDs in the manner which is explained in the question is

CREATE TABLE `test_trigger` (
  `id` BIGINT NOT NULL,
  `tenant` varchar(255) NOT NULL,
  PRIMARY KEY (`id`,`tenant`)
);

I created one table for storing the current id for each tenant.

CREATE TABLE `get_val` (
  `tenant` varchar(255) NOT NULL,
  `next_val` int NOT NULL,
  PRIMARY KEY (`tenant`,`next_val`)
) ENGINE=InnoDB ;

Then I created this trigger which solve my problem

DELIMITER $$
CREATE TRIGGER trigger_name 
BEFORE INSERT 
ON test_trigger 
FOR EACH ROW
    BEGIN
        UPDATE get_val SET next_val = next_val + 1 WHERE tenant = new.tenant;
        set new.id = (select next_val from get_val where tenant=new.tenant);
      END$$
    DELIMITER ;

This approach will be thread safe also because any insertion for the same tenant will happen sequentially because of the update query in the trigger and for different tenants insertions will happen parallelly.

Generate a Composite ID for an entity

2 Answers2