默认情况下,一个命令运行在一个事务中。如果需要执行多个命令,所有这些命令都执行完毕,或都没有执行,就可以显示地启动和提交事务。
事务的特征可以用术语ACID来定义,ACID是Atomicity、Consistency、Isolation和Durability的首字母缩写。
- Atomicity(原子性)——表示一个工作单元。在事务中,要么整个工作单元都成功完成,要么都不完成。
- Consistency(一致性)——事务开始前的状态和事务完成后的状态必须有效。在执行事务的过程中,状态可以有临时值。
- Isolation(隔离性)——表示并发进行的事务独立于状态,而状态在事务处理过程中可能发生变化。在事务未完成时,事务A看不到事务B中的临时状态。
- Durability(持久性)——在事务完成后,它必须以可持久的方式存储起来。如果关闭电源或服务器崩溃,该状态在重新启动时必须恢复。
注意:
事务和有效状态很容易用婚礼来解释。新婚夫妇站在事务协调员面前,事务协调员询问一位新人:“你愿意与你身边的男人结婚吗?”如果第一位新人同意,就询问第二位新人:“你愿意与这个女人结婚吗?”如果第二位新人反对,第一位新人就接收到回滚消息。这个事务的有效状态是,要么两人都同意结婚,要么两个人都不同意结婚。如果两个人都同意结婚,事务就会提交,这两个人就都处于已结婚的状态。如果其中一个人反对,事务就会终止,两个人都处于未结婚的状态。无效的状态是:一个人已结婚,而另一个没有结婚。事务确保结果永远不处于无效状态。
在ADO.NET中,通过调用SqlConnection的BeginTransaction方法就可以开始事务。事务总是与一个连接关联起来;不能在多个连接上创建事务。BeginTransaction方法返回一个SqlTransaction,SqlTransaction需要使用运行在相同事务下的命令:
public static void TransactionSample()
{
using (var connection = new SqlConnection(GetConnectionString()))
{
SqlTransaction transaction = connection.BeginTransaction();
//...
}
}
注意:
为什么OpenAsync和BeginTransaction方法在try块之外定义?这些调用也可能失败。例如,如果OpenAsycn方法失败,则不在本地catch块中捕获异常,而是在TransactionSample方法的外部搜索匹配的catch。这是单独处理这些异常的好方法。transaction变量需要在try块之外声明,否则不可能在catch中使用它。
代码示例在ProCSharp.Books表中创建一个记录。使用SQL子句INSERT INTO 添加记录。Books表定义了一个自动递增的标识符,它使用返回创建的标识符的第二条SQL语句SELECT SCOPE_IDENTITY()返回。在实例化SqlCommand对象后,通过设置Connection来分配连接,设置Transaction属性来指定事务。在ADO.NET事务中,不能把事务分配给使用不同连接的命令。不过,可以用相同的连接创建与事务不相关的命令:
public static async Task TransactionSample()
{
using (var connection = new SqlConnection(GetConnectionString()))
{
await connection.OpenAsync();
SqlTransaction transaction = connection.BeginTransaction();
try
{
string sql = "INSERT INTO [ProCSharp].[Books] "+
"([Title],[Publisher],[Isbn],[ReleaseDate]) "+
"VALUES (@Title,@Publisher,@Isbn,@ReleaseDate); "+
"SELECT SCOPE_IDENTITY()";
var command = new SqlCommand()
{
CommandText = sql,
Connection = connection,
Transaction = transaction
};
//...
}
catch (Exception ex)
{
throw;
}
}
}
在定义参数并填充值后,通过调用方法ExecuteScalarAsync来执行命令。这次,ExecuteScalarAsync方法和INSERT INTO子句一起使用,因为完整的SQL语句通过返回一个结果来结束:从SELECT SCOPE_IDENTITY()返回创建的标识符。如果在WriteLine方法后设置一个断点,检查数据库中的结果,在数据库中就不会看到新记录,虽然已经返回了创建的标识符。原因是事务还没有提交:
public static async Task TransactionSample()
{
using (var connection = new SqlConnection(GetConnectionString()))
{
await connection.OpenAsync();
SqlTransaction transaction = connection.BeginTransaction();
try
{
string sql = "INSERT INTO [ProCSharp].[Books] " +
"([Title],[Publisher],[Isbn],[ReleaseDate]) " +
"VALUES (@Title,@Publisher,@Isbn,@ReleaseDate); " +
"SELECT SCOPE_IDENTITY()";
var command = new SqlCommand()
{
CommandText = sql,
Connection = connection,
Transaction = transaction
};
var p1 = new SqlParameter("@Title", SqlDbType.NVarChar, 50)
{
Value = "title 1"
};
var p2 = new SqlParameter("@Publisher", SqlDbType.NVarChar, 50)
{
Value = "wxg"
};
var p3 = new SqlParameter("@Isbn", SqlDbType.NVarChar, 20)
{
Value = "123-4567"
};
var p4 = new SqlParameter("@ReleaseDate", SqlDbType.Date)
{
Value = new DateTime(2020, 7, 1)
};
command.Parameters.AddRange(new SqlParameter[] { p1, p2, p3, p4 });
object id = await command.ExecuteScalarAsync();
Console.WriteLine($"record added with id: {id}");
//...
}
catch (Exception ex)
{
throw;
}
}
}
现在可以在同一事务中创建另一个记录。在示例代码中,使用同样的命令,连接和事务仍然相关,只是在再次调用ExecuteScalarAsync前改变了值。也可以创建一个新的SqlCommand对象,访问同一个数据库中的另一个表。调用SqlTransaction对象的Commit方法,提交事务。之后,就可以在数据库中看到新记录:
command.Parameters["@Title"].Value = "@title 1";
command.Parameters["@Publisher"].Value = "@wxg";
command.Parameters["@Isbn"].Value = "123-45678";
command.Parameters["@ReleaseDate"].Value = new DateTime(2022,11,2);
id = await command.ExecuteScalarAsync();
Console.WriteLine($"record added with id: {id}");
transaction.Commit();
运行结果:
record added with id: 2055
error 不能在具有唯一索引“IX_Books_Isbn”的对象“ProCSharp.Books”中插入重复键的行。重复键值为 (123-4567)。
语句已终止。 rolling back
检查了两个记录的Isbn号吗?它们是相同的。由于在数据库表中指定了Isbn号使用唯一索引,因此写入第二个记录会失败,抛出类型SqlException的异常,异常信息如上所示,因此,Rollback方法会撤销同一事务中的所有SQL命令。状态重置为事务启动之前的状态。这样,第一个记录也不会写入数据库。
如果在调试模式下运行程序,断点激活的时间太长,事务就会中断,因为事务超时了。有用户输入时,并不意味着事务处于活跃状态。为用户输入增加事务的超时时间也不是很有用,因为事务处于活跃状态,会导致在数据库中有一个锁定。根据读写的记录,可能出现行锁、页锁或表锁。为创建事务设置隔离级别,可以影响锁定,因此影响数据库的性能。然而,这也影响事务的ACID属性,例如,并不是所有事务都是隔离的。
应用于事务的默认隔离级别是ReadCommitted。下表显示了可以设置的不同选项。
下表总结了设置最常用的事务隔离级别可能导致的问题。