SQL Transactions Tutorial

In SQL, transactions are used to maintain data integrity by ensuring that a sequence of SQL statements execute completely or not at all.

Transactions manage sequences of SQL statements that must be executed as a single unit of work, so that the database never contains the results of partial operations.

When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.

When to Use a Transaction?

Transactions are paramount in situations where data integrity would be at risk in the event that any one of a sequence of SQL statements were to fail.

For example, if you were moving money from one bank account to another, you would need to deduct money from one account, and add it to the other. You wouldn’t want it to fail halfway through, otherwise, money could be debited from one account but not credited to the other.

Possible reasons for failure could include insufficient funds, invalid account number, a hardware failure, etc.

So you do not want to be in a situation where it remains like this:

Debit account 1 (Done)
Credit account 2 (Not Done)
Record transaction in transaction journal (Not Done)

That would be really bad. The database would have inconsistent data and money would disappear into thin air. Then the bank would lose a customer (the bank would probably lose all its customers if this kept happening), and you would lose your job.

To save your job, you could use a transaction which would go something like this:

START TRANSACTION
Debit account 1
Credit account 2
Record transaction in transaction journal
END TRANSACTION

You could write conditional logic inside that transaction that rolls back the transaction if anything goes wrong.

For example, if something goes wrong between debiting account 1 and crediting account 2, the whole transaction is rolled back.

Therefore, there would be only two possible outcomes:

Debit account 1 (Not Done)
Credit account 2 (Not Done)
Record transaction in transaction journal (Not Done)

Or:

Debit account 1 (Done)
Credit account 2 (Done)
Record transaction in transaction journal (Done)

This is a simplified depiction, but it’s a classic illustration of how SQL transactions work. SQL transactions have ACID.

Transaction Types

SQL transactions can be run in the following modes.

Transaction mode	Description
Autocommit transaction	Each individual statement is a transaction.
Implicit transaction	A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed, typically with a `COMMIT` or `ROLLBACK` statement depending on the DBMS.
Explicit transaction	Explicitly started with a line such as `START TRANSACTION`, `BEGIN TRANSACTION` or similar, depending on the DBMS, and explicitly committed or rolled back with the relevant statements.
Batch-scoped transaction	Applicable only to multiple active result sets (MARS). An explicit or implicit transaction that starts under a MARS session becomes a batch-scoped transaction.

The exact modes and options available may depend on the DBMS. This table outlines the transaction modes available in SQL Server.

In this article, we’re mainly focussed on explicit transactions.

See How Implicit Transactions Work in SQL Server for a discussion of the difference between implicit transactions and autocommit.

Sytnax

The following table outlines the basic syntax for starting and ending an explicit transaction in some of the more popular DBMSs.

DBMS	Explicit Transaction Syntax
MySQL, MariaDB, PostgreSQL	Explicit transactions start with the `START TRANSACTION` or `BEGIN` statement. `COMMIT` commits the current transaction, making its changes permanent. `ROLLBACK` rolls back the current transaction, canceling its changes.
SQLite	Explicit transactions start with the `BEGIN TRANSACTION` statement and end with the `COMMIT` or `ROLLBACK` statement. Can also end with the `END` statement.
SQL Server	Explicit transactions start with the `BEGIN TRANSACTION` statement and end with the `COMMIT` or `ROLLBACK` statement.
Oracle	Explicit transactions start with the `SET TRANSACTION` statement and end with the `COMMIT` or `ROLLBACK` statement.

In many cases, certain keywords are optional when using explicit transactions. For example in SQL Server and SQLite, you could simply use BEGIN (rather than BEGIN TRANSACTION) and/or you could end with COMMIT TRANSACTION (as opposed to just COMMIT).

There are also various other keywords and options that you can specify when creating a transaction, so see your DBMS’s documentation for the full syntax.

SQL Transaction Example

Here’s an example of a simple transaction in SQL Server:

BEGIN TRANSACTION
    DELETE OrderItems WHERE OrderId = 5006;
    DELETE Orders WHERE OrderId = 5006;
COMMIT TRANSACTION;

In this case, order information is deleted from two tables. Both statements are treated as one unit of work.

