Generate a Composite ID for an entity-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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.