Problem updating two tables in a transaction.

Background...
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.Rick (Rick@.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, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97665332A148Yazorman@.127.0.0.1...
> Rick (Rick@.nowhere.com) writes:
> 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?
I'm using ado.net, so the code would probably be irrelevant, even if I could
extract it from the many layers.
I was not declaring an explicit locking strategy, but instead depending on
the default (read committed?). I can only assume that when P2 read T1, it
got none or only some of the new data. I'm not sure how this could happen.
The strange thing is that when I changed the isolation level of the P1
transaction to serializable, the problem did not recur. This is troubling.
:-/|||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.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9767CF95BB51EYazorman@.127.0.0.1...
> Rick (rick@.nospam.com) writes:
> 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.|||"Rick" <Rick@.nowhere.com> wrote in message
news:uHsgDcLMGHA.1124@.TK2MSFTNGP15.phx.gbl...
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9767CF95BB51EYazorman@.127.0.0.1...
> 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.
Sorry... ERLAND!!|||Why don't you have a ON DELETE CASCADE constraint? Never depend on app
code to maintain your data integrity. This is one of many reason that
a row in a RDBMS is nothing like a record.
The way you chain code together implies a procedural design and
mindset.