事务范围和隔离级别

Question

事务范围和隔离级别

sql-server-2008ado.nettransactionstransactionscope

15

我们在使用TransactionScope时遇到了问题。TransactionScope为我们提供了非常好的灵活性，可以跨越我们的数据访问层使用事务。这样我们可以隐式或显式地使用事务。与ADO.NET事务相比，它还具有一些性能优势，但目前这不是真正的问题。然而，我们在锁定方面存在问题。在下面的示例代码中，虽然隔离级别设置为ReadCommitted，但在主事务（在Main方法中）提交之前，无法从其他客户端对testTable表进行选择SQL语句，因为整个表上有锁。我们还尝试仅在所有方法中使用一个连接，但行为相同。我们的DBMS是SQL Server 2008。我们是否有什么没有理解的地方？

问候 Anton Kalcik

请参阅此示例代码：

class Program
{
    public class DAL
    {
        private const string _connectionString = @"Data Source=localhost\fsdf;Initial Catalog=fasdfsa;Integrated Security=SSPI;";

        private const string inserttStr = @"INSERT INTO dbo.testTable (test) VALUES(@test);";

        /// <summary>
        /// Execute command on DBMS.
        /// </summary>
        /// <param name="command">Command to execute.</param>
        private void ExecuteNonQuery(IDbCommand command)
        {
            if (command == null)
                throw new ArgumentNullException("Parameter 'command' can't be null!");

            using (IDbConnection connection = new SqlConnection(_connectionString))
            {
                command.Connection = connection;
                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public void FirstMethod()
        {
            IDbCommand command = new SqlCommand(inserttStr);
            command.Parameters.Add(new SqlParameter("@test", "Hello1"));

            using (TransactionScope sc = new TransactionScope(TransactionScopeOption.Required))
            {
                ExecuteNonQuery(command);
                sc.Complete();
            }
        }

        public void SecondMethod()
        {
            IDbCommand command = new SqlCommand(inserttStr);
            command.Parameters.Add(new SqlParameter("@test", "Hello2"));

            using (TransactionScope sc = new TransactionScope(TransactionScopeOption.Required))
            {
                ExecuteNonQuery(command);
                sc.Complete();
            }
        }
    }

    static void Main(string[] args)
    {

        DAL dal = new DAL();
        TransactionOptions tso = new TransactionOptions();
        tso.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;

        using (TransactionScope sc = new TransactionScope(TransactionScopeOption.Required,tso))
        {
            dal.FirstMethod();
            dal.SecondMethod();
            sc.Complete();
        }
    }
}