March 3, 2009 at 2:09 pm
SQL 2005, SP2
I have the following temp table in this proc:
create table #Table(
EID int primary key,
AID char(10),
ReceiveDate datetime)
Also have the following for this proc due to it causing deadlock if I do not have it:
set transaction isolation level read uncommitted;
When I select into this table I sometime get a primary key violation, this only happens when run from the application, never if run from a query tool. The select statement is in such a way that it can't possibly get a duplicate of the EID.
After long struggles I finally manged to replicate the problem: double click on the "go" button for the report! So, here is my theory I am asking about:
If you somehow manage to start a proc twice from within the same spid at the same time, would the temp table name in tempdb be the same? The same problem also occurs when using a table variable.
Any thoughts on this would be appreciated, but the above is the only plausible reason I can find for this.
March 3, 2009 at 2:28 pm
Temp tables are scoped at procedure level. your "readuncommited" is what is causing this!
* Noel
March 3, 2009 at 2:40 pm
Using read uncommitted is the same as using (NOLOCK) - and, you are finding out exactly why this is not something that you do to work around issues.
There are several scenarios where you will read duplicate data when using either of the above.
SQL Server reads data on page 1 and starts reading page 2 - user updates data on page 1 that causes a page split (creating page 3), SQL Server reads page 3 which has rows already read from page 1.
The above is the simplest - but that is essentially what is happening.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply