Using transaction on a single update statement

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

Hopefully your colleague's code looks more like this, otherwise SQL will issue a syntax error. As per Ypercube's comment, there is no real purpose in placing one statement inside a transaction, but possibly this is a coding standard or similar.

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

Often, when issuing adhoc statements directly against SQL, it is a good idea to wrap your statements in a transaction, just in case something goes wrong and you need to rollback, i.e.

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