ef mysql 事务_事务 - EF Core | Microsoft Docs

使用事务Using Transactions

9/26/2020

本文内容

事务允许以原子方式处理多个数据库操作。Transactions allow several database operations to be processed in an atomic manner. 如果已提交事务，则所有操作都会成功应用到数据库。If the transaction is committed, all of the operations are successfully applied to the database. 如果已回滚事务，则所有操作都不会应用到数据库。If the transaction is rolled back, none of the operations are applied to the database.

提示

可在 GitHub 上查看此文章的示例。You can view this article's sample on GitHub.

默认事务行为Default transaction behavior

默认情况下，如果数据库提供程序支持事务，则会在事务中应用对 SaveChanges 的单一调用中的所有更改。By default, if the database provider supports transactions, all changes in a single call to SaveChanges are applied in a transaction. 如果其中有任何更改失败，则会回滚事务且所有更改都不会应用到数据库。If any of the changes fail, then the transaction is rolled back and none of the changes are applied to the database. 这意味着，SaveChanges 可保证完全成功，或在出现错误时不修改数据库。This means that SaveChanges is guaranteed to either completely succeed, or leave the database unmodified if an error occurs.

对于大多数应用程序，此默认行为已足够。For most applications, this default behavior is sufficient. 如果应用程序要求被视为有必要，则应该仅手动控制事务。You should only manually control transactions if your application requirements deem it necessary.

控制事务Controlling transactions

可以使用 DbContext.Database API 开始、提交和回滚事务。You can use the DbContext.Database API to begin, commit, and rollback transactions. 以下示例显示了在单个事务中执行的两个 SaveChanges 操作以及一个 LINQ 查询：The following example shows two SaveChanges operations and a LINQ query being executed in a single transaction:

using var context = new BloggingContext();

using var transaction = context.Database.BeginTransaction();

try

{

context.Blogs.Add(new Blog { Url = "http://blogs.msdn.com/dotnet" });

context.SaveChanges();

context.Blogs.Add(new Blog { Url = "http://blogs.msdn.com/visualstudio" });

context.SaveChanges();

var blogs = context.Blogs

.OrderBy(b => b.Url)

.ToList();

// Commit transaction if all commands succeed, transaction will auto-rollback

// when disposed if either commands fails

transaction.Commit();

}

catch (Exception)

{

// TODO: Handle failure

}

虽然所有关系数据库提供程序都支持事务，但在调用事务 API 时，可能会引发其他提供程序类型或不执行任何操作。While all relational database providers support transactions, other providers types may throw or no-op when transaction APIs are called.

保存点Savepoints

备注

EF Core 5.0 中已引入此功能。This feature was introduced in EF Core 5.0.

如果调用 SaveChanges 且事务已在上下文中进行，则在保存任何数据之前，EF 会自动创建保存点。When SaveChanges is invoked and a transaction is already in progress on the context, EF automatically creates a savepoint before saving any data. 保存点是数据库事务中的点，如果发生错误或出于任何其他原因，可能会回滚到这些点。Savepoints are points within a database transaction which may later be rolled back to, if an error occurs or for any other reason. 如果 SaveChanges 遇到任何错误，则会自动将事务回滚到保存点，使事务处于相同状态，就好像从未开始。If SaveChanges encounters any error, it automatically rolls the transaction back to the savepoint, leaving the transaction in the same state as if it had never started. 这样可以解决问题并重试保存，尤其是在出现乐观并发问题时。This allows you to possibly correct issues and retry saving, in particular when optimistic concurrency issues occur.

警告

保存点与 SQL Server 的多重活动结果集不兼容，因此不使用。Savepoints are incompatible with SQL Server's Multiple Active Result Sets, and are not used. 如果在 SaveChanges过程中出现错误，则该事务可能处于未知状态。If an error occurs during SaveChanges, the transaction may be left in an unknown state.

还可以手动管理保存点，就像管理事务一样。It's also possible to manually manage savepoints, just as it is with transactions. 以下示例在事务中创建保存点，并在失败时回滚到该保存点：The following example creates a savepoint within a transaction, and rolls back to it on failure:

using var context = new BloggingContext();

using var transaction = context.Database.BeginTransaction();

try

