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?
3条答案
按热度按时间vc9ivgsu1#
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:
That should be wrapped into a transaction, to assure that the money is transferred correctly. If one fails, so must the other.
k0pti3hp2#
I understand that this is wrong because transaction is used when you want to update multiple tables.
Not necessarily. This involves one table only - and just 2 rows:
niknxzdl3#
As per Ypercube's answer, there is no real purpose in placing one statement inside a transaction, but possibly this is a coding standard or similar, or if the SQL is robo-generated by an ORM or other data tier tooling. Wrapping a single statement in a transaction transiently increments the
@@TRANCOUNT
on the connection, and decrements it on completion.Often, when issuing ad-hoc statements directly against SQL, it can be a good idea to wrap your statements in a transaction, just in case something goes wrong and you need to rollback, i.e.
But do be aware that this will hold locks until the transaction is committed, i.e. don't forget to commit or rollback once you're happy with the changes (i.e. don't go home for the day with your IDE / SQL query tool still connected, with open transactions)