We could write conditional logic into our transaction to make it rollback in the event of an error.

Naming a Transaction

Some DBMSs allow you to provide a name for your transactions. In SQL Server, you can add your chosen name after the BEGIN and COMMIT statements.

BEGIN TRANSACTION MyTransaction
    DELETE OrderItems WHERE OrderId = 5006;
    DELETE Orders WHERE OrderId = 5006;
COMMIT TRANSACTION MyTransaction;

SQL Transaction Rollback Example 1

Here’s the previous example again, but with some extra code. The extra code is used to rollback the transaction in the event of an error.:

BEGIN TRANSACTION MyTransaction

  BEGIN TRY

    DELETE OrderItems WHERE OrderId = 5006;
    DELETE Orders WHERE OrderId = 5006;

    COMMIT TRANSACTION MyTransaction

  END TRY

  BEGIN CATCH

      ROLLBACK TRANSACTION MyTransaction

  END CATCH

The TRY...CATCH statement implements error handling in SQL Server. You can enclose any group of T-SQL statements in a TRY block. Then, if an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.

In this case, we use the CATCH block to rollback the transaction. Given it’s in the CATCH block, rollback only occurs if there’s an error.

SQL Transaction Rollback Example 2

Let’s take a closer look at the database we just deleted rows from.

In the previous example, we deleted rows from the Orders and OrderItems tables in the following database:

Database diagram of a customer orders database

In this database, each time a customer places an order, a row is inserted into the Orders table, and one or more rows into the OrderItems table. The number of rows inserted into OrderItems depends on how many different products the customer orders.

Also, if it’s a new customer, a new row is inserted into the Customers table.

In that case, rows need to be inserted into three tables.

In the event of a failure, we wouldn’t want to have a row inserted into the Orders table but no corresponding rows in the OrderItems table. That would result in an order without any order items. Basically, we want both tables to be completely updated or nothing at all.

It was the same when we deleted the rows. We wanted all rows deleted or none at all.

In SQL Server, we could write the following transaction for the INSERT statements.

BEGIN TRANSACTION
    BEGIN TRY 
        INSERT INTO Customers ( CustomerId, CustomerName, PostalAddress, City, StateProvince, ZipCode, Country, Phone )
        VALUES (1006, 'Hi-Five Solutionists', '5 High Street', 'Highlands', 'HI', '1254', 'AUS', '(415) 413-5182');

        INSERT INTO Orders ( OrderId, OrderDate, CustomerId )
        VALUES ( 5006, SYSDATETIME(), 1006 );
        
        INSERT INTO OrderItems ( OrderId, OrderItemId, ProductId, Quantity, ItemPrice )
        VALUES ( 5006, 1, 1, 20, 25.99 );
        
        INSERT INTO OrderItems ( OrderId, OrderItemId, ProductId, Quantity, ItemPrice )
        VALUES ( 5006, 2, 7, 120, 9.99 );

        COMMIT TRANSACTION;
        
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
    END CATCH

This example assumes that there’s logic elsewhere that determines whether or not the customer already exists in the database.

The customer could have been inserted outside of this transaction:


INSERT INTO Customers ( CustomerId, CustomerName, PostalAddress, City, StateProvince, ZipCode, Country, Phone )
VALUES (1006, 'Hi-Five Solutionists', '5 High Street', 'Highlands', 'HI', '1254', 'AUS', '(415) 413-5182');

BEGIN TRANSACTION
    BEGIN TRY 

        INSERT INTO Orders ( OrderId, OrderDate, CustomerId )
        VALUES ( 5006, SYSDATETIME(), 1006 );
        
        INSERT INTO OrderItems ( OrderId, OrderItemId, ProductId, Quantity, ItemPrice )
        VALUES ( 5006, 1, 1, 20, 25.99 );
        
        INSERT INTO OrderItems ( OrderId, OrderItemId, ProductId, Quantity, ItemPrice )
        VALUES ( 5006, 2, 7, 120, 9.99 );

        COMMIT TRANSACTION;
        
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
    END CATCH

If the transaction failed, the customer would still be in the database (but without any orders). The application would need to check whether the customer already exists before doing the transaction.

