交易没有在c＃中回滚(Transaction not rolling back in c#)

交易没有在c＃中回滚(Transaction not rolling back in c#) private void btnConfigure_Click(object sender, EventArgs e) { try { dbConfigure dc = new dbConfigure(); SqlTransaction tr = conn.BeginTransaction(); cmd.Transaction = tr; if (dc.configuration(cmd, ps.tableNames)) tr.Commit(); else { tr.Rollback(); mesg.show("Transaction is Rolled back"); } } catch (Exception ex) { mesg.show(ex.Message); } }

如果我在configuration方法中的任何地方遇到问题，那么它返回false，我可以看到消息Transaction is Rolled Back 。 但实际上事务并没有完全回滚，并且这个函数所做的数据库结构的一些变化仍然存在，尽管回滚是非常不希望的。 我的问题是什么可能是交易回滚失灵的可能性？

除了共享（上面）方法之外，我的项目中没有其他任何事务

小细节

我正在调用我的类dbConfigure一个非常冗长/复杂的函数configuration 。 它对数据库结构进行了一些必要的更改。 比如它

掉落foriegnKeys 删除主键

删除自动增量字段

它在删除之前保存这些键并以所需的顺序/位置重新创建

conn是一个已经打开的SqlConnection ， SqlConnection我没有使用任何连接

cmd是conn.CreateCommand()我在conn.CreateCommand()任何地方都不使用命令

我从不关闭整个过程中的连接，但是当他们完成工作时，SqlDataReader在configuration功能中关闭。

private void btnConfigure_Click(object sender, EventArgs e) { try { dbConfigure dc = new dbConfigure(); SqlTransaction tr = conn.BeginTransaction(); cmd.Transaction = tr; if (dc.configuration(cmd, ps.tableNames)) tr.Commit(); else { tr.Rollback(); mesg.show("Transaction is Rolled back"); } } catch (Exception ex) { mesg.show(ex.Message); } }

If i get problem anywhere in configuration method then it returns me false and I can see the message Transaction is Rolled Back. But actually transaction is not rolled back completely and some changes in database structure made by this function stay there inspite of rollback which is quite undesired. My Question is What can be the possibility of malfunctioning of Transaction roll back?

I have nowhere else any transaction in my project except the shared (above) method

Little Details

I am calling a very lengthy/complex function configuration of my class dbConfigure. It makes some required changes in database strucure. e.g. It

Drops foriegnKeys Drops Primary keys

Drops auto-increment fields

It saves these keys before dropping and recreates in desired order/position

conn is an SqlConnection which is already opened, I use no connection anywhere other than this

cmd is conn.CreateCommand() I use no command anywhere other than this

I never close the connection in this whole process, however SqlDataReader's are closed in configuration function when they do their job.

最满意答案

对数据库结构的更改不是事务性的，因此您无法回滚创建新表

BS。 大多数DDL都是事务性的，可以回滚。 只有涉及与非事务组件交互的更改（如文件系统，例如向数据库添加新文件）才能回滚。 如果在活动事务中调用，任何非事务性的DDL也将非常明确地引发异常。

添加和修改表非常明确是事务性的，并且可以通过示例轻松地进行说明：

begin transaction; create table foo (a int); select * from sys.tables where object_id = object_id('foo'); rollback; select * from sys.tables where object_id = object_id('foo');

因此问题在于OP丢失代码，未发布的部分。

作为一般性注释，应尽可能使用System.Transactions（考虑到默认构造函数已损坏 ）。 如果使用SqlConnection.BeginTransaction，还是更好地依赖于IDisposable：

using (SqlTransaction trn = conn.BeginTransaction()) { ... trn.Commit (); }

System.Transactions应该受到青睐，因为它们不依赖于代码规则，事务范围中的任何代码SqlClient都会自动注册。

并且btw有错误的配置函数引发，不返回false。

而在潜在的真正问题上：如何处理一个不可能在一个单一事务中注册的冗长，复杂的迁移（例如，它只能生成太多的日志）。 答案是唯一可行的选择是在迁移开始时进行数据库备份，如果迁移失败，则从此备份恢复。 为每次迁移操作提供手动， 经过测试和可靠的补偿操作以撤消迁移的替代方案非常困难，错误，并且最终不必要，因为从备份恢复非常简单且可证明是正确的。

Changes to database structure are not transactional, so you cannot rollback creation of a new table, for example

BS. Most DDL is transactional and can be rolled back. Only changes that involve interactions with non transactional components (like the filesystem, eg. adding a new file to a database) cannot be rolled back. Any DDL that is non transactional will also very explicitly raise an exception if is invoked in an active transaction.

Adding and modifying tables is very explicitly transactional, and it can easily be ilustrated with an example:

begin transaction; create table foo (a int); select * from sys.tables where object_id = object_id('foo'); rollback; select * from sys.tables where object_id = object_id('foo');

Therefore the problem lies in the OP missing code, the parts not posted.

As a general comment one should use System.Transactions when possible (with consideration that the default constructor is broken). If using SqlConnection.BeginTransaction one still better rely on the IDisposable:

using (SqlTransaction trn = conn.BeginTransaction()) { ... trn.Commit (); }

System.Transactions should be favored though as they do not depend on code discipline, any code SqlClient in the transaction scope will automatically enroll.

And btw have the configuration function raise on error, not return false.

And on the underlying real problem: how to handle a lenghy, complicated migration that is not possible to be enrolled in one single transaction (eg. it simply could generate too much log). The answer is that the only feasible option is to take a database backup at the beginning of the migration and restore from this backup if the migration fails. The alternative of providing a manual, tested and reliable, compensating action for every migration operation in order to undo the migration is incredibly difficult, errorprone, and ultimately unnecessary since restoring from a backup is so much simpler and provable correct.