sqlserver.exe删除锁定本地数据库文件需要多长时间?

本文关键字：长时间 文件 数据库 删除 exe 锁定 sqlserver | 更新日期: 2023-09-27 18:18:32

我有一个应用程序，它执行几个选择操作，然后执行一些插入操作。可能这样做了大约200次，然后断开连接并关闭一切(据我所知)。下面是代码:

using (TransactionScope scope = new TransactionScope())
{
  using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["FrameBudgetDB"].ToString()))
  {
    conn.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;
    cmd.CommandText = string.Format("SELECT TOP 1 category_id FROM businesses WHERE '{0}' LIKE CONCAT('%',description,'%')", transDescription.Replace("'", "''"));
    SqlDataReader reader = cmd.ExecuteReader();
    try
    {
      if (reader.HasRows)
      {
        while (reader.Read())
        {
          categoryId = (int)reader[0];
        }
      }
      else
      {
        categoryId = 11; // Extgra Expenses
      }
    }
    finally
    {
      reader.Close();                      
      cmd.Dispose();  
    }

    // Get Transaction Type
    int transTypeId = 0;
    cmd = new SqlCommand();
    cmd.Connection = conn;
    cmd.CommandText = string.Format("SELECT trans_type_id FROM transaction_types WHERE description = '{0}'", transType);
    reader = cmd.ExecuteReader();
    try
    {
      if (reader.HasRows)
      {
        while (reader.Read())
        {
          transTypeId = (int)reader[0];
        }
      }
    }
    finally
    {
      reader.Close();
      cmd.Dispose();
    }
    try
    {
      cmd = new SqlCommand();
      cmd.Connection = conn;
      cmd.CommandText = string.Format(
        "BEGIN " +
          "IF NOT EXISTS(SELECT * FROM transactions " +
                   "WHERE transaction_date = @TransDate " +
                   "AND trans_type_id = @TransTypeId " +
                   "AND amount = @Amount " +
                   "AND ((from_bank = 1 AND description = @TransDesc) " +
                    "OR (from_bank = 0)) " +
                   "AND(category_id = @CategoryId OR previous_category_id = @CategoryId )) " +
           "BEGIN " +
             "INSERT INTO transactions(transaction_date, description, trans_type_id, import_date, category_id, amount, from_bank) " +
             "VALUES(@TransDate, @TransDesc, @TransTypeId, @ImportDate, @CategoryId, @Amount, 1) " +
           "END " +
        "END");
      cmd.Parameters.AddWithValue("@TransDate", transDate);
      cmd.Parameters.AddWithValue("@TransDesc", transDescription);
      cmd.Parameters.AddWithValue("@TransTypeId", transTypeId);
      cmd.Parameters.AddWithValue("@Amount", amount);
      cmd.Parameters.AddWithValue("@CategoryId", categoryId);
      cmd.Parameters.AddWithValue("@ImportDate", DateTime.Now);
      rowsInserted = rowsInserted + cmd.ExecuteNonQuery();
      cmd.Dispose();
    }
    catch (Exception e)
    {
      Console.WriteLine(e.Message);
    }
    conn.Close();
    conn.Dispose();                 
  }
  scope.Complete();
  scope.Dispose();
}
}

在所有插入之后，我想通过复制并将其移动到其他地方来对db进行备份，但是sqlserver.exe在该文件上持有锁很长时间。我试过让应用程序等待，但它似乎持有锁很长时间。

我已经建立了一个锁检查while循环，但它会很高兴知道它实际上应该花多长时间?

sqlserver.exe删除锁定本地数据库文件需要多长时间?

似乎在执行插入的代码的最后一部分有问题。

因为SqlCommand在try..catch的最后一行被处理，如果发生异常，可能根本不会执行。

最好是在using内部使用单独的SqlCommands，以确保一切都被正确地处理。这可能是连接保持打开状态的一个原因。代码:

try
{
  cmd = new SqlCommand();
  cmd.Connection = conn;
  ...
  rowsInserted = rowsInserted + cmd.ExecuteNonQuery();
  cmd.Dispose(); // <- This line might not be executed if exception occurs in previous line
}

提出更正:

try
{
  using (SqlCommand cmd = new SqlCommand())
  {
       cmd.Connection = conn;
       ...
       rowsInserted = rowsInserted + cmd.ExecuteNonQuery();
       // Call dispose is not needed
  }
}

相关文章：