What Does a Transaction Around a Single Statement Do

What does a transaction around a single statement do?

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

Using transaction on a single update statement

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.

Using SQL Transactions for a single sql query

Individual statements implicitly have their own transactions. By default, individual statements create and rollback/commit themselves. In theory, you can make it so that it will behave like an explicit transaction, although I can't think of a super great reason to do this. https://docs.microsoft.com/en-us/sql/t-sql/statements/set-implicit-transactions-transact-sql?view=sql-server-2017

The only reason I can think of to wrap an individual statement in its own explicit transaction is if you wanted to leave the transaction open so you could test something like blocking, or just maybe check the data while debugging before rolling it back.

SQL - Why transactions?

This made a lot of sense when one is doing multiple updates, for
example, in multiple tables in one go. But basically all of my queries
just update one table at a time. If a query errors, it cancels,
transaction or no transaction.

In your case, it does nothing. A single statement has its own transaction itself. For more information you can read the existed question and answers:

• What does a transaction around a single statement do?
• Transaction necessary for single update query?
• Do i need transaction for joined query?

Is there a difference between a SELECT statement inside a transaction and one that is outside of it?

Yes, the one inside the transaction can see changes made by other previous Insert/Update/delete statements in that transaction; a Select statement outside the transaction cannot.

If all you are asking about is what the Isolation Level does, then understand that all Select statements (hey, all statements of any kind) - are in a transaction. The only difference between one that is explicitly in a transaction and one that is standing on its own is that the one that is standing alone starts its transaction immediately before it executes it, and commits or roll back immediately after it executes;

whereas the one that is explicitly in a transaction can (because it has a Begin Transaction statement) can have other statements (inserts/updates/deletes, whatever) occurring within that same transaction, either before or after that Select statement.

So whatever the isolation level is set to, both selects (inside or outside an explicit transaction) will nevertheless be in a transaction which is operating at that isolation level.

Addition:
The following is for SQL Server, but all databases MUST work in the same way. In SQL Server the Query Processor is always in one of 3 Transaction Modes, AutoCommit, Implicit, or Explicit.

• AutoCommit is the default transaction management mode of the SQL Server Database Engine. .. Every Transact-SQL statement is committed or rolled back when it completes. ... If a statement completes successfully, it is committed; if it encounters any error, it is rolled back. This is the default, and is the answer to @Alex's question in the comments.

• In Implicit Transaction mode, "... the SQL Server Database Engine automatically starts a new transaction after the current transaction is committed or rolled back. You do nothing to delineate the start of a transaction; you only commit or roll back each transaction. Implicit transaction mode generates a continuous chain of transactions. ..." Note that the italicized snippet is for each transaction, whether it be a single or multiple statement transaction.

• The engine is placed in Explicit Transaction mode when you explicitly initiate a transaction with BEGIN TRANSACTION Statement. Then, every statement is executed within that transaction until you explicitly terminate the transaction (with COMMIT or ROLLBACK) or if a failure occurs that causes the engine to terminate and Rollback.

Isolation level for two statements on the same transaction vs. single statement

The problem with the task is possible concurrent write access. Merging SELECT and INSERT into one statement reduces the time frame for possible conflicts to a minimum and is the superior approach in any case. The potential for conflicts is still there. And yes, serializable transaction isolation is one possible (if expensive) solution.

Typically (but that's not what you are asking), the best solution is not to try what you are trying. Gapless sequential numbers are a pain in databases with concurrent write access. If possible, use a serial column instead, which gives you unique ascending numbers - with possible gaps. You can eliminate gaps later or dynamically with in a VIEW. Details:

• Serial numbers per group of rows for compound key

Aside: you don't need parentheses around the SELECT:

INSERT INTO event(kind, num)
SELECT 'A', COALESCE(MAX(num) + 1, 0) FROM event WHERE kind='A';

Is a single SQL Server statement atomic and consistent?

I've been operating under the assumption that a single statement in SQL Server is consistent

That assumption is wrong. The following two transactions have identical locking semantics:

STATEMENT

BEGIN TRAN; STATEMENT; COMMIT

No difference at all. Single statements and auto-commits do not change anything.

So merging all logic into one statement does not help (if it does, it was by accident because the plan changed).

Let's fix the problem at hand. SERIALIZABLE will fix the inconsistency you are seeing because it guarantees that your transactions behave as if they executed single-threadedly. Equivalently, they behave as if they executed instantly.

You will be getting deadlocks. If you are ok with a retry loop, you're done at this point.

If you want to invest more time, apply locking hints to force exclusive access to the relevant data:

UPDATE Gifts  -- U-locked anyway
SET GivenAway = 1
WHERE GiftID = (
   SELECT TOP 1 GiftID
   FROM Gifts WITH (UPDLOCK, HOLDLOCK) --this normally just S-locks.
   WHERE g2.GivenAway = 0
    AND (SELECT COUNT(*) FROM Gifts g2 WITH (UPDLOCK, HOLDLOCK) WHERE g2.GivenAway = 1) < 5
   ORDER BY g2.GiftValue DESC
)

You will now see reduced concurrency. That might be totally fine depending on your load.

The very nature of your problem makes achieving concurrency hard. If you require a solution for that we'd need to apply more invasive techniques.

You can simplify the UPDATE a bit:

WITH g AS (
   SELECT TOP 1 Gifts.*
   FROM Gifts
   WHERE g2.GivenAway = 0
    AND (SELECT COUNT(*) FROM Gifts g2 WITH (UPDLOCK, HOLDLOCK) WHERE g2.GivenAway = 1) < 5
   ORDER BY g2.GiftValue DESC
)
UPDATE g  -- U-locked anyway
SET GivenAway = 1

This gets rid of one unnecessary join.

Transaction necessary for single update query?

Nothing serious can happen. By default, MySQL wraps all single update/insert/delete commands in a transaction. If something goes wrong in the update, then the transaction should be rolled back correctly.

You really only need transactions when you are combining multiple changes and want them all to take effect "at the same time" or "not at all".

You can read more about this in the documentation.

---

Related Topics