Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sergei19
Starting Member
1 Post |
 Posted - 2008-07-07 : 16:49:01
|
| Here is what I want to do: I want to use an IDENTITY column in a multithreaded application. There will be threads that insert data into database and threads that read data. I understand that IDENTITY column ensures uniqueness, but does it guarantee the incrementing sequence from reader’s point of view? Basically I want the following guarantee: if I see a number X in the IDENTITY column, this means that any value less than X will never be inserted into that column.Here is an example that shows when the sequence seems to be broken:Writer thread #0 inserts the record into database and the value of IDENTITY column happened to be 2Writer thread #0 commits transaction.Reader thread #1 reads max value and finds it to be 2Writer thread #1 inserts the record into database and the value of IDENTITY column happened to be 3Writer thread #2 inserts the record into database and the value of IDENTITY column happened to be 4Writer thread #2 commits transaction.Reader thread #1 reads max value and finds it to be 4Writer thread #1 commits transaction.From reader’s perspective the value of 3 seems to be inserted after the value of 4. This is not what I want. I guess I might avoid the problem with delayed commits by using AUTO-COMMIT feature. I am not sure if it is going help. I am afraid that the database engine itself might introduce a delay between obtaining next unique value and inserting it. Do you know if MSSQL engine guarantees what I need? |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2008-07-07 : 19:26:29
|
| I don't believe that it does. You are asking that the number generated at INSERT be guaranteed to still be the lowest number at the time of COMMIT. This is essentially involves locking the entire table until the COMMIT. I'm not convinced that this is what you want.There are ways of accomplishing this but I don't see how an IDENTITY column is the means to the end.=======================================Some people walk in the rain, others just get wet. -Roger Miller, musician (1936-1992) |
 |
|
|
|
|
|
INDENTITY column in multi-threaded application