{

context.Blogs.Add(new Blog { Url = "https://devblogs.microsoft.com/dotnet/" });

context.SaveChanges();

transaction.CreateSavepoint("BeforeMoreBlogs");

context.Blogs.Add(new Blog { Url = "https://devblogs.microsoft.com/visualstudio/" });

context.Blogs.Add(new Blog { Url = "https://devblogs.microsoft.com/aspnet/" });

context.SaveChanges();

transaction.Commit();

}

catch (Exception)

{

// If a failure occurred, we rollback to the savepoint and can continue the transaction

transaction.RollbackToSavepoint("BeforeMoreBlogs");

// TODO: Handle failure, possibly retry inserting blogs

}

跨上下文事务Cross-context transaction

还可以跨多个上下文实例共享一个事务。You can also share a transaction across multiple context instances. 此功能仅在使用关系数据库提供程序时才可用，因为该提供程序需要使用特定于关系数据库的 DbTransaction 和 DbConnection。This functionality is only available when using a relational database provider because it requires the use of DbTransaction and DbConnection, which are specific to relational databases.

若要共享事务，上下文必须共享 DbConnection 和 DbTransaction。To share a transaction, the contexts must share both a DbConnection and a DbTransaction.

允许在外部提供连接Allow connection to be externally provided

共享 DbConnection 需要在构造上下文时向其中传入连接的功能。Sharing a DbConnection requires the ability to pass a connection into a context when constructing it.

允许在外部提供 DbConnection 的最简单方式是，停止使用 DbContext.OnConfiguring 方法来配置上下文并在外部创建 DbContextOptions，然后将其传递到上下文构造函数。The easiest way to allow DbConnection to be externally provided, is to stop using the DbContext.OnConfiguring method to configure the context and externally create DbContextOptions and pass them to the context constructor.

提示

DbContextOptionsBuilder 是在 DbContext.OnConfiguring 中用于配置上下文的 API，现在即将在外部使用它来创建 DbContextOptions。DbContextOptionsBuilder is the API you used in DbContext.OnConfiguring to configure the context, you are now going to use it externally to create DbContextOptions.

public class BloggingContext : DbContext

{

public BloggingContext(DbContextOptions options)

: base(options)

{

}

public DbSet Blogs { get; set; }

}

替代方法是继续使用 DbContext.OnConfiguring，但接受已保存并随后在 DbContext.OnConfiguring 中使用的 DbConnection。An alternative is to keep using DbContext.OnConfiguring, but accept a DbConnection that is saved and then used in DbContext.OnConfiguring.

public class BloggingContext : DbContext

{

private DbConnection _connection;

public BloggingContext(DbConnection connection)

{

_connection = connection;

}

public DbSet Blogs { get; set; }

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)

{

optionsBuilder.UseSqlServer(_connection);

}

}

共享连接和事务Share connection and transaction

现在可以创建共享同一连接的多个上下文实例。You can now create multiple context instances that share the same connection. 然后使用 DbContext.Database.UseTransaction(DbTransaction) API 在同一事务中登记两个上下文。Then use the DbContext.Database.UseTransaction(DbTransaction) API to enlist both contexts in the same transaction.

using var connection = new SqlConnection(connectionString);

var options = new DbContextOptionsBuilder()

.UseSqlServer(connection)

.Options;

using var context1 = new BloggingContext(options);

using var transaction = context1.Database.BeginTransaction();

try

{

context1.Blogs.Add(new Blog { Url = "http://blogs.msdn.com/dotnet" });

context1.SaveChanges();

using (var context2 = new BloggingContext(options))

{

context2.Database.UseTransaction(transaction.GetDbTransaction());

var blogs = context2.Blogs

.OrderBy(b => b.Url)

.ToList();

}

// Commit transaction if all commands succeed, transaction will auto-rollback

// when disposed if either commands fails

transaction.Commit();

}

catch (Exception)

{

// TODO: Handle failure

}

使用外部 DbTransactions(仅限关系数据库)Using external DbTransactions (relational databases only)

如果使用多个数据访问技术来访问关系数据库，则可能希望在这些不同技术所执行的操作之间共享事务。If you are using multiple data access technologies to access a relational database, you may want to share a transaction between operations performed by these different technologies.

