Sometimes when a program processes a small amount of data, everything is smooth and fine, but with a large amount of data, the underlying problem is exposed.
In a project I did, I was responsible for the attendance check of a factory. There are more than 1000 employees in the factory. If each person clocked in four times a day for 30 days a month, the number of attendance records produced is 1000 * 4 * 30 = 120,000. To process these records, I create SQL statements and execute them:
SqlConnection conn = new SqlConnection(strConn);
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
try
{
CEngine.ExecuteNonQuery(trans, CommandType.Text, sql);
trans.Commit();
}
catch(SqlException ex)
{
trans.Rollback();
ErrorCode = ex.Number;
Info = "Data operation failed:" + ex.Message;
}
finally
{
trans.Dispose();
conn.Close();
}
Copy the codeBut it’s running in error. The error message “SqlTransaction has run out; It can no longer be used.”
At first, I suspected it was memory related. Because the system needs to be ready for transaction rollback, each execution of an insert or modify SQL, there is a certain amount of overhead, a large amount of data, I am afraid will not be able to bear. However, I checked the DATA of SQL SERVER and did not see any mention of memory problems.
Then IT occurred to me that there was a time limit for database connection to SqlConnection. The default is 15 seconds. When there’s a lot of data, it’s probably not enough time. So instead:
SqlConnection conn = new SqlConnection(strConn);
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
// Change the connection time to 300 seconds
cmd.CommandTimeout = 300;
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
trans.Commit();
}
catch(SqlException ex)
{
trans.Rollback();
ErrorCode = ex.Number;
Info = "Data operation failed:" + ex.Message;
}
finally
{
trans.Dispose();
conn.Close();
}
Copy the codeProblem solved.