Identity Column in Concurrency, Multithreading, Parallel Processing

Our company would like to understand the effects:

1) If we have single table with Primary Key Identity column, int primary key IDENTITY(1,1),

2) and Application is conducting multi-threading parallel processing, trying to conducts 1000s of simulataneous inserts on one table.

Would the identity primary key column fail, with a primary key violation in any way, example: processors are trying to input the same identity number? Or was SQL designed to be successful even in multithreading parallel insert?


more ▼

asked Sep 09, 2017 at 06:08 AM in Default

avatar image

0 1 2

@BlueCar12 The inserts will not fail provided that identity_insert is off so the database engine is in charge of assigning the values. The calling code should use transactions with appropriate isolation levels. The identity values are not reused unless they are forced to, by the means of reseeding or using identity_insert on. Even though there might be simultaneous transactions, simultaneous in the sense that they started at the same time by multiple threads, each is still going to have to wait until the one which blocked it is either committed or rolled back.

For example, say there are 3 simultaneous transactions each inserting 100 records. The second transaction is rolled back while transactions 1 and 3 are committed. This results in identity column values 1 to 100 and then 201 to 300 (the 101 to 200 range was supposed to be used by transaction 2 which was rolled back). The next transaction (#4) will begin from the value 301, the range 101 to 200 will not be reused unless is forced to be reused.

Sep 11, 2017 at 01:56 PM Oleg
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 09, 2017 at 06:08 AM

Seen: 45 times

Last Updated: Sep 11, 2017 at 01:56 PM

Copyright 2018 Redgate Software. Privacy Policy