Database gets stuck on SELECT operation

Question

I'm using MS Sql database in my application and when I do SELECT operation from database it stucks for a few minutes. And it happens a few times per day. All other SELECTS take several seconds only.

Also I noticed if I close app while SELECT operation is in progress. It will not work at all on any next app starts UNTIL I restart database engine...

Why could it be?

Here is the code snippet:

        using (SqlConnection myConnection = new SqlConnection(connString))
        {
            myConnection.Open();

            SqlCommand command = myConnection.CreateCommand();
            SqlTransaction transaction;

            transaction = myConnection.BeginTransaction("LockTransaction");

            command.Connection = myConnection;
            command.Transaction = transaction;

            try
            {
                int recordsAtOnce = maxToLock;
                command.CommandText =
                "SELECT TOP 1000 id, productName from Parts WHERE part_used is NULL;";

                List<string> idList = new List<string>();
                SqlDataReader myReader = command.ExecuteReader(CommandBehavior.Default);
                while (myReader.Read())
                {
                    string id = myReader.GetString(1);
                    string name = myReader.GetInt32(0).ToString();
                    idList.Add(id);
                }

                myReader.Close();

                string idsStr = "";
                for(int i = 0; i < idList.Count; i++)
                {
                    if (i != 0)
                    {
                        idsStr += ", ";
                    }

                    idsStr += idList[i];
                }

                // lock record
                command.CommandText = "UPDATE Parts SET part_used=\'rt\' WHERE id in (" + idsStr + ")";
                command.Parameters.Clear();
                command.CommandType = CommandType.Text;

                command.ExecuteNonQuery();

                transaction.Commit();                    
            }
            catch (Exception ex)
            {
                transaction.Rollback();                    
            }

Answer 1

How big is the Parts table? That could cause performance problems. Or it could be another transaction locking before you.

Other things:

• Call dispose on your connection, command and reader when done with them via using

• Parameterize the second query rather than string concating. It's more secure and significantly more performant because of how Sql Server works internally.

• minor: If you're looking at thousands of items, use StringBuilder instead of concatenating.

Answer 2

This is most probably because of a lock. If another transaction is writing into the Parts table, your select needs to wait.

Answer 3

I think your reader values are being assigned to the wrong variables.

Try changing:

string id = myReader.GetString(1);
string name = myReader.GetInt32(0).ToString();
idList.Add(id);

To:

string id = myReader.GetInt32(0);
string name = myReader.GetString(1).ToString();
idList.Add(id);