SQL Server Using transaction on a single update statement

bxgwgixi  于 2023-03-07  发布在  其他
关注(0)|答案(3)|浏览(131)

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?

vc9ivgsu

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:

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.

k0pti3hp

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:

--- transaction  begin

BEGIN TRANSACTION ;

UPDATE tableX 
SET Balance = Balance + 100
WHERE id = 42 ;

UPDATE tableX 
SET Balance = Balance - 100
WHERE id = 73 ;

COMMIT TRANSACTION ;

--- transaction  end
niknxzdl

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.

begin transaction;  -- Increases @@TRANCOUNT to 1
update table whatever with whatever
commit transaction;  -- DECREMENTS @@TRANCOUNT to 0

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.

begin tran; -- Just in case my query goofs up
update table whatever with whatever
select ... from table ... -- check that the correct updates / deletes / inserts happened
-- commit tran;  -- Only commit if the above check succeeds, else rollback.

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)

相关问题