在单个更新语句上使用事务 [英] Using transaction on a single update statement

问题描述

我在工作中为一些 SP 配音，我发现编写代码的人在这样的单个更新语句上使用了事务

I am dubbing some SP at work and I have discover that whoever wrote the code used a transaction on a single update statement like this

begin transaction 
*single update statment:* update table whatever with whatever
commit transaction

我知道这是错误的，因为当您想要更新多个更新时使用事务.我想从理论的角度来理解，使用上面的代码有什么含义?在有和没有事务的情况下更新任何表有什么区别吗?有没有额外的锁什么的?

I understand that this is wrong because transaction is used when you want to update multiple updates. I want to understand from the theoretical point, what are the implications of using the code as above? Is there any difference in updating the whatever table with and without the transaction? Are there any extra locks or something?

推荐答案

可能是由于先前或将来可能涉及其他数据的代码而包含该事务.也许开发人员只是养成了在事务中包装代码的习惯，以确保安全"?

Perhaps the transaction was included due to prior or possible future code which may involve other data. Perhaps that developer simply makes a habit of wrapping code in transactions, to be 'safe'?

但是，如果语句字面上只涉及对单行的一次更新，那么在这种情况下存在该代码确实没有任何好处.事务不一定锁定"任何东西，当然，尽管在其中执行的操作可能会.它只是确保其中包含的所有操作都是全有或全无执行的.

But if the statement literally involves only a single update to a single row, there really is no benefit to that code being there in this case. A transaction does not necessarily 'lock' anything, though the actions performed inside it may, of course. It just makes sure that all the actions contained therein are performed all-or-nothing.

请注意，事务不是关于多个表，而是关于多个更新.它确保多次更新全有或全无.

Note that a transaction is not about multiple tables, it's about multiple updates. It assures multiple updates happen all-or-none.

因此，如果您更新同一个表两次，则有或没有事务都会有所不同.但是您的示例只显示了一个更新语句，大概只更新了一条记录.

So if you were updating the same table twice, there would be a difference with or without the transaction. But your example shows only a single update statement, presumably updating only a single record.

事实上，事务封装对同一个表的多次更新可能很常见.想象一下:

In fact, it's probably pretty common that transactions encapsulate multiple updates to the same table. Imagine the following:

INSERT INTO Transactions (AccountNum, Amount) VALUES (1, 200)
INSERT INTO Transactions (AccountNum, Amount) values (2, -200)

这应该包含在交易中，以确保正确转移资金.如果一个失败，另一个也必须.

That should be wrapped into a transaction, to assure that the money is transferred correctly. If one fails, so must the other.

这篇关于在单个更新语句上使用事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！