以下示例显示了如何在同一事务中执行 ADO.NET SqlClient 操作和 Entity Framework Core 操作。The following example, shows how to perform an ADO.NET SqlClient operation and an Entity Framework Core operation in the same transaction.

using var connection = new SqlConnection(connectionString);

connection.Open();

using var transaction = connection.BeginTransaction();

try

{

// Run raw ADO.NET command in the transaction

var command = connection.CreateCommand();

command.Transaction = transaction;

command.CommandText = "DELETE FROM dbo.Blogs";

command.ExecuteNonQuery();

// Run an EF Core command in the transaction

var options = new DbContextOptionsBuilder()

.UseSqlServer(connection)

.Options;

using (var context = new BloggingContext(options))

{

context.Database.UseTransaction(transaction);

context.Blogs.Add(new Blog { Url = "http://blogs.msdn.com/dotnet" });

context.SaveChanges();

}

// Commit transaction if all commands succeed, transaction will auto-rollback

// when disposed if either commands fails

transaction.Commit();

}

catch (Exception)

{

// TODO: Handle failure

}

使用 System.TransactionsUsing System.Transactions

如果需要跨较大作用域进行协调，则可以使用环境事务。It is possible to use ambient transactions if you need to coordinate across a larger scope.

using (var scope = new TransactionScope(

TransactionScopeOption.Required,

new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted }))

{

using var connection = new SqlConnection(connectionString);

connection.Open();

try

{

// Run raw ADO.NET command in the transaction

var command = connection.CreateCommand();

command.CommandText = "DELETE FROM dbo.Blogs";

command.ExecuteNonQuery();

// Run an EF Core command in the transaction

var options = new DbContextOptionsBuilder()

.UseSqlServer(connection)

.Options;

using (var context = new BloggingContext(options))

{

context.Blogs.Add(new Blog { Url = "http://blogs.msdn.com/dotnet" });

context.SaveChanges();

}

// Commit transaction if all commands succeed, transaction will auto-rollback

// when disposed if either commands fails

scope.Complete();

}

catch (Exception)

{

// TODO: Handle failure

}

}

还可以在显式事务中登记。It is also possible to enlist in an explicit transaction.

using (var transaction = new CommittableTransaction(

new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted }))

{

var connection = new SqlConnection(connectionString);

try

{

var options = new DbContextOptionsBuilder()

.UseSqlServer(connection)

.Options;

using (var context = new BloggingContext(options))

{

context.Database.OpenConnection();

context.Database.EnlistTransaction(transaction);

// Run raw ADO.NET command in the transaction

var command = connection.CreateCommand();

command.CommandText = "DELETE FROM dbo.Blogs";

command.ExecuteNonQuery();

// Run an EF Core command in the transaction

context.Blogs.Add(new Blog { Url = "http://blogs.msdn.com/dotnet" });

context.SaveChanges();

context.Database.CloseConnection();

}

// Commit transaction if all commands succeed, transaction will auto-rollback

// when disposed if either commands fails

transaction.Commit();

}

catch (Exception)

{

// TODO: Handle failure

}

}

System.Transactions 的限制Limitations of System.Transactions

EF Core 依赖数据库提供程序以实现对 System.Transactions 的支持。EF Core relies on database providers to implement support for System.Transactions. 如果提供程序未实现对 System.Transactions 的支持，则可能会完全忽略对这些 API 的调用。If a provider does not implement support for System.Transactions, it is possible that calls to these APIs will be completely ignored. SqlClient 支持它。SqlClient supports it.

重要

建议你测试在依赖提供程序以管理事务之前 API 与该提供程序的行为是否正确。It is recommended that you test that the API behaves correctly with your provider before you rely on it for managing transactions. 如果不正确，则建议你与数据库提供程序的维护人员联系。You are encouraged to contact the maintainer of the database provider if it does not.

自 .NET Core 2.1 起，System.Transactions 实现不包括对分布式事务的支持，因此不能使用 TransactionScope 或 CommittableTransaction 来跨多个资源管理器协调事务。As of .NET Core 2.1, the System.Transactions implementation does not include support for distributed transactions, therefore you cannot use TransactionScope or CommittableTransaction to coordinate transactions across multiple resource managers.