Lesson 27 Advanced Level
27 of 30 lessons

Transactions

Learn about COMMIT, ROLLBACK, and SAVEPOINT for managing database transactions. Transactions ensure data consistency and integrity.

What is a Transaction?

A transaction is a sequence of one or more SQL operations that are executed as a single logical unit of work. A transaction is completed when a COMMIT or ROLLBACK is issued.

Key Properties (ACID)

  • Atomicity – All operations succeed or none at all.
  • Consistency – Ensures database integrity.
  • Isolation – Operations are isolated until committed.
  • Durability – Once committed, the transaction is permanent.

Transaction Control Statements

  • COMMIT – Saves changes made by the transaction.
  • ROLLBACK – Undoes changes made during the transaction.
  • SAVEPOINT – Sets a point to which you can later rollback.

Example Usage

BEGIN;

UPDATE employees
SET salary = salary + 500
WHERE department_id = 10;

SAVEPOINT before_bonus;

UPDATE employees
SET bonus = 1000
WHERE department_id = 10;

ROLLBACK TO before_bonus;

COMMIT;

This updates salaries and bonuses, but rolls back the bonus update while keeping the salary changes.

Practice Task