I am a newbie when it comes to a concurrency issues for data access. There is a lot of documentation on handling concurrency when updating disconnected data but what about when you are creating a new DataSet and filling it with several tables?

Let's take a simple example. Suppose we have a database which has 2 tables: TblFamily which stores info about each family (e.g. family_id, family_name, etc), and TblMember which holds info about each of the members in each of the families (e.g. person_id, person_name, family_id, etc). No family can have zero members.

We want to retrieve info on a particular family and its members to a disconnected DataSet. From what I've read, most books/articles advocate filling the DataSet table by table. So,

Step 1: read one record from TblFamily to a DataTable in my DataSet. This info includes the family_id.

Step 2: use the family_id to retrieve a subset from TblMember of the members for just that family, filling a second DataTable in the same DataSet.

But now suppose the family and its members are deleted from the database between Step 1 and 2. After Step 2, my DataSet will be inconsistent. I have a family with zero members.

Granted, in this simple example, I could test for such a case. Or perhaps use a join query, which, from what I understand, would be an atomic operation and so preclude the problem from arising (*Is this true?*).

But what about more complicated situations? In general, what are the common approaches to handle/prevent data changing in related tables, while you fill a DataSet?

Can anyone point me to any books/papers/info on this?

Thanks,

Charles.