SQL Transaction: Syntax, Usage, and Examples

An SQL transaction is a sequence of operations performed as a single logical unit of work. Transactions ensure data integrity by following the ACID (Atomicity, Consistency, Isolation, Durability) principles. They help maintain database consistency, especially when executing multiple SQL statements that depend on one another.

How to Use an SQL Transaction

A transaction in SQL begins with the BEGIN TRANSACTION statement and ends with either a COMMIT (to save changes) or a ROLLBACK (to undo changes).

Starting a Transaction

BEGIN TRANSACTION;

Once the transaction starts, all subsequent SQL commands are part of it.

Committing a Transaction

Use COMMIT to permanently save the changes made within a transaction.

COMMIT;

Rolling Back a Transaction

If something goes wrong, use ROLLBACK to undo changes made during the transaction.

ROLLBACK;

Example of a Basic Transaction

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

COMMIT;

If everything runs successfully, COMMIT saves the changes. However, if an issue occurs (such as insufficient funds), a ROLLBACK can be used to undo all changes.

When to Use an SQL Transaction

SQL transactions ensure data integrity when multiple operations need to succeed together. They are useful in scenarios where data consistency is crucial.

Transferring Money Between Accounts

A transaction guarantees that funds are deducted from one account only if they are successfully added to another. If one part fails, the entire transaction is rolled back.

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

COMMIT;

Handling Order Processing

In e-commerce, processing an order involves multiple steps: deducting inventory, recording payment, and updating order status. A transaction ensures all steps complete successfully before committing the changes.

BEGIN TRANSACTION;

UPDATE inventory SET stock = stock - 1 WHERE product_id = 10;
INSERT INTO orders (customer_id, product_id, quantity) VALUES (5, 10, 1);
UPDATE payments SET status = 'Completed' WHERE payment_id = 1001;

COMMIT;

If any step fails (e.g., stock is unavailable), ROLLBACK prevents a partially completed order.

Preventing Data Corruption in Batch Updates

When inserting multiple records, a transaction ensures either all rows are added or none at all.

BEGIN TRANSACTION;

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');

COMMIT;

If one insert fails, a rollback ensures consistency by undoing the changes.

Examples of SQL Transactions

Rolling Back a Failed Transaction

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 200 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 200 WHERE account_id = 2;

-- Simulating an error (e.g., insufficient funds)
IF (SELECT balance FROM accounts WHERE account_id = 1) < 0
BEGIN
    ROLLBACK;
    PRINT 'Transaction Failed: Insufficient funds';
END
ELSE
    COMMIT;

If the account has insufficient funds, ROLLBACK undoes all changes.

Using Savepoints in Transactions

A savepoint allows rolling back only part of a transaction instead of the entire operation.

BEGIN TRANSACTION;

UPDATE employees SET salary = salary + 500 WHERE id = 1;
SAVEPOINT sp1;

UPDATE employees SET salary = salary + 500 WHERE id = 2;

-- If something goes wrong with the second update, rollback only that part
ROLLBACK TO sp1;

COMMIT;

The rollback restores the state from sp1, keeping the first salary update intact.

Learn More About SQL Transactions

ACID Properties of Transactions

SQL transactions follow four key properties to ensure reliability:

• Atomicity: All operations in a transaction succeed or fail together.
• Consistency: The database remains in a valid state before and after the transaction.
• Isolation: Transactions run independently of each other.
• Durability: Once committed, changes persist even if a system failure occurs.

Implicit vs. Explicit Transactions

Some databases support implicit transactions, where every SQL statement runs as its own transaction. Others use explicit transactions, where developers manually start and commit transactions.

Implicit Transaction Example

In SQL Server, implicit transactions automatically start after each statement:

SET IMPLICIT_TRANSACTIONS ON;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
COMMIT;

By default, MySQL operates in autocommit mode, meaning each statement is automatically committed unless explicitly placed in a transaction.

SET autocommit = OFF;
BEGIN TRANSACTION;
UPDATE orders SET status = 'Shipped' WHERE order_id = 101;
COMMIT;

Nested Transactions

Some databases, like SQL Server, allow nested transactions, where one transaction starts inside another.

BEGIN TRANSACTION;
    UPDATE employees SET salary = salary + 100 WHERE id = 1;
    BEGIN TRANSACTION;
        UPDATE employees SET salary = salary + 200 WHERE id = 2;
    COMMIT;
COMMIT;

However, databases like MySQL do not support true nested transactions, so savepoints are used instead.

BEGIN TRANSACTION;
    SAVEPOINT before_update;
    UPDATE employees SET salary = salary + 100 WHERE id = 1;
    ROLLBACK TO before_update;
COMMIT;

Read-Committed vs. Serializable Transactions

Databases handle concurrent transactions using isolation levels, which define how transactions interact.

• Read Committed: Transactions see only committed changes.
• Repeatable Read: Ensures consistent reads, preventing data changes within the transaction.
• Serializable: Prevents concurrent modifications by locking rows, ensuring strict isolation.

Set an isolation level before starting a transaction:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

This prevents other transactions from modifying the same data while yours is running.