What does a transaction around a single statement do?

Question

I understand how a transaction might be useful for co-ordinating a pair of updates. What I don't understand is wrapping single statements in transactions, which is 90% of what I've ever seen. In fact, in real life code it is more common in my experience to find a series of logically related transactions each wrapped in their own transaction, but the whole is not wrapped in a transaction.

In MS-SQL, is there any benefit from wrapping single selects, single updates, single inserts or single deletes in a transaction?

I suspect this is superstitious programming.

Answer 1

When you start an explicit transaction and issue a DML, the resources being locked by the statement remain locked, and the results of statement are not visible from outside the transaction until you manually commit or rollback it.

This is what you may or may not need.

For instance, you may want to show preliminary results to outer world while still keeping a lock on them.

In this case, you start another transaction which places a lock request before the first one commits, thus avoiding race condition

Implicit transactions are commited or rolled back immediatley after the DML statement completes or fails.

Answer 2

It does nothing. All individual SQL Statements, (with rare exceptions like Bulk Inserts with No Log, or Truncate Table) are automaticaly "In a Transaction" whether you explicitly say so or not.. (even if they insert, update, or delete millions of rows).

EDIT: based on @Phillip's comment below... In current versions of SQL Server, Even Bulk Inserts and Truncate Table write some data to the transaction log, although not as much as other operations do)

All this means is that the changes the statement makes to data in the database are logged to the transaction log so that they can be undone if the operation fails.

The only function that the "Begin Transaction", "Commit Transaction" and "RollBack Transaction" commands provide is to allow you to put two or more individual SQL statements into the same transaction.

EDIT: (to reinforce marks comment...) YES, this would either be superstitious programming, or an indication of a fundamental misunderstanding of the nature of database transactions

Answer 3

As Charles Bretana said, "it does nothing" -- nothing in addition to what is already done.

Ever here of the "ACID" requirements of a relational database? That "A" stands for Atomic, meaning that either the statement works in its entirety, or it doesn't--and while the statement is being performed, no other queries can be done on the data affected by that query. BEGIN TRANSACTION / COMMIT "extends" this locking functionality to the work done by multiple statements, but it adds nothing to single statements.

However, the database transaction log is always written to when a database is modified (insert, update, delete). This is not an option, a fact that tends to irritate people. Yes, there's wierdness with bulk inserts and recovery modes, but it still gets written to.

I'll name-drop isolation levels here too. Fussing with this will impact individual commands, but doing so will still not make a declared-transaction-wrapped query perform any differently than a "stand-alone" query. (Note that they can be very powerful and very dangeroug with multi-statement declared transactions.) Note also that "nolock" does not apply to inserts/updates/deletes -- those actions always required locks.

Answer 4

One possible excuse is that that single statement could cause a bunch of other SQL to run via triggers, and that they're protecting against something going bad in there, although I'd expect any DBMS to have the common sense to use implicit transactions in the same way already.

The other thing I can think of is that some APIs allow you to disable autocommit, and the code's written just in case someone does that.