Ideas on logic/algorithm and how to prevent race i

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?

回答1:

Is there some reason you can't use transactions?

回答2:

I'll admit now that I'm not an expert in this field.

With transactions and cursors you will get lock escalation if your operation is large. E.g. your operation will start locking a row, then a page then a table if it needs to, preventing other operations from functioning. The idiot that I was assumed that SQL Server would just queue these blocked operations up and wait for locks to be released, but it just returns errors and it's up to the API programmer to keep retrying (someone correct me if I'm wrong, or if it's fixed in a later version). If you are happy to be reading possibly old data that you then copy over, like we were, we changed our isolation mode to stop the server blocking operations unnecessarily. ALTER DATABASE [dbname] SET READ_COMMITTED_SNAPSHOT ON;

You may also alter your insert statments to use NOLOCK. But please read up on this.