asked
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? Note: IDENTITY_INSERT is OFF
@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.
18 People are following this question.
