I have a dotnet applicaton that executes a set of insert,update,delete statements in transactionaly manner
我有一个dotnet应用程序,以事务方式执行一组插入,更新,删除语句
The code is like this
代码是这样的
try
{
mytrans = mycon.begintransaction();
//execute sql statements
mytrans.commit();
}
catch(Exception)
{
mytrans.rollback();
}
The problem is that sometimes we faced timeout exceptions in rollback and I found that the database size (mdf file) increased!!! So it means Sql will not make implicit rollback? if so how can I recover from this error and go to the original state???
问题是有时我们在回滚时遇到超时异常,我发现数据库大小(mdf文件)增加了!那么这意味着Sql不会进行隐式回滚吗?如果是这样我怎样才能从这个错误中恢复并转到原始状态???
4 个解决方案
#1
The fundamental concept of transactions demands that transactions that are not committed do not affect the state of the database.
事务的基本概念要求未提交的事务不会影响数据库的状态。
File size does not mean anything. RDBMS data structures are far more complex than simply adding a line to a file - they include logs and indices, so the file can grow and shrink quite independently from the amount of data in the DB.
文件大小并不意味着什么。 RDBMS数据结构远比简单地将一行添加到文件复杂得多 - 它们包括日志和索引,因此文件可以独立于数据库中的数据量而增长和缩小。
#2
Any SQL that may have executed will not have yet been committed. If your Rollback for some reason timeouts that won't result in a commit. Hence eventually the DB will realise its all gone pearshaped and will discard the changes.
任何可能已执行的SQL都尚未提交。如果您的回滚由于某种原因超时不会导致提交。因此,最终数据库将实现其全部变形,并将丢弃这些变化。
An increase in MDF size is not an indication that the transaction has been committed. However the results of the transaction need to be put somewhere. The committing of transaction should require the smallest change possible in the DB. Hence pages may be allocated and data written and then on commit just a few other bits pointing in all the right places are tweaked.
MDF大小的增加并不表示事务已提交。但是,交易的结果需要放在某处。提交事务应该要求DB中尽可能小的更改。因此,可以分配页面并写入数据,然后在提交时仅调整指向所有正确位置的几个其他位。
If there is a rollback those last few bits are not tweaked and those allocated pages simply become free pages to be used for other things. You can't expect the DB to just shrink again.
如果存在回滚,那么最后几位不会被调整,并且那些分配的页面只是成为用于其他事物的空闲页面。你不能指望数据库再次缩小。
#3
#4
Once a Rollback has started it MUST complete. Whether you are still connected or not, SQL Server will still complete the Rollback. Failure to complete a rollback leaves your database transactionaly corrupt and in need of recovery.
一旦回滚开始,它必须完成。无论您是否仍在连接,SQL Server仍将完成回滚。如果未能完成回滚,则会导致数据库事务损坏并需要恢复。
#1
The fundamental concept of transactions demands that transactions that are not committed do not affect the state of the database.
事务的基本概念要求未提交的事务不会影响数据库的状态。
File size does not mean anything. RDBMS data structures are far more complex than simply adding a line to a file - they include logs and indices, so the file can grow and shrink quite independently from the amount of data in the DB.
文件大小并不意味着什么。 RDBMS数据结构远比简单地将一行添加到文件复杂得多 - 它们包括日志和索引,因此文件可以独立于数据库中的数据量而增长和缩小。
#2
Any SQL that may have executed will not have yet been committed. If your Rollback for some reason timeouts that won't result in a commit. Hence eventually the DB will realise its all gone pearshaped and will discard the changes.
任何可能已执行的SQL都尚未提交。如果您的回滚由于某种原因超时不会导致提交。因此,最终数据库将实现其全部变形,并将丢弃这些变化。
An increase in MDF size is not an indication that the transaction has been committed. However the results of the transaction need to be put somewhere. The committing of transaction should require the smallest change possible in the DB. Hence pages may be allocated and data written and then on commit just a few other bits pointing in all the right places are tweaked.
MDF大小的增加并不表示事务已提交。但是,交易的结果需要放在某处。提交事务应该要求DB中尽可能小的更改。因此,可以分配页面并写入数据,然后在提交时仅调整指向所有正确位置的几个其他位。
If there is a rollback those last few bits are not tweaked and those allocated pages simply become free pages to be used for other things. You can't expect the DB to just shrink again.
如果存在回滚,那么最后几位不会被调整,并且那些分配的页面只是成为用于其他事物的空闲页面。你不能指望数据库再次缩小。
#3
#4
Once a Rollback has started it MUST complete. Whether you are still connected or not, SQL Server will still complete the Rollback. Failure to complete a rollback leaves your database transactionaly corrupt and in need of recovery.
一旦回滚开始,它必须完成。无论您是否仍在连接,SQL Server仍将完成回滚。如果未能完成回滚,则会导致数据库事务损坏并需要恢复。