Advice on possible concurrency issue

[Rick]

I have a table TableB which holds references to table TableA. I have one
process Process2, invoked by another process Process1, that deletes all
records in both tables and then repopulates them, all in a single
transaction. It then notifies Process1, which in turn reads the new data.

My problem occurs when Process2 has completed, and Process1 is reading the
data. It first reads in the entire TableA, and then reads in the entire
TableB. However, when reading in TableB, Process1 occasionally finds rows
referencing TableA rows that have not been loaded. When this situation
occurs, Process1 throws an exception. If I then ask Process1 to try to read
the data again, it succeeds. If I look at the raw data, all is well.

My theory is... even though Process2 thinks it has committed all the
changes, there is a time lag (due to asynchronous transaction(s)??). When
Process1 reads TableA, it omits new rows not yet committed. Then when it
gets to loading TableB, it finds rows referencing TableA data that was
missed.

Is my theory likely to be right? If so, would specifying a different
transaction isolation level in the Process2 transaction cure the problem?

Thx!

 

[Sahil Malik [MVP C#]]

Okay your paragraph 1 and paragraph 2 conflict each other. What process
reads and comes second - Process2? or Process1?

Now assuming Process2->Process1,

What isolation level are you reading in, in the process that follows? I
suspect that may be your problem - you need transactional integrity in the
load process as well.

One possible theory of what is happening could be that before you read
Table#2, (even if it is in a transaction), rows in Table#2 aren't locked
yet. As a result you have inconsistency between Table #1 and Table #2. To
prevent this from happening, When you read rows in TableA and B in
Process2(?), first lock all the rows before you read using a Select
(HOLDLOCK) (with some kinda join) before even a single row is read - discard
the results of that select. Now read the two selects you need to populate
the tables. And finally commit that overall transaction. Of course because
you need this level of blocking, concurrent performance will suck.

Re: Async transactions - there is no such thing. If that were the case,
transactions would be completely useless.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
__________________________________________________________