Ideas on logic/algorithm and how to prevent race in threaded writes to SqlServer

Question

I have the following logic:

public void InQueueTable(DataTable Table)
{
    int incomingRows = Table.Rows.Count;

    if (incomingRows >= RowsThreshold)
    {
        // asyncWriteRows(Table)

        return;
    }

    if ((RowsInMemory + incomingRows) >= RowsThreshold)
    {
        // copy and clear internal table
        // asyncWriteRows(copyTable)
    }

    internalTable.Merge(Table);
}

There is one problem with this lagorithm:

• Given RowsThreshold = 10000

• If incomingRows puts RowsInMemory over RowsThreshold: (1) asynchronously write out data, (2) merge incoming data

• If incomingRows is over RowsThreshold, asynchronously write incoming data

But what if??? Assume a second thread spins up and calls asyncWriteRows(xxxTable); also, that each thread owning the asynchronous method will be writing to the same table in SqlServer: Does SqlServer handle this sort of multi-threaded write functionality to the same table?

Follow up
Based on Greg D's suggestion:

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString, 
                                              sqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.UseInternalTransaction))
{
    // perform bulkcopy
}

Regardless, I still have the issue of signaling the asyncWriteRows(copyTable). The algorithm needs to determine the need to go ahead and copy internalTable, clear internalTable, and asyncWriteRows(copyTable). I think that what I need to do is move the internalTable.Copy() call to it's own method:

private DataTable CopyTable (DataTable srcTable)
{
    lock (key)
    {
        return srcTable.Copy();
    }
}

...and then the following changes to the InQueue method:

public void InQueueTable(DataTable Table)
{
    int incomingRows = Table.Rows.Count;

    if (incomingRows >= RowsThreshold)
    {
        // asyncWriteRows(Table)

        return;
    }

    if ((RowsInMemory + incomingRows) >= RowsThreshold)
    {
        // copy and clear internal table
        // asyncWriteRows(CopyTable(Table))
    }

    internalTable.Merge(Table);
}

...finally, add a callback method:

private void WriteCallback(Object iaSyncResult)
{
    int rowCount = (int)iaSyncResult.AsyncState;

    if (RowsInMemory >= rowCount)
    {
        asyncWriteRows(CopyTable(internalTable));
    }
}

This is what I have determined as a solution. Any feedback?

Answer 1

Is there some reason you can't use transactions?