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

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 s开发者_如何学编程pins 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?

Is there some reason you can't use transactions?

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.