December 3, 2007 at 7:47 am
No problem. I'm Glad that fixed it. I've got similar systems and it would not be good if my identity values or Session variables get screwed up.
I believe you want all of your transactions to be serializable. Starting the transaction in your ASP.Net code was the correct way to do it when you are adding 2 table entries. On a single table I usually just add the transaction in my Proc's.
Best of luck with your application
December 3, 2007 at 11:22 am
Besides load-testing the proc (set up 1000 connections on your computer and run them all at once, 100 times each), which would reveal any hidden problem in the proc, I would most expect this to be a problem with the data connection you are using.
As others have mentioned, the possibility of cross-contamination of session data needs to be investigated.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 4, 2007 at 9:26 pm
Ok... not knowing how SQL Server handles concurrent users this is a big guess.
SP InsertNewOrder:
Begin
Insert into order_table(..) values (...)
SELECT SCOPE_IDENTITY();
End
Is it possible that between the time one user completes their insert and gets the result of the SELECT SCOPE_IDENTITY , another user of the SP is able to compete an insert? In that case the first user would get the SCOPE_IDENTITY of the second user's INSERT statement.
I have seen this kind of thing happen in other multi-thread systems with heavy loads but am just throwing this out here for those of you who are more aware of the inner workings of SQL Server.
(also if you need more granularity for locking you could try using the WITH(SERIALIZABLE) hint on the INSERT instead of the SET TRANSACTION ISOLATION LEVEL.)
December 5, 2007 at 7:48 am
Tony, check my previous posts on this thread. Setting the isolation level on the transaction (not on the Insert Statement) did the trick.
It seems that the session variable corruption was a false alarm.
What must be happening is this:
1) ProcessId=A, runs the insert, and SQL holds locks on the table
2) processid=B, runs the insert, and is waiting
3) SQL server processes the Insert for A, then B
4) SQL server then handles the output param for B or A (its not clear which goes first)
5) When A gets the Identity value it's from B's insert. (no serializable transaction, so SQL think's this is ok)
6) B gets its identity value (It can get the ID before step 5, this is OK too, B will get the correct value)
The end result is that A got the wrong identity value because no serializable transaction was requested. The "get" on the identity column was irrelevant in this case because they were both hitting the same table.
However, if there were other tables involved the "get" on the identity column would be significant. You're always better off to go with serializable and a "get" for the identity of a specific table.
In my world I tell my programmers to ONLY use scope_identity when you don't know what table your updating. If your insert targets a specific table then be specific, using scope_identity should be rare. Think about why?
In most cases you ALWAYS know what table your inserting to. So you should always ask for the identity of a specific table. Using a random get like scope_identity, does not make sense.
Books online make it quite clear. I challenge you to review books online and see if thier example makes sense.
Good luck
December 5, 2007 at 9:08 am
Thanks for the explanation Doug.
It sure sounded like a concurrency problem. As you say the lock needs to be at the transaction level since both the insert and the Scope_Identity are the work unit that needs to be done as a whole.
Oh and it is Ton- I by the way not Tony.
December 5, 2007 at 9:39 am
"Besides load-testing the proc (set up 1000 connections on your computer and run them all at once, 100 times each), which would reveal any hidden problem in the proc, I would most expect this to be a problem with the data connection you are using."
- this may not be a real good test because your computer's network card has speed limitation and this could produce a faked positive result.
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply