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

[Robin Tucker]

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.

[Simon Hayes]

"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

[Robin Tucker]

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