Using a transaction in a single update statement

Question

I duplicate some SPs at work, and I found that the one who wrote the code used the transaction in one update statement like this

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

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

Answer 1

The transaction may have been included due to previous or possible future code, which may include other data. Perhaps this developer is just making the habit of wrapping code in transactions to be “safe”?

But if the statement literally only includes one update per line, then there is no benefit to this code. A transaction does not necessarily “block” anything, although actions performed within it may, of course. It simply ensures that all the actions contained in it are performed all or nothing.

Note that a transaction is not about multiple tables - it is about multiple updates. Ensuring that multiple updates occur or not.

So, if you update the same table twice, there will be a difference with or without a transaction. But your example shows only one update statement, supposedly updating only one record.

In fact, it is probably quite common that transactions encapsulate multiple updates in the same table. Imagine the following:

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

This must be enclosed in a transaction to guarantee the correct transfer of money. If someone fails, then another.

Answer 2

I understand that this is wrong because a transaction is used when you want to update multiple tables.

Not necessary. This includes only one table - and only 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

Hope your peer code is more like this, otherwise SQL will throw a syntax error. According to Ypercube's comment, there is no real purpose for placing a single statement within a transaction, but perhaps it 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 releasing adhoc statements directly against SQL, it is recommended that you transfer your statements to the transaction, in case something goes wrong and you need a 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.