SQL

Transactions

Learn how to manage database transactions

By TechCoder TeamLast updated: 2026-06-02
In a Nutshell

Learn how to manage database transactions This hands-on tutorial focuses on practical implementation of transactions concepts.

What is a Transaction?

A transaction is a sequence of one or more SQL operations treated as a single unit of work.
If everything is successful, all changes are saved (committed).
If any part fails, all changes are reversed (rolled back).

Real-world Example

Transferring money from Account A to Account B:

  1. Deduct $100 from Account A.
  2. Add $100 to Account B.

Both must happen, or neither should.

Transaction Control Commands

  1. BEGIN TRANSACTION (or just BEGIN): Starts a new transaction.
  2. COMMIT: Saves all changes made during the transaction.
  3. ROLLBACK: Undoes all changes made during the transaction.

Syntax

BEGIN;
-- SQL statements
UPDATE Accounts SET Balance = Balance - 100 WHERE ID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE ID = 2;
COMMIT;

If an error occurs before COMMIT, you can ROLLBACK:

ROLLBACK;
sql-transaction-commit

Commit a Transaction

Problem Statement

Start a transaction, update the Stock of Product ID 1 by decreasing it by 5, and then commit the changes.

Savepoints

You can create points within a transaction to rollback to without rolling back the entire transaction.

SAVEPOINT sp1;
-- do something
ROLLBACK TO sp1;

[!NOTE] By default, many databases (like MySQL) are in AUTOCOMMIT mode, meaning every individual statement is treated as a transaction and saved immediately unless you explicitly BEGIN.