0

Suppose We create a table, with identity disabled:

CREATE TABLE TestIdentity (
    Id    int NOT NULL,
    abc   varchar(10) NULL,
    CONSTRAINT PK_ident_test PRIMARY KEY CLUSTERED (id)
);

Note : I have no rights to make Primary Key to identity-enabled, so I have to calculate next Id value from the application programme.

Suppose from multiple connections data is inserted in this table, Id field value is generated by C# application code, the problem is in multi-threaded application multiple requests might generate the same id to insert row. In that case SQL server, PK constraint violates.

What is the best solution to reduce PK constraint violation?

  • Are you limited on number of requests to the table? E.g. Create ID and then check if it is unique, if not - create a new one. Any forward looking logic will need check for existing key. How uniqueness is checked/enforced now? – Stoleg Aug 15 '16 at 11:03
  • How about using a uniqueidentifier as the primary key? – Mikael Eriksson Aug 15 '16 at 11:10
  • @Stoleg, In my case logic is, First programme gets max Id of that table and increment it by 1 and use this Id to insert the column, I can achieve this by enabling Identity insert but in my case, I can't do that because the application is very old. It may possible that multiple threads generate the same Id simultaneously, and I need to avoid that situation at SQL server level e.g SQL server reserve one id for future insertion use. – Mukesh Aug 15 '16 at 11:15
  • @MikaelEriksson I can't do that because I need to implement this solution in the legacy application in which data type is int and I need it to be int. – Mukesh Aug 15 '16 at 11:17
  • 1
    What parts of this legacy application can you do something about? – Mikael Eriksson Aug 15 '16 at 11:18
0

You could try this technique will single thread the inserts into this table, but should guarantee uniqueness. Your C# code would need to do the following in a transaction. Select the current max Id and hold the lock so other users will wait. Add +1 to the Id and insert that row. Commit the transaction

BEGIN TRANSACTION
select @Id = max(Id) from TestIdentity WITH (TABLOCKX,HOLDLOCK)
set @Id += 1
insert into TestIdentity... new row...
COMMIT
| improve this answer | |
  • 1
    I guess the only problem with this is that it'll be a bottleneck, depending on what other work the application does before it insets the new row – Philᵀᴹ Aug 15 '16 at 12:07
  • Agreed - it could be problematic if you have extremely high numbers of inserts going on at the same time, but the select and subsequent insert should be relatively quick - you'd have to test, of course – Scott Hodgin Aug 15 '16 at 12:10
  • 1
    @Phil The only way to absolutely eliminate bottlenecks from two threads trying to write at the same time is to have them write to different physical locations (e.g. table per thread or partition per thread). You'll still have log bottleneck of course. – Aaron Bertrand Aug 15 '16 at 14:49
  • @ScottHodgin Your answer is useful for me and it is the thing for which I am looking , I just want you to know that, I have just tested your solution and came to know that even without HOLDLOCK this solution works. – Mukesh Aug 16 '16 at 14:37
0

Make sure each thread has a unique identifer I from 1 to N. Generate your PK in the usual way. Then multiply it by 10 and add I. This way, keys generated by thread #1 will always have a 1 as their last digit.

*This assumes there will always be <10 threads, but if N > 10 you can multiply by 100, then add I, by 1000 if N > 100, etc etc.

| improve this answer | |

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.