> In Sql Server 2000 I have two tables T1 & T2, where T2 contains a key to
> T1. I have one process P1 that deletes all the records in T2 and then
> T1, and then inserts new records into T1 and then T2, all done in a
> transaction (default isolation level). When complete, the P1 notifies
> another process P2 that it has finished, and P2 immediately loads all
> the data from T1 and then T2.
>
> Problem...
>
> P2 occasionally fails when loading T2 because it can't find a referenced
> row in the data it loaded from T1. However, on a second attempt to load
> both tables it succeeds. Subsequent queries on the tables show
> problems.
>
> Questions...
>
> Is this likely to be a concurrency issue? If so, why is this happening?
> How can I fix it?
>
> One thing that might be relevant is that a foreign key constraint
> between T2 and T1 is not defined in the database. I have limited control
> over this.

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns97665332A148Yazorman@127.0.0.1...
> Rick (R***@nowhere.com) writes:
>> In Sql Server 2000 I have two tables T1 & T2, where T2 contains a key to
>> T1. I have one process P1 that deletes all the records in T2 and then
>> T1, and then inserts new records into T1 and then T2, all done in a
>> transaction (default isolation level). When complete, the P1 notifies
>> another process P2 that it has finished, and P2 immediately loads all
>> the data from T1 and then T2.
>>
>> Problem...
>>
>> P2 occasionally fails when loading T2 because it can't find a referenced
>> row in the data it loaded from T1. However, on a second attempt to load
>> both tables it succeeds. Subsequent queries on the tables show
>> problems.
>>
>> Questions...
>>
>> Is this likely to be a concurrency issue? If so, why is this happening?
>> How can I fix it?
>>
>> One thing that might be relevant is that a foreign key constraint
>> between T2 and T1 is not defined in the database. I have limited control
>> over this.
>
> I'm afraid that this introduction is not enough to say anything for sure.
> Seeing the code would have helped?
>
> Of course, the missing FK constraint is not good, but as long as you know
> that the data you insert is consistent, it is not a problem. And, anyway,
> if you were to INSERT data into T2 where the key to T1 is missing, it
> should keep on failing.
>
> How does P2 access the tables? Does it use NOLOCK or READ UNCOMMITTED?
>
> Could you have P1 running again while P2 is running?

:-/

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns9767CF95BB51EYazorman@127.0.0.1...
> Rick (rick@nospam.com) writes:
>> I'm using ado.net, so the code would probably be irrelevant, even if I
>> could extract it from the many layers.
>
> It may be that your code is too complex to be easily understood in a
> newsgroup post, but you are wrong to assume that it is irrelevant. Without
> code, I can at best guess what you are doing.
>
> Since it helped to set the transaction isolation level to serializable for
> the update process, there is obviously something you did not tell us.
>
> My guess is that once instance of P1 runs, alerts P2. P2 starts reading
> T1.
> At the same time a second instance of P2 starts running and deletes the
> row in T2.

"Rick" <R***@nowhere.com> wrote in message
news:uHsgDcLMGHA.1124@TK2MSFTNGP15.phx.gbl...
>
> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message
> news:Xns9767CF95BB51EYazorman@127.0.0.1...
>> Rick (rick@nospam.com) writes:
>>> I'm using ado.net, so the code would probably be irrelevant, even if I
>>> could extract it from the many layers.
>>
>> It may be that your code is too complex to be easily understood in a
>> newsgroup post, but you are wrong to assume that it is irrelevant.
>> Without
>> code, I can at best guess what you are doing.
>>
>> Since it helped to set the transaction isolation level to serializable
>> for
>> the update process, there is obviously something you did not tell us.
>>
>> My guess is that once instance of P1 runs, alerts P2. P2 starts reading
>> T1.
>> At the same time a second instance of P2 starts running and deletes the
>> row in T2.
>
> Thanks Eric. It's important to know that this should not be happening IF
> the sequence of events are as I described. I can only then assume, like
> you, that they are not. This gives me something to work with.