在SQL Server 2014中使用事务语句时出现问题 [英] Problem while working with transaction statement in SQL server 2014
问题描述
您好。最近我不得不在Sql Server中使用Update查询。下面提到的方式是在Transaction块中写一个更新查询的好习惯吗？;
-------
开始交易xyz -1
我的更新查询在这里执行-2
回滚交易xyz -3
提交交易xyz -4
-----------
所以最初我先执行2行，取决于我的结果是否正确，我执行了声明3或4.
现在我想问执行声明3或4后，我的交易将结束，并且与我的交易相关的任何类型的流程/线程仍然是活跃的，并且关于正确使用交易及其替代品的建议将非常感谢
预先感谢您的帮助。
我尝试过：
嗨。最近我不得不在Sql Server中使用Update查询。下面提到的方式是在Transaction块中写一个更新查询的好习惯吗？;
-------
开始交易xyz -1
我的更新查询在这里执行-2
回滚交易xyz -3
提交交易xyz -4
-----------
所以最初我先执行2行，取决于我的结果是否正确，我执行了声明3或4.
现在我想问执行声明3或4后，我的交易将结束，并且与我的交易相关的任何类型的流程/线程仍然是活跃的，并且关于正确使用交易及其替代品的建议将非常感激
Hi. Recently i had to use a Update query in Sql Server. Is below mentioned way of writing Update query within Transaction block a good practice?;
-------
Begin Transaction xyz -1
My Update query to execute here -2
Rollback transaction xyz -3
Commit transaction xyz -4
-----------
So initially I executed first 2 lines, depending upon whether my result was correct or not, I executed either statement 3 or 4.
Now i want to ask that after executing statement 3 or 4, will my transaction end and will any sort of process/thread related to my this transaction will still be active and also suggestions regardings proper usage of Transaction and its substitutes will be really appreciated
Thanks in advance for your help.
What I have tried:
Hi. Recently i had to use a Update query in Sql Server. Is below mentioned way of writing Update query within Transaction block a good practice?;
-------
Begin Transaction xyz -1
My Update query to execute here -2
Rollback transaction xyz -3
Commit transaction xyz -4
-----------
So initially I executed first 2 lines, depending upon whether my result was correct or not, I executed either statement 3 or 4.
Now i want to ask that after executing statement 3 or 4, will my transaction end and will any sort of process/thread related to my this transaction will still be active and also suggestions regardings proper usage of Transaction and its substitutes will be really appreciated
推荐答案
COMMIT TRANSACTION [ ^ ] ...使自事务开始以来执行的所有数据修改成为数据库的永久部分，释放事务所持有的资源...
ROLLBACK TRANSACTION [ ^ ] ...擦除从事务开始或保存点所做的所有数据修改。它还释放了交易所持有的资源。
至于何时使用交易......事实上，即使没有定义交易，你也在使用交易 - 它称为隐式交易。 ..例如一个应该更改5条记录的更新语句，总是会改变所有五条或者没有...当语句本身包含在一个事务中时，任何内部故障都会回滚整个语句......
显式声明事务（BEGIN TRANSACTION）的原因是为了在多个SQL语句中获得相同的效果...例如，您将在不同的表中进行一些索引规范化，但它必须是原子的（你可以在一个交易中包装语句......
COMMIT TRANSACTION[^]...makes all data modifications performed since the start of the transaction a permanent part of the database, frees the resources held by the transaction...
ROLLBACK TRANSACTION[^]...erase all data modifications made from the start of the transaction or to a savepoint. It also frees resources held by the transaction.
As for when to use transaction...In fact you are using transactions even without defining it - it called implicit transaction...For instance an update statement that should change 5 records, always will change all the five or nothing...As the statement itself wrapped inside a transaction, any internal failure will roll-back the whole statement...
The reason to explicitly declare a transaction (BEGIN TRANSACTION) is to get the very same effect across multiply SQL statements...For instance, you are going to do some index normalization across different tables, but it must be atomic (all-or-none), you will wrap the statements in a transaction...
这篇关于在SQL Server 2014中使用事务语句时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
