473,322 Members | 1,417 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Is @@IDENTITY really unique in multi-sessions scope

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
5 3983
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Benny | last post by:
Dear All, Suppose in the program a record is added to a table whose primary key is a identity field. If I really want to get the lastest value for that field after the insertion, is it the best...
112
by: Andy | last post by:
Hi All! We are doing new development for SQL Server 2000 and also moving from SQL 7.0 to SQL Server 2000. What are cons and pros for using IDENTITY property as PK in SQL SERVER 2000? Please,...
4
by: brent.ryan | last post by:
How do I get the next int value for a column before I do an insert in MY SQL Server 2000? I'm currently using Oracle sequence and doing something like: select seq.nextval from dual; Then I...
13
by: Jerry Camel | last post by:
I need to be able to generate unique names for files. I was considering that hash alogorithms, but if I had two files with the same name, I'd get the same hash. I am collecting and storing files...
41
by: pb648174 | last post by:
In a multi-user environment, I would like to get a list of Ids generated, similar to: declare @LastId int select @LastId = Max(Id) From TableMania INSERT INTO TableMania (ColumnA, ColumnB)...
3
by: Dave A | last post by:
Has anyone written or using an identity mapper design pattern in an enterprise framework? The identity mapper pattern is described in Martin Fowler's Patterns of Enterprise Application...
1
by: Brad Eck | last post by:
In Access, newID returns a unique for the table. In SQL Server, newid() returns a GUID - unique in the world. I do not need or desire that complexity. Is there a way to get a simple unique int on...
4
by: Dabbler | last post by:
I'm using an SQLCommand to insert row using a text command. Is there a way to return the IDENTITY key value after the insert ? Thanks much!
1
by: cedric.louyot | last post by:
Hi, I've written a schema that looks like : <xs:schema> <xs:complexType name="myType"> <xs:sequence> <xs:element name="e1" type="T1" maxOccurs="unbounded"/> <xs:element name="e2"...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.