0

I have created a multi-tenant application in which users creates record which has an unique number like a primary key. Every record that is added to the system increases that unique number and labels itself with that number.

My problem is, when two users try to insert a record at the same time, I get the same unique number. I want that number to be unique.

I tried to work with threads, assumed the second thread will wait for the first one to get the correct number and continue. But it didn't go as I planned.

I have also tried table triggers. When a new record inserted, I just updated the field with the latest unique ID. But still when two record comes at the same time, got same unique ID.

I expect to create unique id column, like a primary key. I understand that it could be done with in database or code.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MonkeyDLuffy
  • 556
  • 7
  • 17
  • Possible duplicate of [How To Create Table with Identity Column](https://stackoverflow.com/questions/10725705/how-to-create-table-with-identity-column) – Piotr Palka Jun 20 '19 at 23:38
  • 3
    You can do it using identity or sequence object. – Piotr Palka Jun 20 '19 at 23:39
  • 1
    `i get same unique number` Put a unique index on the column. When the insert fails, re-generate the number and try again. – mjwills Jun 20 '19 at 23:43
  • @Piotr sequence objects are good solution i didn't even know about it. like i mentioned it's a multi-tenant application, in order to do it with sequence object, i guess i have to create a sequence object for every tenant. i guess that won't help. – MonkeyDLuffy Jun 20 '19 at 23:45
  • @mjwills i really did thought that, but i can't afford time loss on the insert process. but it's a solution anyway. if i cant find anything. surely i'll try that. – MonkeyDLuffy Jun 20 '19 at 23:49
  • what you mean by multi-tenant? each tenant has its own database? – Piotr Palka Jun 20 '19 at 23:56
  • 2
    Create a uniqueidentifier column, make it primary key, not null, and then put DEFAULT NEWID(). As far as I know SQL Server can only issue each Uniqueidentifier one time. – russ Jun 21 '19 at 00:01
  • @Piotr no. each tenant using the same database. lets assume the table is called as orders. Orders table has it's PK. Lets call it OrderID. Order increases when new record added as it should be. I have also a ReferenceID which increases when a new record added by tenant. Each tenant starts from different ReferenceID's. Lets say, when user A from tenant X added a new record, gets referenceID of 100. But user b from tenant Y gets 400. The problem is when user A and B from tenant X inserts at the same time, they get same ReferenceID. Hope i can explain it well. Sorry for lack of my English. – MonkeyDLuffy Jun 21 '19 at 00:05
  • In a long run numbers from tenant A and tenant B will overlap (e.g. after tenant A get to 399 and then 400)? So your primary key is tenant id and number unique within tenant? – Piotr Palka Jun 21 '19 at 00:12
  • 2
    `but i can't afford time loss on the insert process.` You need something, somewhere, to ensure there is no duplicates. The database is the natural choice for it, and is likely no slower than any other solution. As others have suggested, an `IDENTITY` or GUID column is the usual solution for this. – mjwills Jun 21 '19 at 00:15
  • And please post the code you are using to increase a number. It may be enough to wrap it in transaction to prevent concurrency errors. – Piotr Palka Jun 21 '19 at 00:17

0 Answers0