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?
2
Answers
My current table definition where I wanted to get the IDs in the manner which is explained in the question is
I created one table for storing the current id for each tenant.
Then I created this trigger which solve my problem
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.
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.