Hi all,
I am new to the forum, I am having a weird issue here.

I have a stored procedure that
1. Insert data,
2. update data
3. delete data

all 3 operations are within the same Begin Transaction/Commit and on
the same table.

The issue is that I have an other process that is reading the data, and
often that process sees the updated values but not the delete- meaning
that the row is still present in the result set for a select on that
table.

if I reorganise the stored procedure to
1. Delete
2. Update
3. Insert
the issue is that the newly inserted row is not seen when the updated
rows are seen.

if I move the update in position 3, I have no problem and the reading
process see all the data properly.

My question is what is going on ?  The update values are not supposed
to be seen until the transaction has been commited or rollbacked ?

I have isolation level at Read commited and no hint on the table.

Also If I put WITH(TABLOCK)   in the update statement everything works
fine and the select sees the data only once commited.

Anyone can give some light on this ?
I have an example that I can email to people if they want to try it.

Thanks
Christian

No one with an idea ?

On 11 Nov 2005 12:31:29 -0800, Christian wrote:

Show quoteHide quoteHi Christian,

Based on the description above, I'd say that there's some kind of bug
involved. No offence meant, but the probability that the bug is in your
code is somewhat higher than the probability of a bug in the SQL Server
code. The latter is certainly not impossible, but it is improbable,
given that this is a quite common scenario and SQL Server 2000 is
already a few yuears old, yet yours is the first report of this
behaviour.

Please post a repro script (a script that has CREATE TABLE statements to
set up tables, ISNERT statements to populate them, then the statements
needed to reproduce the problem) that I (and others in the group) can
run on a test database to play with it. Also, please post the result
from running the following query in Query Analyzer:

  SELECT @@VERSION

Best, Hugo

I will work on getting this to the forum.
Thanks

I got a sample,.
the best is to create a separate database with my code in it.
I couldn't attach any file to the post so it can be downloaded from
here:
http://mcandlau.dyndns.org/Files/SampleCode-Forum.zip

It will create 2 tables and 5 SPs.

to test the code,
in one SQL Analyser window run " exec SaveData" stored procedure which
will do INSERT  then Update  then DELETE

In an other SQL analyser windows you can run "Exec ReadData" but I
noticed the flush is very slow using the code from the ReadData
procedure directly into the sql analyser window make the refresh much
fast.

Start the SaveData then Start the readData,  you will see message when
there is a problem in the data.

Also, a problem I just saw is that the SaveData may give a Duplicate
alternate key on slow server, seems like when a new insert comes, the
previous one doesn't seem to be removed... I have some really weird
situation here.

Thanks for the help.

Christian

I forgot the @@Version I tried on 2 servers   SP3 and SP4
it's

Microsoft SQL Server  2000 - 8.00.760 (Intel X86)
    Dec 17 2002 14:22:05
    Copyright (c) 1988-2003 Microsoft Corporation
    Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

And

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Microsoft SQL Server  2000 - 8.00.2040 (Intel X86)
    May 13 2005 18:33:17
    Copyright (c) 1988-2003 Microsoft Corporation
    Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

I found my problem!
Just for future reference, the save procedure had a Create temporary
table which was throwing off the transaction...
changing the create table made everything work as expected.

On 16 Nov 2005 11:03:01 -0800, Christian wrote:

Hi Christian,

Good to hear you got it sorted out.

I had not yet found the time to look into your code. I was hoping to
have some time available today; I'll now use that time to help others.

Best, Hugo

Consecutive values
Random number generation with seed value
Compute sum of count(*) with group by
Copying records from one table to another
Automate Importing File