synchronize datatables to prevent modification by other threads

[mankowitz]

I have a dataset with several datatables which are variably updated and refilled from the database. From time to time I get exceptions about corrupted indexes and I suspect that they are related to simultaneous modification of the datatables from different threads.

So, I have a couple of questions:
1. What is the best way to synchronize these activities?
2. When using sync lock, what object should I lock? should I use the datatable itself? Is there any benefit in using datatable.syncroot?

For example (ds is a dataset containing several tables; da is dataadapter)

'in response to user action
SyncLock ds.Tables("Master")
     ' modify rows in table Master
End SyncLock

'elsewhere, runs once every 60 seconds
SyncLock ds.Tables("Master")
     da.Fill(ds, "Master")
End SyncLock

Here is an example error

System.ArgumentNullException occurred
  HResult=-2147467261
  Message=Value cannot be null.
Parameter name: key
  ParamName=key
  Source=mscorlib
  StackTrace:
       at System.Collections.Generic.Dictionary`2.FindEntry(TKey key)
       at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
       at System.Data.DataView.MaintainDataView(ListChangedType changedType, DataRow row, Boolean trackAddRemove)
       at System.Data.Listeners`1.Notify[T1,T2,T3](T1 arg1, T2 arg2, T3 arg3, Action`4 action)
       at System.Data.Index.MaintainDataView(ListChangedType changedType, Int32 record, Boolean trackAddRemove)
       at System.Data.Index.InsertRecord(Int32 record, Boolean fireEvent)
       at System.Data.Index.ApplyChangeAction(Int32 record, Int32 action, Int32 changeRecord)
       at System.Data.DataTable.RecordStateChanged(Int32 record1, DataViewRowState oldState1, DataViewRowState newState1, Int32 record2, DataViewRowState oldState2, DataViewRowState newState2)
       at System.Data.DataTable.SetNewRecordWorker(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge, Boolean suppressEnsurePropertyChanged, Int32 position, Boolean fireEvent, Exception& deferredException)
       at System.Data.DataTable.LoadDataRow(Object[] values, Boolean fAcceptChanges)
       at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
       at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
       at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
       at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
       at Documenter.clsDB2.DAFill(DBTable dbt) in C:\Users\mankowitzs\Documents\Visual Studio 2010\Projects\Documenter\Documenter\ClassDB2.vb:line 1207

[Shaggy Hiker]

Are you suspecting different threads in this one application?

There are some objects you shouldn't lock on, but other than that, there are few rules. What you can always do is create some global object to use for a lock object. It doesn't even matter what that object is:

Public someLockObject As New Object

with that in a module, you can lock on someLockObject from anywhere in the app. It does waste some trivial amount of memory, but only a few bytes for the whole application, and that is the only cost. Locking on the dataset or the datatable may cause trouble in some cases, though I'm not sure. You wouldn't want to lock on the class that holds the dataset, but it seems like locking on the dataset might be ok. Since that's kind of vague, why not just create an object for the purpose of locking?

[mankowitz]

my thought was that I would lock on specific tables. In this application, the Master table is reloaded every 2 minutes or so by a backgroundworker. Meanwhile, the user is able to edit the data in a datagrid. That data is then propagated back to the database. Since there are other tables, I figured that I'd only lock the ones I needed.

[jmcilhinney]

That's not going to work straight away if the DataTable is bound to the grid. If you refresh that DataTable in a secondary thread then that change is also going to affect the grid and you'll get a cross-thread exception. Whether or not you lock on the DataTable or another object will make no difference to that.

What you probably ought to do is populate a new DataTable in the DoWork event handler and then Merge that with the existing DataTable in the RunWorkerCompleted event handler. RunWorkerCompleted is raised on the UI thread and there will thus be no cross-thread issues and no need to lock on anything.

That said, I would suggest always using an object created specifically for the purpose to lock on. If you do that then you never have to even think about whether the object you're locking on might have issues or not and all your other objects are used only for the purpose they were created for.

[Shaggy Hiker]

I'm not sure about that cross-threaded exception, in this case. I was recently demonstrating an app for somebody where a datatable was filled from a task. If the user (me, in this case) clicked on a certain button before the task had finished, it waits on the task for 10 seconds, but then goes ahead and displays the grid. If the task hasn't completed, some of the fields will hold TBD. In that example, I got to the grid with all the records showing TBD. I was about to explain to my audience the significance of that when the records all changed to actual values. That means that the task updated the datatable and the grid updated to show the values. I haven't gone back to check, but that update to the datatable should have occured on whichever thread the task ran on, as the update was part of the task. I was a bit surprised to see the DGV suddenly shift values, but it suggests that updating a datatable on a background thread won't cause a problem for a DGV that uses that datatable as a datasource.

[jmcilhinney]

I'm not sure about that cross-threaded exception, in this case. I was recently demonstrating an app for somebody where a datatable was filled from a task. If the user (me, in this case) clicked on a certain button before the task had finished, it waits on the task for 10 seconds, but then goes ahead and displays the grid. If the task hasn't completed, some of the fields will hold TBD. In that example, I got to the grid with all the records showing TBD. I was about to explain to my audience the significance of that when the records all changed to actual values. That means that the task updated the datatable and the grid updated to show the values. I haven't gone back to check, but that update to the datatable should have occured on whichever thread the task ran on, as the update was part of the task. I was a bit surprised to see the DGV suddenly shift values, but it suggests that updating a datatable on a background thread won't cause a problem for a DGV that uses that datatable as a datasource.

I've seen it happen. If you were using the TPL, did you perhaps execute a task that returned the DataTable? It sounds like maybe you used Await to call an Async function that populated and then returned the DataTable that was then bound on the UI thread. Then again, maybe not. Perhaps the TPL includes some magic to prevent a cross-thread error in that case but I have seen an error occur when an already-bound DataTable was repopulated in the DoWork event handler of a BackgroundWorker.

[Shaggy Hiker]

Well, there must be something odd going on. After looking it over, the datatable is filled from the task. The bulk of the data in the datatable will certainly fill, because the program waits 10 seconds for the task to complete. In that time, a certain set of rows will almost certainly be available, but some of the fields will be set to placeholders. After the 10 seconds have elapsed (or after the task completes, whichever is quicker), the DGV will get the datatable as a datasource, regardless of what state it is in.

This is off topic, but something I'll have to keep an eye on.