在同一张表上使用SqlTransaction获取超时错误 [英] Getting timeout errors with SqlTransaction on same table

问题描述

public TransImport()
{
    ConnString = ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;

    SqlConnection conn_new;
    SqlCommand command_serial_new;

    SqlConnection conn;
    SqlCommand command_serial;

    SqlTransaction InsertUpdateSerialNumbers;

    conn = new SqlConnection(ConnString);
    command_serial = conn.CreateCommand();

    conn_new = new SqlConnection(ConnString);
    command_serial_new = conn_new.CreateCommand();
    command_serial_new.CommandText = "SELECT 1 FROM YSL00 WHERE SERLNMBR = @slnr";
    var p = new SqlParameter("@slnr", SqlDbType.NVarChar, 50);
    command_serial_new.Parameters.Add(p);

    //Here you will start reading flat file to get serialnumber. 

     InsertUpdateSerialNumbers = conn.BeginTransaction();
     while (!headerFileReader.EndOfStream)
     {
         headerRow = headerFileReader.ReadLine();

         if (CheckSerialNumber(headerFields[0].Trim()))
            DisplayMessage("Good serialnumber"); //this function is not copied here.
      }
      InsertUpdateSerialNumbers.Commit();

}

private Boolean CheckSerialNumber(string SerialNumber)
{
    command_serial_new.Parameters["@slnr"].Value = SerialNumber;
    try
    {
        var itExists = Convert.ToInt32(command_serial_new.ExecuteScalar()) > 0;
        if (!itExists)
        {
            command_serial.Transaction = InsertUpdateSerialNumbers;
            command_serial.CommandText = "INSERT INTO YSL00([Manifest_Number],[PONUMBER],[ITEMNMBR],[SERLNMBR]"
             + "VALUES ('" + Manifest + "','" + PONr + "','" + itemNumber + "','" + serialNr  + "')";
    var insertStatus = command_serial.ExecuteNonQuery();
            return true;
        }
    }
    catch (Exception ex)
    {
        LogException(ex, "Error in CheckSerialNumber =>"+ command_serial_new.CommandText.ToString());
    }
    return false;
}

我收到错误消息超时到期.在操作完成或服务器没有响应之前经过的超时时间."

I get error "Timeout expired. The timeout period elapsed prior to completion of the operation or server is not responding".

CheckSerialNumber函数还向YSL00(与我执行executescalar的同一表.请参见上面的代码)进行插入. 如前所述，我读取和更新YSL000表的平面文件中有1000行.

The CheckSerialNumber function also does an insert to YSL00 (the same table where I had executescalar. See code above). As I mentioned earlier there are 1000s of line in a flat file that I read and update YSL000 table.

请注意，我有两个单独的sqlcommand和两个单独的连接来处理此问题.原因是使用sqltransaction，它不允许我在同一张表上进行查询.我认为可能是因为这个原因导致超时吗?

Note that I have two separate sqlcommands and also two separate connections to handle this. Reason is with sqltransaction it doesn't let me to query on the same table. I think timeout may be happening because of this?

感谢您的阅读.请建议

更新1:由于我没有粘贴完整的代码，所以我想提一下，使用程序中的以下代码可以完成处置.

Update 1: Since I have not pasted entire code, I want to mention that dispose is done using below code in the program.

            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }

            if (conn_new != null)
            {
                conn_new.Close();
                conn_new.Dispose();
            }

推荐答案

我认为默认隔离级别(已提交读)正在阻止您的"CheckSerialNumber"方法生效. Command_serial_new将不考虑循环中插入的行-这可能会导致一些麻烦.老实说，我还会寻找一些僵局.也许command_serial_new实际上完全被其他事务阻止.

I think default isolation level - read commited - is preventing your 'CheckSerialNumber' method from being effective. Command_serial_new will not take into consideration rows inserted in your loop - this might lead to some troubles. To be honest I would also look for some deadlock. Perhaps command_serial_new is actually completely blocked by the other transaction.

要开始:

1. 将command_serial_new查询设置为:

SELECT 1 FROM YSL00 WITH (NOLOCK) WHERE SERLNMBR = @slnr

1. 考虑使用较低的隔离级别来查询插入的行(将其设置为未提交).
2. 关闭您的联系和交易.
3. 仅使用一个SqlConnection-您不需要两个.

这篇关于在同一张表上使用SqlTransaction获取超时错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！