Search code examples
sql-servertransactions

Using transaction on a single update statement

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?

Solution

  • 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.