identity column (SQL)- two process can try to grab same number? how is it resolve-d?

what is the technology microsoft uses to avoid two requests trying to grab the same identity column. (perhaps to the nano second, 2 users clicking at same time and a request has been released for new identity value)
how does MS tech/architecture provided to resolve a conflict like that? does MS reveal this outside in tech papers etc?
LVL 5
25112Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

UnifiedISCommented:
One transaction has to occur before the other.  At some level, there is a point of entry that only allows one at a time. If 2 transactions are trying to occur at the same I don't know what sort method chooses which one would go first.
0
Kent OlsenData Warehouse Architect / DBACommented:
Hi 25112,

Internally, an identity column looks exactly like any other column.  It has a name, a position in the table, and a data type.

While we think of the identity attribute as part of the column, it is actually maintained as part of the table.  So when the DBMS (SQL Server, DB2, or any other mutlitasking DBMS) stores a row into a table with an identity column, it increments the value in the table that is the identity.  The locking mechanism to ensure no duplication can be a mutex (in-memory lock), but it can also be a simple "increment memory location and return value" instruction.  

Good Luck,
Kent
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brian CroweDatabase AdministratorCommented:
It is one of the fundamental rules of an ACID database as SQL is for the most part.  Below are some discussions of how it works in the background but I think after 15 years of implementation it is fairly robust and reliable at this point if you are having trust issues :-)

http://dba.stackexchange.com/questions/1635/why-are-denali-sequences-supposed-to-perform-better-than-identity-columns

http://www.sqlmag.com/article/sql-server/Sequences-Part-2-129205
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
As I understand it, MS generates an internal pool of upcoming numbers to be used for any table with an identity.  Exactly how many isn't important, I think it's 1000 (but, any number will do as an example).  Thus, if the last used identity value was 5,287, then the pool would contain numbers from 5,288 thru 6,287.  As identity values are needed, they are pulled from the pool and assigned to rows and then deleted from the pool, thus preventing the same number from ever being used more than once.  Some standard locking mechanism is used to control access to the pool.  When the pool is (about to be) depleted, more numbers are generated.

That is also why numbers can be "lost": certain things can clear the pool, such as shutting down SQL, but SQL can't go back and try to reuse numbers from an earlier pool.  It simply generates a new pool of numbers when it needs to.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
The use of an unique constraint will guarantee the uniqueness of the value.
0
ZberteocCommented:
What do you mean by "two requests trying to grab the same identity column". Do you want to read an identity column or to insert into a table with an identity column? What are you trying to do? Can you be more specific?
0
25112Author Commented:
thanks all..

why i asked this question was to possibly infer how the technology is made fail-proof..

in one of the older applications, we have the column PK incremented by 1 programatically.. and from a stored procedure..
we had a rare situation where two users clicked for a new record to the nano second, and there was a duplicate and there was a crash, as there was no provision to handle it..

hence wondering if the technology behind the identity is available, that we can program it (adding one to the previous value, with proper troubleshooting, so it does not crash).
0
Kent OlsenData Warehouse Architect / DBACommented:
If you let SQL Server (or any major DBMS) handle it, there will not be a duplicated value in the identity column.  The only time there is a risk of duplication is when the application tries to do the work of the DBMS.

Kent
0
Scott PletcherSenior DBACommented:
Quite right.  An identity value can never return a repeated/duplicate value until/unless you reseed the identity value yourself.  Note, though, that TRUNCATEing the table will reset the identity value to its original starting value.
0
ZberteocCommented:
The only scenario where you can try to generate the identity value in an application is if you want to use it cross SQL servers but that is not a trivial task.
0
UnifiedISCommented:
In your stored procedure, you should have the lookup and the insert in one transaction so that won't happen

BEGIN
SELECT @NewID = (SELECT MAX(ID)  + 1 FROM Table)
INSERT INTO Table (ID)
SELECT @NewID
END
0
UnifiedISCommented:
Of course, making it an identity column is the preferred method if possible.
0
25112Author Commented:
>>BEGIN
 SELECT @NewID = (SELECT MAX(ID)  + 1 FROM Table)
 INSERT INTO Table (ID)
 SELECT @NewID
 END

technically, this is how we got the error.. two stored procedures called this at the same nano second and there was a duplicate issue..
0
25112Author Commented:
>>If you let SQL Server (or any major DBMS) handle it, there will not be a duplicated value in the identity column.

yes, but do we know what more do they do/have other than the code sample suggested by UnifiedIS:
>>BEGIN
  SELECT @NewID = (SELECT MAX(ID)  + 1 FROM Table)
  INSERT INTO Table (ID)
  SELECT @NewID
  END
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If ID is an Identity Column you shouldn't explicit insert the value. It will throw an error if you try to do it.
0
Kent OlsenData Warehouse Architect / DBACommented:
For that INSERT statement to work you must be running with IDENTITY_INSERT enabled.  (If the column really is declared as an IDENTITY, that is.)  For normal production use you would normally not do that just to prevent what you're seeing now.

  SELECT @NewID = (SELECT MAX(ID)  + 1 FROM Table)
  INSERT INTO Table (ID)
  SELECT @NewID

