SCOPE_IDENTITY Puzzle
-
I have some other programmer's VB.NET application that uses stored procedure in the SQL database. Strange things are going on.
Basically application takes an order from the user, inserts that order into the database, gets order_id of the last inserted record and brings order id to the web page for user to print his confirmation. It works fine in the dev enviroment but in the production when many users hit the database hard, people start to exchange their confirmation numbers like if two users submitted their orders close to each other in time there is a chance that one user will get other user's order id.
VB code calls SP that returns order id:
addNewOrder = New SqlCommand("InsertNewOrder",tr.Connection)
session("NewOrderID") = AddNewOrder.ExecuteScalar()
At this point ExecuteScalar executes SP and returns the first column of the first row in the result set. So it's all good.
SP InsertNewOrder:
Begin
Insert into order_table(..) values (...)
SELECT SCOPE_IDENTITY();
End
Here SP inserts a new record and returns the last identity value inserted into an identity column in the same SP.
I am puzzled then how scope_identity can return identity inserted by some other user when there are a lot of hits???
-
The scope_identity function usage is well explained in BOL ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/eef24670-059b-4f10-91d4-a67bc1ed12ab.htm
This behaviour is expected when PK-FK constraints are not defined correctly.
-
Actually, SCOPE_IDENTITY should work perfectly well in the way you've described it. Is it possible you're getting insert errors, deadlocks or something, because that would cause bad results from SCOPE_IDENTITY.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
"This behaviour is expected when PK-FK constraints are not defined correctly. "
There is no FK actually. There is only PK order_id and SP inserts into this table only to get order_id back. Insert error .. even when user get's wrong confirmation number, his order record is inserted correctly into the table, order is there, all correct ... with the different order_id..
And it doesn't happen very often but when DB is hit hardest. What is deadlock, what happens during deadlock?
I also noticed that Amazon, for example, doesn't let you print your order confirmation at the end of your order. I just completed an order with them and at the end they said "Thank you, your order confirmation was emailed to you". So they don't have to return that order_id from the database ..
-
Vika (11/26/2007)
"And it doesn't happen very often but when DB is hit hardest. What is deadlock, what happens during deadlock?A deadlock is when two processes are in contention for the same resource. SQL Server makes a determination which one will be the least costly to rollback and then chooses that least costly operation as a "deadlock victim" and then rolls back it's transaction.
Do you have a trigger on the table that's inserting to another table?
SCOPE_IDENTITY() is supposed to work. I've never seen it not work, except for error states.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Totally agree SCOPE_IDENTITY() works very well.
CHECK your client code or the server for "after" the fact processing ...
* Noel -
Probably it works well because correct order_id and order information is inserted into the tables. Then SP returns order_id into the code, assigns it to the session variable. At this point in 1% of the cases this session variable has somebody else's order id. And in 99% it's correct. It's hard to check the code because I cannot reproduce the error, code all looks correct. Thank you everybody who answered ..
-
I have a hard time believing that scope_identity() could have problems. It could be deadlocks but you should get an error and the variable would be null and the order would not be in the table (if it was chosen as the deadlock victim) but I have seen stranger things.
I would look at the web or logic layer. We had an application (ASP.Net 2.x) for which the programmer used a .Net session/caching methodology that didn't behave quite the way he expected. When he tested in a low volume (while coding) environment, it always returned the correct information. However in a high volume area like our production network, the load balancer (an F5 appliance) was configured to do round robin routing amongst 40 web serveres and as such would rarely be able to hit the same server and thus, he was picking up a session variable from someone else's session. Once we set the F5 to do sticky sessions for incoming IP address (all the same incoming IP addresses go to the same web server as the previous hit), his issues went away. (and he has been trained not to use this methodology anymore...)
-
you can try using begin and end tran for inserting and getting the identity
-
" the load balancer (an F5 appliance) was configured to do round robin routing amongst 40 web serveres and as such would rarely be able to hit the same server and thus, he was picking up a session variable from someone else's session. Once we set the F5 to do sticky sessions for incoming IP address .."
This sounds familiar but I don't have many servers, I have one virtual server with it's own IP. I don't know exactly how it works, it's like all servers for everybody but each thinks he has a separate server..
What is load balancer F5? How to set it up for "sticky" sessions?
-
At this point in 1% of the cases this session variable has somebody else's order id. And in 99% it's correct.
Absolutely not true... Scope_Identity will always return the correct ID... ALWAYS... even in the presence of triggers (@@IDENTITY does not suffer triggers well). What you do with it in the code may be another story... if your code is juggling shared connections, that may be a problem, as well. You must use the same session to retrieve the value of Scope_Identity as the session that did the insert.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I've never seen scope_identity fail but if you are using an Instead of Insert Trigger scope_identity is null outside of the trigger itself, which I find strange and annoying. Which forces the trigger to populate another table, usually with a process id, table id and identity retrieved from within the IoI. It's doubtful you are going that far but this is the only issue of scope_identity I know of.
Code to test scope_identity if you're interested
IF(EXISTS(SELECT * FROM sysobjects WHERE NAME = 't' AND xtype = 'u'))
DROP TABLE t
GO
CREATE TABLE t(
id INT IDENTITY,
code CHAR(10),
NAME VARCHAR(50),
CONSTRAINT pkT PRIMARY KEY (id)
)
GO
CREATE TRIGGER tr
ON t
instead OF INSERT
AS
BEGIN
DECLARE @id int
INSERT INTO t(code, NAME)
SELECT UPPER(code), NAME --We use an InitCap function here
FROM inserted
SELECT @id = SCOPE_IDENTITY()
PRINT 'Scope from IoI trigger [' + CAST(@id AS VARCHAR) + ']'
--populate [ProcessId], [TableId], [@id] in global temp table if needed
END
GO
SET NOCOUNT ON
DECLARE @id int
INSERT INTO t(code, NAME) VALUES('code','the name')
SELECT @id = SCOPE_IDENTITY()
IF @id IS NULL
PRINT 'Out of luck'
ELSE
PRINT 'Scope from actual insert [' + CAST(@id AS VARCHAR) + ']'
SET NOCOUNT OFF
GO
DROP TABLE t
GO
-
This is what I got after running it on my local server on my machine:
Scope from IoI trigger [1]
Out of luck
Does it mean scope_identity doesn't work?
-
In a trigger? It'll only work within the trigger... trigger is at a different scope... you may want to try @@IDENTITY if you're interested in the last ID that the current session inserted... keep in mind, it does NOT tell you which table the ID is for... your app must "know"...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It's only when it is an Instead of Trigger. Here is an example of an after(for) trigger where scope_identity doesn't work in the trigger but does work in the scope of the work.
IF ( EXISTS ( SELECT *
FROM sysobjects
WHERE NAME = 't'
AND xtype = 'u' ) )
DROP TABLE t
GO
CREATE TABLE t (
id INT IDENTITY,
code CHAR(10),
NAME VARCHAR(50),
CONSTRAINT pkT PRIMARY KEY ( id )
)
GO
CREATE TRIGGER tr ON t
after INSERT
AS
BEGIN
DECLARE @id int
UPDATE t
SET code = UPPER(i.code)
FROM t
INNER JOIN inserted i
ON i.id = t.id
SELECT @id = SCOPE_IDENTITY()
IF @id IS NULL
PRINT 'Can get identitity during trigger'
ELSE
PRINT 'Scope from after trigger [' + CAST(@id AS VARCHAR) + ']'
--populate [ProcessId], [TableId], [@id] in global temp table if needed
END
GO
SET NOCOUNT ON
DECLARE @id int
INSERT INTO t ( code, NAME )
VALUES ( 'code', 'the name' )
SELECT @id = SCOPE_IDENTITY()
IF @id IS NULL
PRINT 'Out of luck'
ELSE
PRINT 'Scope from actual insert [' + CAST(@id AS VARCHAR) + ']'
SET NOCOUNT OFF
GO
DROP TABLE t
GO
Accepting all invitesViewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply