Locking all DataTables in DataSet to perform safe Update

Question

I'm developing an application where i have a single Dataset (it represents an Access DataBase) which has several Datatables in it. Then i have threads that simultaneously are inserting/editing/deleting rows in the various datatables.

From time to time i have a thread that commits the datatable changes to the DataBase (invoking the update method on every datatable). My problem is that the datatables have some relationships that are being violated. Let me give an example, i have this in the thread that commits the changes to the database:

If DS.Tables.Contains("TableA") Then SyncLock DS.Tables("TableA") : TableADataAdapter.Update(DS.Tables("TableA")) : End SyncLock
If DS.Tables.Contains("TableB") Then SyncLock DS.Tables("TableB") : TableBDataAdapter.Update(DS.Tables("TableB")) : End SyncLock

TableA is parent of TableB so there is a column with an ID that every record in TableB must have a corresponding value in TableA

Sometimes after TableA is updated a thread inserts a record in TableA and TableB and when i update TableB there is a missing parent record from TableA and a relation is break (an exception is thrown)

I have tried to lock the DataSet to see if all the DataTables inside the DataSet would become locked objects but the exception remains.

SyncLock DS: Do The Updates : End SyncLock

My question is: Is there any way to Lock all the DataTables simultaneously so i can update the database safely?

Thanks for any advises

Answer 1

So, the final solution is locking individual objects for each DataTable and then locking them all when i want to update:

While modifying rows in the different threads:

SyncLock DataTableALock
    Insert/Edit/Delete Rows
End SyncLock

SyncLock DataTableBLock
    Insert/Edit/Delete Rows
End SyncLock

When i want to commit the changes:

SyncLock DataTableALock
    SyncLock DataTableBLock
        Commit Changes
    End SyncLock
End SyncLock

I think this code is safe and doesn't lock the dataset as a whole every time i want to change a row (just the individual DataTables). It's just ugly but i can live with that.