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

Is @@IDENTITY really unique in multi-sessions scope

P: n/a
I have a table EugeneTest(id_num, 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
Jul 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Eugene wrote:
I have a table EugeneTest(id_num, 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.

Jul 23 '05 #2

P: n/a
On 4 Feb 2005 09:23:17 -0800, "Phil" <ph********@btopenworld.com> wrote:
Eugene wrote:
I have a table EugeneTest(id_num, 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.
Jul 23 '05 #3

P: n/a
On 4 Feb 2005 09:23:17 -0800, "Phil" <ph********@btopenworld.com> wrote:
Eugene wrote:
I have a table EugeneTest(id_num, 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.
Jul 23 '05 #4

P: n/a
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!)

Jul 23 '05 #5

P: n/a
On 4 Feb 2005 10:03:56 -0800, "Phil" <ph********@btopenworld.com> wrote:
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
I agree with that completely :)
is another topic altogether!)

Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.