That INSERT statement has a couple of issues.  It needlessly queries the table to find the maximum value for ID and it allows the application to control the value of the IDENTITY column.  The INSERT should be simply:

INSERT INTO Table (col1) SELECT @ColValue;

Noting that col1 is NOT the identity column.


Kent
0
Scott PletcherSenior DBACommented:
Clearly you can't allow any opening for another task to use the same number.  Therefore, do something like this:

INSERT INTO table_name ( ID, ... )
SELECT (SELECT MAX(ID) + 1 FROM table_name WITH (TABLOCKX)), ...

That will keep the table locked while the row is being inserted.  Naturally you'll need that insert to finish as quickly as possible, and that code works only for a single row.  To insert multiple rows at once, you'd need a subquery with ROW_NUMBER() to add to the MAX(ID).
0
UnifiedISCommented:
I wasn't proposing that querying the table for the max ID was a good practice but it sounded like that was what was happening.  
It should be BEGIN TRANSACTION...COMMITT TRANSACTION instead of BEGIN...END in order to keep the lookup and insert together without allowing the other request to cause an issue.

Again, switch it to an identity column if possible.
0
Olaf DoschkeSoftware DeveloperCommented:
SELECT @NewID = (SELECT MAX(ID)  + 1 FROM Table)
 INSERT INTO Table (ID)
 SELECT @NewID
 END

Open in new window


Such a routine is not failsafe, as two users can start it at almost the same time and even if determining MAX(ID) can be optimized by index usge storing MAX(ID)+1 is after determining it, so two users can determine the same next ID, even if it's stored into the table right away.

The technical solution of the RDBMS is to put a lock on it, a read lock, an exclusive lock. Even other SELECTS would need to wait for the unlock of the table and then will read the next max value. SQL Server maintains a pool of next ids instead, as has been explained, it doesn't work anyway close to determining MAX of all records. This is quite simple for the RDBMS. It' not part of the T-SQL language to put exclusive or read locks on data. The only hints you can put on queries are NOLOCK hints, quite the opposite of what you'd need. I can't think of anything you can do as database user, even transactions don't block out any further reads, so a SELECT MAX(ID) can always return the same max value twice.

Bye, Olaf.
0
Scott PletcherSenior DBACommented:
It's not part of the T-SQL language to put exclusive or read locks on data. The only hints you can put on queries are NOLOCK hints, quite the opposite of what you'd need.

Hmm, that's just not true, at all.  Indeed, I posted code above showing to hold an exclusive lock on the table for the duration of your transaction:

INSERT INTO table_name ( ID, ... )
SELECT (SELECT MAX(ID) + 1 FROM table_name WITH (TABLOCKX)), ...
0
25112Author Commented:
i appreciate all the discussion.. we will keep these in mind (lock preferences) when having to do it with the application.

as a standard for new development, IDENTITY is the method used.

in conclusion, would it too simple yet true to say that the technology MS makes sure to make IDENTITY FAILPROOF is by locking the table, (too)?
0
Kent OlsenData Warehouse Architect / DBACommented:
MS doesn't lock the table to increment the IDENTITY value.
0
ZberteocCommented:
You don't have to worry about MS identity feature or for any other database platform for that matter. It is safe to use for decades now. :)
0
25112Author Commented:
yes, no worries..! will keep deploying IDENTITY tables..

before I close, is there a MSDB article or any KB that covers this below point, (to read more on this pool)
"As I understand it, MS generates an internal pool of upcoming numbers to be used for any table with an identity.  Exactly how many isn't important, I think it's 1000"
0
Olaf DoschkeSoftware DeveloperCommented:
Sorry, kdo, I was just trying to explain what a general RDBMS could do. What actually is done with the pool if next identity values illustrates how the MS SQL Server solution differs from that.

And sorry, Scott, I wasn't paying attention to TABLOCKX, I have to admit that's new to me. From what I read it only works within a transaction, I assume also the automatic transaction each statement has anyway, so it would work. Is there any genral isolation level it wouldn't work?

In the end the IDENTITY columns are working reliable. The cons about IDENTITY columns are surely not about creating duplicates.

Bye, Olaf.
0
Scott PletcherSenior DBACommented:
@Olaf:

Yes, only within a trans, and, yes, every UPDATE in SQL will be part of a trans, either an explicit one you create or one that SQL implicitly creates for you.

It will work for any isolation level.  If SQL can't obtain that degree of lock, the statement would eventually become deadlocked, and one transaction or the other would be killed.
1
Olaf DoschkeSoftware DeveloperCommented:
Thanks, I'll have to look into table hints in more detail, though they better not become the main tool.

Bye, Olaf.
0
25112Author Commented:
thanks all.

could not distribute the points for some of the posts, since min 50 points needed for each helped answer.

thank you again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.