By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,842 Members | 2,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,842 IT Pros & Developers. It's quick & easy.

Concurrent Access - New Record, Primary Key problem....

P: n/a

Can someone explain what happens when two users concurrently attempt to
create a new record in a table with an autonumber primary key? For example,
user 1 creates a new record and manipulates it within a transaction making
use (perhaps) of the @@IDENTITY value when creating other, related records.
Before this transaction is complete, user 2 creates a new record and does
the same thing. Presumably they will both have the same @@IDENTITY? If
this is the case, how is it possible to manage such a situation?

Thanks.
Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

"Robin Tucker" <id*************************@reallyidont.com> wrote in
message news:cs*******************@news.demon.co.uk...

Can someone explain what happens when two users concurrently attempt to
create a new record in a table with an autonumber primary key? For
example, user 1 creates a new record and manipulates it within a
transaction making use (perhaps) of the @@IDENTITY value when creating
other, related records. Before this transaction is complete, user 2
creates a new record and does the same thing. Presumably they will both
have the same @@IDENTITY? If this is the case, how is it possible to
manage such a situation?

Thanks.


No, each session will have a different value, so there's no problem with
concurrency - check out SCOPE_IDENTITY(), IDENT_CURRENT() and @@IDENTITY in
Books Online.

Note that just defining a column as an identity column is not enough to
guarantee uniqueness - you can still create duplicates manually (see SET
IDENTITY_INSERT in BOL), so if you want to use the column as a PK, make sure
it is declared as a PK when you create the table.

Simon
Jul 23 '05 #2

P: n/a
Ok that simplifies things somewhat. Yes, the columns in question are both
Identity and Primary Key.

Thanks very much for your reply.

Robin

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:41**********@news.bluewin.ch...

"Robin Tucker" <id*************************@reallyidont.com> wrote in
message news:cs*******************@news.demon.co.uk...

Can someone explain what happens when two users concurrently attempt to
create a new record in a table with an autonumber primary key? For
example, user 1 creates a new record and manipulates it within a
transaction making use (perhaps) of the @@IDENTITY value when creating
other, related records. Before this transaction is complete, user 2
creates a new record and does the same thing. Presumably they will both
have the same @@IDENTITY? If this is the case, how is it possible to
manage such a situation?

Thanks.


No, each session will have a different value, so there's no problem with
concurrency - check out SCOPE_IDENTITY(), IDENT_CURRENT() and @@IDENTITY
in Books Online.

Note that just defining a column as an identity column is not enough to
guarantee uniqueness - you can still create duplicates manually (see SET
IDENTITY_INSERT in BOL), so if you want to use the column as a PK, make
sure it is declared as a PK when you create the table.

Simon

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.