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.

Spew

Answer 1

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 do write some data to the transaction log, although not as much as other operations do. The critical distinction from a transactional perspective, is that in these other types of operations, the data in your database tables being modified is not in the log in a state that allows it to be rolled back.

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 could be attributed to "superstitious" programming, or it could be an indication of a fundamental misunderstanding of the nature of database transactions. A more charitable interpretation is that it is simply the result of an over-application of consistency which is inappropriate and yet another example of Emersons euphemism that:

A foolish consistency is the hobgoblin of little minds,
adored by little statesmen and philosophers and divines

Avisavitaminosis

You should affirm, yes, this is superstitious programming. =) – Fulllength

@Charles, What about MySQL? – Lynlyncean

@Pacerier, I am not fluent in MySQL, but I would be flabbergasted if their product behaved differently in this regard from other relational products. One of the newer non-relational database products, like noSQL, might operate under a different paradigm, but I'd bet MySQL is the same. – Avisavitaminosis

By the way, in SQL Server, you can in fact rollback a TRUNCATE TABLE. in a transaction The reason it is still more efficient than DELETE in terms of logging is that only the page deallocations are logged, rather than the rows. – Karlmarxstadt

Answer 2

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

Ever hear 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.

Austria

@Philip, Thx, in researching your comment, I discovered that things have changed for 'Bulk Insert' since I last reviewed this functionality (SQL 7 or SQL2k) ... – Avisavitaminosis

But two stand-alone queries executed in a single command without an explicit transaction from code would execute as two implicit transactions in the database with all that means in terms of isolation levels and dirty/written data. – Colly

An addenda from the future: declaring such a transaction does add some overhead to the processing of the query, since SQL has to manage the declared transaction. This overhead is small, but it does exist and can add up if/when used frequently. – Austria

Answer 3

For me, wrapping a single statement in a transaction means that I have the ability to roll it back if I, say, forget a WHERE clause when executing a manual, one-time UPDATE statement. It has saved me a few times.

e.g.

--------------------------------------------------------------
CREATE TABLE T1(CPK INT IDENTITY(1,1) NOT NULL, Col1 int, Col2 char(3));
INSERT INTO T1 VALUES (101, 'abc');
INSERT INTO T1 VALUES (101, 'abc');
INSERT INTO T1 VALUES (101, 'abc');
INSERT INTO T1 VALUES (101, 'abc');
INSERT INTO T1 VALUES (101, 'abc');
INSERT INTO T1 VALUES (101, 'abc');
INSERT INTO T1 VALUES (101, 'abc');

SELECT * FROM T1


--------------------------------------------------------------
/* MISTAKE SCENARIO     (run each row individually) */
--------------------------------------------------------------
BEGIN TRAN YOUR_TRANS_NAME_1;   /* open a trans named YOUR_TRANS_NAME_1 */
    UPDATE T1 SET COL2 = NULL;  /* run some update statement */
    SELECT * FROM T1;       /* OOPS ... forgot the where clause */
ROLLBACK TRAN YOUR_TRANS_NAME_1;    /* since it did bad things, roll it back */
    SELECT * FROM T1;       /* tans rolled back, data restored. */



--------------------------------------------------------------
/* NO MISTAKES SCENARIO (run each row individually) */
--------------------------------------------------------------

BEGIN TRAN YOUR_TRANS_NAME_2;
    UPDATE T1 SET COL2 = 'CBA' WHERE CPK = 4;   /* run some update statement */
    SELECT * FROM T1;               /* did it correctly this time */

COMMIT TRAN YOUR_TRANS_NAME_2           /* commit (close) the trans */

--------------------------------------------------------------

DROP TABLE T1

--------------------------------------------------------------

Reduplicative

Maybe my question wasn't clear. I was referring to code like: begin tran; update foo set col1 = null; commit tran; Which is executed as a single batch. This is a really common pattern in multiple code bases I've maintained and it is also common to see when you trace the sql that an existing app is emitting. You are describing an interactive process that is done in two discrete steps. – Spew

This is true for statements executed manually in a query processing tool, since by explicitly starting a transaction, the tool requires that you explicitly commit (or rollback), instead of doing it automatically. – Avisavitaminosis

Answer 4

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.

Kordula

Ah, subtle difference. But it isn't really a benefit of explicit transactions, I'd think that the extra time that explicit transaction lock single statement transactions would be a clear lose/lose situation-- lower performance and lower concurrency, albeit for probably milliseconds. – Spew

@MatthewMartin: I said nothing of benefits or drawbacks, I just explained the difference. Transactions are not all about performance. For instace, 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 will place a lock request before the first one commits, thus avoiding race condition. In this case you still need to wrap this single statement into a transaction. – Kordula

SQL Server doesn't support genuine nested transactions. Starting another one is a bad idea. sqlskills.com/BLOGS/PAUL/post/… – Titleholder

Answer 5

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.

Unhand

SQL Server triggers run inside an implicit transaction of the DML code that kicked them off. And yes, MS SQL allows you to turn off autocommit. See: msdn.microsoft.com/en-us/library/aa259220(SQL.80).aspx – Coffer

Answer 6

SQL Server has a setting which allows turning autocommit off for a session. It's even the default for some clients (see https://learn.microsoft.com/en-us/sql/t-sql/statements/set-implicit-transactions-transact-sql?view=sql-server-2017)

Depending on a framework and/or a database client you use, not putting each individual command into its own transaction might cause them to be all lumped together into a default transaction. Explicitly wrapping each of them in a transaction clearly declares the intent and actually makes sure it happens the way the programmer intended, regardless of the current autocommit setting, especially if there isn't a company-wide policy on autocommit.

If the begin tran / commit tran commands are being observed in the database (as per your comment here), it is also possible that a framework is generating them on behalf of an unsuspecting programmer. (How many developers closely inspect SQL code generated by their framework?)

I hope this is still relevant, despite the question being somewhat ancient.

Dentistry

Answer 7

To complement @Charles Bretana's answer, MySQL also has the concept of atomic in each single operation:

https://dev.mysql.com/doc/refman/8.0/en/commit.html

By default, MySQL runs with autocommit mode enabled. This means that, when not otherwise inside a transaction, each statement is atomic, as if it were surrounded by START TRANSACTION and COMMIT. You cannot use ROLLBACK to undo the effect; however, if an error occurs during statement execution, the statement is rolled back.

https://dev.mysql.com/doc/refman/8.0/en/innodb-autocommit-commit-rollback.html says:

In InnoDB, all user activity occurs inside a transaction. If autocommit mode is enabled, each SQL statement forms a single transaction on its own.

Interval