SQL Transaction with Savepoints

A savepoint defines a location to which a transaction can return if part of the transaction is conditionally cancelled. In SQL Server, we specify a savepoint with SAVE TRANSACTION savepoint_name (where savepoint_name is the name we give to the savepoint).

Let’s rewrite the previous example to include a savepoint:


BEGIN TRANSACTION
    INSERT INTO Customers ( CustomerId, CustomerName, PostalAddress, City, StateProvince, ZipCode, Country, Phone )
    VALUES (1006, 'Hi-Five Solutionists', '5 High Street', 'Highlands', 'HI', '1254', 'AUS', '(415) 413-5182');
    SAVE TRANSACTION StartOrder;

    INSERT INTO Orders ( OrderId, OrderDate, CustomerId )
    VALUES ( 5006, SYSDATETIME(), 1006 );
    
    INSERT INTO OrderItems ( OrderId, OrderItemId, ProductId, Quantity, ItemPrice )
    VALUES ( 5006, 1, 1, 20, 25.99 );
    
    INSERT INTO OrderItems ( OrderId, OrderItemId, ProductId, Quantity, ItemPrice )
    VALUES ( 5006, 2, 7, 120, 9.99 );
    ROLLBACK TRANSACTION StartOrder;
COMMIT TRANSACTION;
SELECT @@TRANCOUNT;

Here, we’ve set a savepoint straight after the customer INSERT statement. Later in the transaction, I use the ROLLBACK statement to instruct the transaction to rollback to that savepoint.

When I run that statement, the customer is inserted, but none of the order information is inserted.

If a transaction is rolled back to a savepoint, it must proceed to completion with more SQL statements if needed and a COMMIT TRANSACTION statement, or it must be canceled altogether by rolling back the entire transaction.

If I move the ROLLBACK statement back to the previous INSERT statement, like this:

BEGIN TRANSACTION
    INSERT INTO Customers ( CustomerId, CustomerName, PostalAddress, City, StateProvince, ZipCode, Country, Phone )
    VALUES (1006, 'Hi-Five Solutionists', '5 High Street', 'Highlands', 'HI', '1254', 'AUS', '(415) 413-5182');
    SAVE TRANSACTION StartOrder;

    INSERT INTO Orders ( OrderId, OrderDate, CustomerId )
    VALUES ( 5006, SYSDATETIME(), 1006 );
    
    INSERT INTO OrderItems ( OrderId, OrderItemId, ProductId, Quantity, ItemPrice )
    VALUES ( 5006, 1, 1, 20, 25.99 );
    ROLLBACK TRANSACTION StartOrder;
    
    INSERT INTO OrderItems ( OrderId, OrderItemId, ProductId, Quantity, ItemPrice )
    VALUES ( 5006, 2, 7, 120, 9.99 );
COMMIT TRANSACTION;
SELECT @@TRANCOUNT;

This produces a foreign key conflict error. Specifically, I get the following error:

(1 row affected)
(1 row affected)
(1 row affected)
Msg 547, Level 16, State 0, Line 13
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_OrderItems_Orders". The conflict occurred in database "KrankyKranes", table "dbo.Orders", column 'OrderId'.
The statement has been terminated.
(1 row affected)

This occurred because, even though the order had already been inserted, that operation was undone when we rolled back to the savepoint. Then the transaction proceeded to completion. But when it encountered the final order item, there was no corresponding order (because that had been undone), and we got the error.

When I check the database, the customer was inserted, but again, none of the order information was inserted.

You can reference the same savepoint from multiple places in the transaction if required.

In practice, you would use conditional programming to return the transaction to a savepont.

Nested Transactions

You can also nest transactions inside other transactions if required.

Like this:

BEGIN TRANSACTION Transaction1;  
    UPDATE table1 ...;
    BEGIN TRANSACTION Transaction2;
        UPDATE table2 ...;
        SELECT * from table1;
    COMMIT TRANSACTION Transaction2;
    UPDATE table3 ...;
COMMIT TRANSACTION Transaction1;

As mentioned, the exact syntax you use to create a transaction will depend on your DBMS, so check your DBMS’s documentation for a complete picture of your options when creating transactions in SQL.