Is @@IDENTITY really unique in multi-sessions scope

[Eugene]

I have a table EugeneTest(id_n um, fname, minit, lname)
where field "id_num" is type IDENTITY and has UNIQUE constraint

Let's say 2 user execute this code at the same time:

DECLARE @return integer
use EugeneTest
INSERT employees
( fname, minit, lname)
VALUES
( 'Eugene3', 'F', 'Josephs')
SET @return = @@IDENTITY

Is is not possible to get duplicated value in id_num column becuase of
UNIQUE
constraint, but is it possible the both user get the same @@IDENTITY
number
( for example, if @@IDENTITY is evaluated and kept somewhere in the
buffer before the data actually get written to the disk )

Thanks, Eugene

[Phil]

Eugene wrote:

I have a table EugeneTest(id_n um, fname, minit, lname)
where field "id_num" is type IDENTITY and has UNIQUE constraint

Let's say 2 user execute this code at the same time:

DECLARE @return integer
use EugeneTest
INSERT employees
( fname, minit, lname)
VALUES
( 'Eugene3', 'F', 'Josephs')
SET @return = @@IDENTITY

Is is not possible to get duplicated value in id_num column becuase of UNIQUE
constraint, but is it possible the both user get the same @@IDENTITY
number
( for example, if @@IDENTITY is evaluated and kept somewhere in the
buffer before the data actually get written to the disk )

Thanks, Eugene

The problem with @@IDENTITY is that, whilst it holds the last identity
value inserted into any table, it is modified by ALL user sessions
(i.e. it has global scope). Thus, if 2 concurrent users execute the
code in your example above, it is possible for User1 to actually
return the value generated by User2 if user2 is running a slpit-second
behind User1.

To get around this problem, you should use SCOPE_IDENTITY( ) instead.
This also returns the most recent indetity value inserted into any
table, BUT ONLY FOR THAT SESSION (i.e. local scope, hence the name).
This means that you will not pick up an identity value generated by
another user session.

[Steve Jorgensen]

On 4 Feb 2005 09:23:17 -0800, "Phil" <ph********@bto penworld.com> wrote:

Eugene wrote:

I have a table EugeneTest(id_n um, fname, minit, lname)
where field "id_num" is type IDENTITY and has UNIQUE constraint

Let's say 2 user execute this code at the same time:

DECLARE @return integer
use EugeneTest
INSERT employees
( fname, minit, lname)
VALUES
( 'Eugene3', 'F', 'Josephs')
SET @return = @@IDENTITY

Is is not possible to get duplicated value in id_num column becuaseof

UNIQUE
constraint, but is it possible the both user get the same @@IDENTITY
number
( for example, if @@IDENTITY is evaluated and kept somewhere in the
buffer before the data actually get written to the disk )

Thanks, Eugene

The problem with @@IDENTITY is that, whilst it holds the last identity
value inserted into any table, it is modified by ALL user sessions
(i.e. it has global scope). Thus, if 2 concurrent users execute the
code in your example above, it is possible for User1 to actually
return the value generated by User2 if user2 is running a slpit-second
behind User1.

Right on 1 - wrong on 2. @@IDENTITY is only affected by statements executed
in the same connection, even though @@ normally means Global.

From the T-SQL Help: "@@IDENTITY and SCOPE_IDENTITY will return the last
identity value generated in any table in the current session. However,
SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is
not limited to a specific scope."

To get around this problem, you should use SCOPE_IDENTITY( ) instead.
This also returns the most recent indetity value inserted into any
table, BUT ONLY FOR THAT SESSION (i.e. local scope, hence the name).
This means that you will not pick up an identity value generated by
another user session.

@@IDENTITY won't pick up a value generated by another user session, but it can
pick up a value generated by a trigger action on another table that was
triggered by the action you explicitly executed.

[Steve Jorgensen]

On 4 Feb 2005 09:23:17 -0800, "Phil" <ph********@bto penworld.com> wrote:

Eugene wrote:

I have a table EugeneTest(id_n um, fname, minit, lname)
where field "id_num" is type IDENTITY and has UNIQUE constraint

Let's say 2 user execute this code at the same time:

DECLARE @return integer
use EugeneTest
INSERT employees
( fname, minit, lname)
VALUES
( 'Eugene3', 'F', 'Josephs')
SET @return = @@IDENTITY

Is is not possible to get duplicated value in id_num column becuase

of

UNIQUE
constraint, but is it possible the both user get the same @@IDENTITY
number
( for example, if @@IDENTITY is evaluated and kept somewhere in the
buffer before the data actually get written to the disk )

Thanks, Eugene

As I understand it, the next value for an identity column is incremented
immediately, and not rolled back in order to prevent just that problem. Thus,
IDENTITY values are not guaranteed to be consecutive, they are guaranteed
never to produce duplicates, even if 2 users have insert transactions
overlapping in time.

[Phil]

Looks like I was being sloppy and using session and connection
interchangeably . I must confess I thought I'd finally got the hang of
identity value behaviour - clearly not! :-)

Still, 99% of the time all I need to know is that code at our place
needs to use scope_identity, and not @@identity or ident_current (which
is another topic altogether!)

[Steve Jorgensen]

On 4 Feb 2005 10:03:56 -0800, "Phil" <ph********@bto penworld.com> wrote:

Looks like I was being sloppy and using session and connection
interchangeabl y. I must confess I thought I'd finally got the hang of
identity value behaviour - clearly not! :-)

Still, 99% of the time all I need to know is that code at our place
needs to use scope_identity, and not @@identity or ident_current (which
I agree with that completely :)
is another topic altogether!)