Loading
JAVA
SPRING
ANGULAR
SQL
HTML
CSS
JAVASCRIPT
Data Structure
SQL Transactions
VideosDiscussion
In real-world database systems, operations rarely happen in isolation.
A single business action often involves multiple SQL statements working together.

For example:

  • Transferring money between two bank accounts
  • Placing an order and reducing inventory
  • Updating employee details and logging the change
In such cases, all operations must either succeed together or fail together. Partial updates can lead to data corruption, financial loss, or inconsistent systems.

To handle such scenarios safely, SQL provides a mechanism called Transactions.


What Is a Transaction?

A transaction is a logical unit of work that consists of one or more SQL statements.

A transaction ensures that:

  • All changes are applied permanently, or
  • No changes are applied at all

In simple terms:
A transaction guarantees data consistency even when something goes wrong.


Why Transactions Are Important

Transactions protect the database from:

  • System crashes
  • Power failures
  • Application errors
  • Invalid business operations
They make sure that the database always remains in a reliable and consistent state.


Transaction Control Statements

SQL provides three key commands to manage transactions:

  • COMMIT
  • ROLLBACK
  • SAVEPOINT
Together, these commands give full control over how and when changes are finalized.


COMMIT

The COMMIT command permanently saves all changes made during the current transaction.

Once a COMMIT is executed:

  • Changes are written to the database
  • They cannot be undone
  • Other users can see the updated data


Example: Using COMMIT

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 101;

UPDATE accounts
SET balance = balance + 500
WHERE account_id = 102;

COMMIT;

Here:

  • Money is transferred successfully
  • Both updates are saved permanently
  • The transaction completes safely


When COMMIT Is Used

COMMIT is used when:

  • All queries execute successfully
  • Business logic is satisfied
  • Data is verified and correct
It marks the successful end of a transaction.


ROLLBACK

The ROLLBACK command cancels all changes made during the current transaction.

When ROLLBACK is executed:

  • Database returns to the previous committed state
  • All uncommitted changes are discarded
  • Data remains safe and unchanged


Example: Using ROLLBACK

UPDATE products
SET stock = stock - 10
WHERE product_id = 5;

-- Something goes wrong
ROLLBACK;

Here:

  • The stock update is undone
  • No data is modified
  • The database remains consistent


When ROLLBACK Is Needed

ROLLBACK is commonly used when:

  • An error occurs
  • Validation fails
  • Business rules are violated
  • Partial execution must be prevented
It acts as a safety net.


SAVEPOINT

A SAVEPOINT allows you to mark a specific point inside a transaction.

Instead of rolling back everything, you can:

  • Roll back only part of the transaction
  • Resume from a known safe state
This is especially useful in long and complex transactions.


Example: Using SAVEPOINT

INSERT INTO orders VALUES (1, 'Laptop');
SAVEPOINT order_created;

INSERT INTO order_items VALUES (1, 'Keyboard', 1);
INSERT INTO order_items VALUES (1, 'Mouse', 1);

-- Error occurs
ROLLBACK TO order_created;

COMMIT;

Here:

  • Order record is preserved
  • Faulty item inserts are undone
  • Transaction continues safely


Key Characteristics of SAVEPOINT

  • Works inside a transaction
  • Allows partial rollback
  • Improves control and flexibility
  • Useful for complex workflows


Difference Between COMMIT, ROLLBACK and SAVEPOINT

FeatureCOMMITROLLBACKSAVEPOINT
PurposeSave changesUndo changesMark a point
ScopeEntire transactionEntire or partialPartial only
ReversibleNoYesYes
UsageSuccessFailureRecovery


Auto-Commit vs Manual Transactions

By default, many databases use auto-commit mode, where:

  • Each SQL statement is committed automatically

However, in professional systems:

  • Auto-commit is often disabled
  • Transactions are controlled manually
  • COMMIT and ROLLBACK are used explicitly
Manual control provides greater safety and precision.


Real-World Use Cases

Transactions are essential in:

  • Banking systems
  • E-commerce platforms
  • Inventory management
  • Payroll processing
  • Reservation systems
Anywhere consistency matters, transactions are unavoidable.


Two Minute Drill

  • A transaction is a unit of work
  • COMMIT saves changes permanently
  • ROLLBACK cancels uncommitted changes
  • SAVEPOINT allows partial rollback
  • Transactions protect data intergrity
  • Always use transactions for critical operations