November 28, 2007 at 12:40 pm
The problem is probably that the web server is using connection pooling and scope_identity will return the last value generated in any table in the current session. (see books online).
Look at the ProcessId of the 2 web users and I'd bet they are the same.
Put this in a transaction and that should fix it.
If not, then try a transaction with IDENT_CURRENT('mytable') and this should work also.
Good luck
November 28, 2007 at 12:59 pm
oh, be careful with IDENT_Current... lot's of folks can insert rows in that very brief time span between the time you do an insert and the time you use IDENT_Current.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 28, 2007 at 1:12 pm
Jeff, I've got a couple more questions for you:
1) I belive my earlier statement about the 2 web users sharing the same connection are correct, but I believe the will have different ProcessId's (my earlier statements are incorrect).
If 2 web users are sharing a connection, how can you tell by looking in the activity monitor?
2) Each recordset opened would have it's own ProcessId, correct?
3) Since scope_Identity and @@Identity return the last values from "ANY" table, this is more dangerous than Ident_Current which is limited to a single table. Would you agree?
So doesn't, putting this in a transaction with the right isolation level solve the problem?
Thanks
November 28, 2007 at 1:30 pm
Doug Stoltz (11/28/2007)
Jeff, I've got a couple more questions for you:1) I belive my earlier statement about the 2 web users sharing the same connection are correct, but I believe the will have different ProcessId's (my earlier statements are incorrect).
If 2 web users are sharing a connection, how can you tell by looking in the activity monitor?
Dunnot the actual answer to that one... if the connection isn't dropped, it may be considered to be the same session... or not. Don't know how to monitor for that... I'm more of a data troll...
2) Each recordset opened would have it's own ProcessId, correct?
Not necessarily... if the server considers it to be the same session, multiple recordsets could be returned with the same Spid/ProcessID.
3) Since scope_Identity and @@Identity return the last values from "ANY" table, this is more dangerous than Ident_Current which is limited to a single table. Would you agree?
So doesn't, putting this in a transaction with the right isolation level solve the problem?
Scope_Identity doesn't return last value from "ANY" table... it returns the last value the current session inserted in a given table. @@Identity is not so good. If you insert into table "A" and a trigger fires that inserts into table "B", that identity for table "B" is what will be returned. That's where the "ANY comes from for @@Identity...
--Jeff Moden
Change is inevitable... Change for the better is not.
November 28, 2007 at 1:53 pm
Can you please expand the code for this back to the function with any sensitive info altered?
addNewOrder = New SqlCommand("InsertNewOrder",tr.Connection)
session("NewOrderID") = AddNewOrder.ExecuteScalar()
I want to understand your path thru
November 28, 2007 at 3:20 pm
Jeff, from books online I got this:
Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.
Thus SCOPE_IDENTITY can return values from tables T1 or T2 in the sample. (aka the "any" table). Therefore, I would agrue that you should not use SCOPE_IDENTITY, and you should use IDENT_CURRENT to make sure its the orders table.
Here's the re-write, for the proc (excuse the syntax errors):
SP InsertNewOrder:
Begin
set transaction isolation level serializable
-- Store identity in @MyKey during the tran and return from the variable
Declare @MyKey int
-- The real problem was a lack of transactions, start one here
Begin TRAN
Insert into order_table(..) values (...)
-- now grab the key before committing (locking out other users)
SELECT @MyKey = IDENT_CURRENT('order_table')
commit Tran
-- @MyKey should never be a duplicate so return the key from a variable
Select @MyKey
End
I think this would work without code changes in the Web app, but i have not tested this.
Personally, I'd return the new Key value in an output parameter, but this will require a code change.
What do you think? Please post the final fix for this... Thanks
November 28, 2007 at 3:26 pm
vica, here's another approach. Look it over and figure out what you'd need in the where clause to make it work.
SP InsertNewOrder:
Begin
Insert into order_table(..) values (...)
SELECT Top 1 YourIdentityColumnName
from order_table where x=x
order by YourIdentityColumnName desc
End
November 28, 2007 at 3:44 pm
OK. I still try to pinpoint the problem, is it vb code, application server settings, SQL server settings, scope_identity problem. I read a lot of info regarding session loss etc, still don't know what to blame.
Since many threads were submitted here, I will explain the problem again.
On the page confirmOrder.aspx user press the button "Confirm". Order is inserted into the database, scope_identinty returns new order id which is placed into a just created session variable and user is redirected to a new page orderConfirmed.aspx where he can print order confirmation with that session variable. It all works fine but in some rare cases when traffic is very high, user can get somebody's else session with order id, not his. At the same time everything is OK with that user's order in the database and if user makes a search by his last name, he finds his order with the correct order id. So it's a mistery how user can get somebody's else orderID. Maybe I need a .NET programmer help to explain me what's going on. This is not my application either but I need to fix it.
Now a little bit about the hardware. I use ASP.NET 2.0, this is a single application server, not a web farm or web garden and settings in IIS maximum number of working processes = 1 are correct. It doesn't happen to ALL users at the same time so it's not the recycle worker process problem. Domain name doesn't have "_" in it's name. Session string in web.config is
sessionState mode="InProc" stateConnectionString="tcpip=127.0.0.1:42424" sqlConnectionString="data source=127.0.0.1;Trusted_Connection=yes" cookieless="AutoDetect" timeout="30"/
Now about the code:
confirmOrder.aspx.vb:
Private Sub InsertOrder()
Try
Dim sOrder As New Order
Dim objHeader As New Order.OrderHeader
Dim OrderID As Long
objDS = New DataSet()
objBasket = New DataTable
objHeader = Session("UserInfo")
objDS = Session("OrderBasket")
objBasket = objDS.Tables("Basket")
objBasketOptions = objDS.Tables("Options")
Session("OrderID") = sOrder.Add(objHeader, objBasket, objBasketOptions, Session("UserID"))
Response.Redirect("OrderConfirmed.aspx")
Catch ex As Exception
Session("Complete") = False
End Try
End Sub
OrderConfirmed.aspx.vb
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
OrderIDLabel.Text = Session("OrderID")
End Sub
Function Add where we insert order into the database and return orderID:
Public Function Add(ByVal oh As OrderHeader, ByVal objBasket As System.Data.DataTable, ByVal objBasketOptions As System.Data.DataTable, ByVal UserID As String) As Long
Dim conn As SqlConnection = Nothing
Dim _sql As SqlCommand
Dim AddOrderHeader As SqlCommand
Dim AddOrderDetails As SqlCommand
Dim NewOrderID As Long
Dim tr As SqlTransaction = Nothing
Try
conn = New SqlConnection(...)
conn.Open()
tr = conn.BeginTransaction(IsolationLevel.ReadCommitted)
AddOrderHeader = New SqlCommand("InsertOrderHeader", tr.Connection)
AddOrderHeader.CommandType = CommandType.StoredProcedure
AddOrderHeader.Parameters.AddWithValue("@OrderDTTM", Date.Now)
................
AddOrderHeader.Transaction = tr
NewOrderID = AddOrderHeader.ExecuteScalar()
AddOrderDetails = New SqlCommand(InsertOrderDetail, tr.Connection)
AddOrderDetails.CommandType = CommandType.StoredProcedure
AddOrderDetails.Parameters.AddWithValue("@TimeStamp", Date.Now)
....................
AddOrderDetails.Transaction = tr
AddOrderDetails.ExecuteScalar()
tr.Commit()
Return NewOrderID
Catch ex As Exception
tr.Rollback()
Throw New ApplicationException(ex.Message)
Finally
conn.Close()
End Try
End Function
Then stored procedures:
PROCEDURE [dbo].[InsertOrderHeader]
(@OrderDTTM datetime,
................
@OrderID numeric(18,0) output)
BEGIN
INSERT INTO ORDER_HEADER (
"ORDER_DTTM" ..) VALUES (
@OrderDTTM ..)
SET @OrderID = scope_identity();
END
November 28, 2007 at 4:08 pm
Doug,
here's your SP;
SP InsertNewOrder:
Begin
set transaction isolation level serializable
-- Store identity in @MyKey during the tran and return from the variable
Declare @MyKey int
-- The real problem was a lack of transactions, start one here
Begin TRAN
Insert into order_table(..) values (...)
-- now grab the key before committing (locking out other users)
SELECT @MyKey = IDENT_CURRENT('order_table')
commit Tran
-- @MyKey should never be a duplicate so return the key from a variable
Select @MyKey
End
It looks almost like my SP and yes, my SP has orderID as an output parameter so no VB code changes needed.. Transaction is in VB code, not in SP. Should transaction be placed in SP too? How would it be re-written then?
PROCEDURE [dbo].[InsertOrderHeader]
(@OrderDTTM datetime,
@OrderID numeric(18,0) output)
BEGIN
INSERT INTO ORDER_HEADER (
"ORDER_DTTM" ) VALUES (
@OrderDTTM )
SET @OrderID = scope_identity();
END
November 28, 2007 at 4:21 pm
I would find it hard to believe the IIS was getting session variables screwed up, but anything is possible. Look at the datetimes in your order headers table when the problems occur. how close are the transactions?
Also, I would look at the isolation level where you begin the transaction and change to serializable. Here's your code:
tr = conn.BeginTransaction(IsolationLevel.ReadCommitted)
ReadCommitted is defined as "Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data."
On the performance side: response.redirect causes a postback to the browser resulting in 2 requests back to the web server from the client, while server.transfer advances to the next ASPX page directly. - check it out in books online
You're also opening up connections explicitly in your code, I'd open up profiler and see if you are really using connection pooling or not. Opening connection will slow you down too.
November 28, 2007 at 5:02 pm
"On the performance side: response.redirect causes a postback to the browser resulting in 2 requests back to the web server from the client, while server.transfer advances to the next ASPX page directly. - check it out in books online
"
http://weblogs.asp.net/bleroy/archive/2004/08/03/207486.aspx
"Why not use Server.Transfer instead of Response.Redirect especially if both pages are aspx? How can I avoid ThreadAbortException for Server.Transfer?"
"Excellent question.
Server.Transfer will not get the right URL in the address line of the user's browser. If that's acceptable, you can use Transfer, and you save one client/server roundtrip.
Now, Transfer also has a Response.End (and hence a ThreadAbortException), but the code inside Transfer is really just two lines:
Execute(path, null, preserveForm);
Response.End();
So you see that to avoid the exception for Transfer, you should just use Execute instead.
Of course, if you do that, you still need to suppress the output from the first page because both pages will output to the same response (you don't need to do that with Redirect because you have two separate requests in this case, and thus two different responses, the first one being thrown away). This can be done by clearing the response before calling Execute or doing some funny stuff with the writer parameter of Execute. Many strange scenarios here ;)"
November 28, 2007 at 8:24 pm
vicka, I'd change the proc to what is shown below. Transactions, the Serializable isolation and IDENT_CURRENT should rule out this proc as the problem. Try this out before you make code changes in the ASPX files. The links were cool, thanks, let me know if this works.
more info here: http://msdn2.microsoft.com/en-us/library/aa259216(SQL.80).aspx
PROCEDURE [dbo].[InsertOrderHeader]
(@OrderDTTM datetime,
@OrderID numeric(18,0) output)
Begin
Set transaction isolation level serializable
BEGIN TRAN
INSERT INTO ORDER_HEADER (
"ORDER_DTTM" ) VALUES (
@OrderDTTM )
SET @OrderID = IDENT_CURRENT('ORDER_HEADER');
COMMIT TRAN
END
November 29, 2007 at 2:42 pm
Hi Doug,
this was created without errors (for 2005)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE TEST
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT getdate();
COMMIT TRANSACTION;
END
November 30, 2007 at 9:01 am
Thanks, I still hoping to hear that you solved this problem. I think Transaction and Iden_Current changes will rule out SQL server as the problem.
If it turns out that this relates to Sessions in ASP.NET let me know and I'll give you my home email and help you out on this. This is probaly the wrong forum to debug IIS problems.
You may want to log the ProcessId or SessionId in the order header so you can find a pattern in the data. I would also add the SessionID as a hidden field in the web page.
That way when you find the error, you should be albe to View Source in IE and see the SessionID.
Good Luck and post back when you've made more progress
December 3, 2007 at 7:33 am
Doug,
seems like changes in the stored procedure did help. Thank you very much, you are very smart!
-V
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply