dangerous IDENTITY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

dangerous IDENTITY

Author

Topic

p2bl
Yak Posting Veteran

54 Posts

Posted - 2003-06-05 : 23:18:58

look at this SP:

create proc up_do
AS
begin tran
declare @tid int
insert into someTable (....) values(.....)
set @tid=@@identity
insert into otherTable (tid,....)values(@tid.....)
commit tran

this procedure is called by many user at the same time,sometimes the "@@IDENTITY" got the wrong number(bigger).I have changed "set @tid=@@identity" to "set @tid=IDENT_CURRENT(sometable)",problerm still.Transaction can not(or will not)lock the table?What if I use "SCOPE_IDENTITY()"??
DO I have to add "synchronize" keyword in my Java Code?But what can I do if I use asp?

Any idea will be appreciated!

========================
look!

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-06-06 : 00:34:47

p2bl,

Do you know about the SQL Server help file "Books On Line" (BOL)
It could have saved you this post......

DavidM

"SQL-3 is an abomination.."

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2003-06-06 : 00:35:38

Is there a trigger on this table that inserts into another table? Is it possible your transaction is being rolled back?

As you suggested, SCOPE_IDENTITY() would be worth checking out since it returns the identity value for the row you inserted, not from an underlying trigger.

I googled this and came up with a couple of interesting articles:
http://sqlteam.com/item.asp?ItemID=319
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01d4.asp
http://www.embarcadero.com/news/identitycrisis.asp

-ec

Edited by - eyechart on 06/06/2003 00:40:01

p2bl
Yak Posting Veteran

54 Posts

Posted - 2003-06-06 : 02:00:41

There's no trigger with a "insert" on the table.What confused me is that transaction seems not to lock the table .For example,here is what someTable now contains
a(Identity column with primary key) b
1 xxxx
2 xxxx

then,in my SP

create proc up_do
AS
begin tran
declare @tid int
insert into someTable (....) values(.....)
set @tid=@@identity
insert into otherTable (tid,....)values(@tid.....)
commit tran

the value of @tid should be 3,but it get 4!
This only occurs 3 times in about 30000 execution,but even one single error may result in severe logical confusion.

I have googled this too,but I got is about "insert triggers".

byrmol,could find answers in BOL?

========================
look!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources