逻辑/算法,以及如何想法美元的线程写入SqlServer的p $ pvent比赛 [英] Ideas on logic/algorithm and how to prevent race in threaded writes to SqlServer

查看:143
本文介绍了逻辑/算法,以及如何想法美元的线程写入SqlServer的p $ pvent比赛的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有以下逻辑:

 公共无效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 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?

解决方案

Is there some reason you can't use transactions?

这篇关于逻辑/算法,以及如何想法美元的线程写入SqlServer的p $ pvent比赛的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
相关文章
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