May 23, 2002 at 5:45 am
Can the writer who said "....The concurrency and performance implications of this don't bear thinking about......." please explain the reasons behind that suggestion? Thank you.
quote:
If Identities were allocated as part of a transaction, then the allocation of identities for any other connections would be locked out until the end of the transaction.The concurrency and performance implications of this don't bear thinking about.
May 23, 2002 at 6:13 am
In reply to gambini's request for clarification:-
Consider the following scenario:-
User1 has a long running transaction that includes adding a row to a table with an identity.
User2 has a short transaction that adds to the same table with an identity.
If the allocation of identities was done under transaction control then User1 would be locking the 'next identity' row until the end of his/her transaction, and User2 would be blocked from obtaining the 'next identity' until User1 commits or rolls back the transaction and releases any locks held.
In a heavily used system, queues and deadlocks would quickly build.
SQL Server provides the ability to avoid 'Hotspots' at the end of a table where large amounts of inserts are done.
To put the allocation of identities under transaction control would negate all of this.
This behaviour most certainly is NOT a bug.
I hope this explains my comments.
May 23, 2002 at 2:43 pm
While it has been awhile since I saw this, we had problems with identity columns being the primary key of tables. If the server crashed, the last value of the identity column was lost. When the server came back up, new rows could not be inserted because of duplicate key violations. It seemed as if identity columns incrementing are not logged. The existence of dbcc checkident would tend to confirm this.
For this reason, we never use identity columns as the only column in a primary key.
May 23, 2002 at 4:08 pm
Yes thats always true, but in the article's sample MyDesc column had been used as a key programatically enforced. It's not suggesting that the identity is repeating a value.
I personally saw this behavior before and used this as indication of a repetition in an alternate key. But until now I'm considering it as a posible source of error when reading @@identity without a proper check. Had anybody checked the implicances of misusing @@identity and this behavior?
quote:
I think I am missing something, here. Perhaps it is the syntax of your article and my understanding of the English language. I always believed that, no matter what you do with a row on a table containing an IDENTITY field, a new row would AUTOMATICALLY have a new ID anyways, based on the seed and increment values, unless the SET IDENTITY_INSERT [table_name] ON command it run. I ran both cases you outlined in the against SQLServer 2000 Developer Edition and SQL Server 7.0 Standard Edition. Both cases on both servers returned identical results. I was expecting this to be the case. Was it supposed to be so?
May 23, 2002 at 4:09 pm
I've never had it happen so far. Then again, I dont have many server crashes. Even when I have had bad shutdowns I've never had an error. Perhaps lucky so far! Easy enough to run the checkident on all tables using sp_foreachtable in a proc that you could run on start up.
Andy
May 24, 2002 at 10:36 am
I had some bad indentity issues in SQL v6.5, but nothing in v7/2000. Of course, they havne't crashed either.
Steve Jones
May 28, 2002 at 6:54 am
Another use for DBCC CHECKIDENT... I am working on migrating data from one database to another. During development, there are frequent clearing of tables with identities. Using DBCC CHECKIDENT resets the identity to 1. Otherwise, the next transfer starts with the last identity value+1 of the last transfer.
Craig W. Bell
Craig W. Bell
May 28, 2002 at 6:59 am
Truncating the table will also reset it.
Andy
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply