Getting timeout errors with SqlTransaction on same table

Question

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".

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.

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?

Thanks for reading. Please suggest

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();
            }

score 2 · Answer 1 · answered Oct 09 '13 at 20:09

2

you can increase the time out of your SqlConnection object.

you can do this with your ConnString:

 string connStr = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated   
 Security=SSPI;Connection Timeout=300";

answered Oct 09 '13 at 20:09

DeadlyDan

669
2
8
20

I will try that.. but is there is any issue with what I am doing? esepcially sqltransaction process started and then on the same table, I query? – Anirudh Oct 09 '13 at 20:11
nested transactions are not unheard of - but in your case you only need an outer transaction if the exception is thrown you will rollback everyting. you can remove the line command_serial.Transaction = InsertUpdateSerialNumbers; from the method CheckSerialNumber. – DeadlyDan Oct 09 '13 at 20:29
If I remove InsertUpdateSerialNumbers against the command (that does insert), then how does it rollback when there are failures or errors? I do have rollback in the program on few exceptions. – Anirudh Oct 09 '13 at 21:41

score 1 · Answer 2 · answered Oct 09 '13 at 20:35

1

Many of the objects you are using implement IDisposable, and you should be wrapping them with using statements. Without these using statements, .NET won't necessarily get rid of your objects until an undetermined time when the garbage collector runs, and could block subsequent queries if it's still holding a transaction open somewhere.

So for example, you'll need to wrap your connections with using statements:

    using (conn_new = new SqlConnection(ConnString)) {
        ...

answered Oct 09 '13 at 20:35

pattermeister

3,132
2
25
27

I started with using but problem I encountered is I will open the connection for every line read. I am still thinking how I can fit in the above scenario. – Anirudh Oct 09 '13 at 21:13

borkovski · Accepted Answer · 2013-10-09T20:53:41.930

1

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.

To start off:

Set command_serial_new query as:

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

Think about using lower isolation level to query inserted rows as well (set it to read uncommited).
Close your connections and transactions.
Use just one SqlConnection - you don't need two of them.

edited Oct 09 '13 at 20:53

answered Oct 09 '13 at 20:38

borkovski

938
8
12

I will check on 1 and 2 points. As far as 3, I earlier had just one connection but the executescalar statement won't work blaming on connection. I then opened another connection for executescalar. – Anirudh Oct 09 '13 at 21:06
Actually as p.0. I would open SqlConnections using conn.Open() and conn_new.Open(). – borkovski Oct 09 '13 at 21:29
I tried to use with Using statement for connections but the code/business logic runs across many lines within the open and close (I mean last line of using). But I do close connections as updated in my original post. – Anirudh Oct 09 '13 at 21:43

score 0 · Answer 4 · answered Oct 09 '13 at 20:41

0

If I am not mistaken you need to merge the file content with the table content. For this purpose I would recommend you

Copy the file content in to a temporary table (see temporary tables and BulkInsert)
Use command MERGE (http://msdn.microsoft.com/en-us/library/bb510625.aspx) to merge the temporary table content with the original table

answered Oct 09 '13 at 20:41

Yaugen Vlasau

2,148
1
17
38

I am not doing merge. I will be updating few tables including generating some XML files. – Anirudh Oct 09 '13 at 21:07

Getting timeout errors with SqlTransaction on same table

4 Answers4

Linked