逻辑/算法,以及如何想法美元的线程写入SqlServer的p $ pvent比赛 [英] Ideas on logic/algorithm and how to prevent race in threaded writes to SqlServer
问题描述
我有以下逻辑:
公共无效InQueueTable(数据表表)
{
INT incomingRows = Table.Rows.Count;
如果(incomingRows> = RowsThreshold)
{
// asyncWriteRows(表)
返回;
}
如果((RowsInMemory + incomingRows)> = RowsThreshold)
{
//复制和明确的内部表
// asyncWriteRows(copyTable)
}
internalTable.Merge(表);
}
有一个问题与此lagorithm:
-
由于
RowsThreshold = 10000
-
如果
incomingRows
看跌RowsInMemory
在RowsThreshold
:(1) 异步写出来的数据,(2) 合并进来的数据 -
如果
incomingRows
的是的过RowsThreshold
,异步写入 输入数据
但是,如果??? 假定第二个线程旋转起来,并呼吁asyncWriteRows(xxxTable);同时,每个线程拥有异步方法将被写入同一个表在SQLServer中: 是否SqlServer的处理这样的多线程写入功能,在同一个表的 <? / P>
跟进
基于格雷格D的建议:
使用(SqlBulkCopy的bulkCopy =新SqlBulkCopy的(的connectionString,
sqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.UseInternalTransaction))
{
//执行bulkcopy
}
不管怎样,我还是有信号的asyncWriteRows(copyTable)的问题。该算法需要决定是否需要继续前进,复制internalTable,清晰internalTable和asyncWriteRows(copyTable)。我认为,我需要做的是移动 internalTable.Copy()
调用它自己的方式:
私人数据表CopyTable(数据表srcTable要)
{
锁(钥匙)
{
返回srcTable.Copy();
}
}
...然后将以下更改INQUEUE方式:
公共无效InQueueTable(数据表表)
{
INT incomingRows = Table.Rows.Count;
如果(incomingRows&GT; = RowsThreshold)
{
// asyncWriteRows(表)
返回;
}
如果((RowsInMemory + incomingRows)&GT; = RowsThreshold)
{
//复制和明确的内部表
// asyncWriteRows(CopyTable(表))
}
internalTable.Merge(表);
}
...最后,添加一个回调方法:
私人无效WriteCallback(对象的IAsyncResult)
{
INT rowCount等=(INT)iaSyncResult.AsyncState;
如果(RowsInMemory&GT; = rowCount等)
{
asyncWriteRows(CopyTable(internalTable));
}
}
这是我所确定的解决方案。任何反馈?
是有一些原因,你不能使用交易?
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
putsRowsInMemory
overRowsThreshold
: (1) asynchronously write out data, (2) merge incoming dataIf
incomingRows
is overRowsThreshold
, 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?
Is there some reason you can't use transactions?
这篇关于逻辑/算法,以及如何想法美元的线程写入SqlServer的p $ pvent比赛的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!