SQL TRANSACTION

What is SQL TRANSACTION and how do you use it?

Groups one or more SQL statements into an atomic, all-or-nothing unit of work that can be committed or rolled back.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL TRANSACTION: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, Redshift, DB2

SQL TRANSACTION Full Explanation

A TRANSACTION is the fundamental mechanism for ensuring data integrity in relational databases. When a transaction begins, the database records all subsequent changes in a temporary state. If the client issues COMMIT, the changes become permanent and visible to other sessions. If ROLLBACK is issued, every change made since the start of the transaction is undone, leaving the database exactly as it was. Transactions guarantee the ACID properties: Atomicity, Consistency, Isolation, Durability. Most databases run in autocommit mode by default, meaning each statement is its own transaction unless explicitly wrapped in BEGIN or START TRANSACTION. Long-running or poorly isolated transactions can cause locks, deadlocks, and performance bottlenecks. Some systems support nested transactions through SAVEPOINTs, while others do not. Each session manages its own transaction boundaries; there is no global transaction across sessions unless using distributed transaction managers.

SQL TRANSACTION Syntax

BEGIN [TRANSACTION] [transaction_name];
-- one or more DML statements
COMMIT [TRANSACTION] [transaction_name];

-- or
START TRANSACTION;
-- statements
ROLLBACK;

SQL TRANSACTION Parameters

Example Queries Using SQL TRANSACTION

-- Atomic transfer between two accounts
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

-- Undo mistaken update
START TRANSACTION;
UPDATE products SET price = price * 0.5 WHERE id = 42;
ROLLBACK;

Expected Output Using SQL TRANSACTION

First block: both UPDATEs succeed and balances change together
If any statement fails, COMMIT is not reached and the database remains unchanged
Second block: the ROLLBACK cancels the price update, so the products table stays as it was before the START TRANSACTION

Use Cases with SQL TRANSACTION

Protecting multi-statement business logic such as money transfers or inventory movements
Grouping batch updates so that either all rows change or none do
Performing schema migrations safely
Testing data changes then discarding them with ROLLBACK during development

Common Mistakes with SQL TRANSACTION

Forgetting to COMMIT and leaving open transactions that lock rows
Assuming autocommit is off when it is on, causing partial writes
Mixing Data Definition Language inside a transaction in systems that auto-commit DDL (e.g., MySQL prior to 8.0 for some engines)
Expecting nested BEGIN statements to nest when the DB only supports one level

First Introduced In

SQL-92 standard

Frequently Asked Questions

What is the difference between BEGIN and START TRANSACTION?

Both start a new transaction. BEGIN is supported by more systems, while START TRANSACTION is explicit in MySQL and PostgreSQL. In most cases they are interchangeable.

What happens if I forget to COMMIT?

The transaction stays open, locking affected rows or tables. When the session ends, many databases automatically roll back, but resources remain locked until then.

Are DDL statements transactional?

It depends on the database. PostgreSQL and recent MySQL versions treat most DDL as transactional. SQL Server auto-commits DDL. Always check your engine.

How can I check if I am inside a transaction?

Many drivers expose functions like txid_current() in PostgreSQL or @@TRANCOUNT in SQL Server. You can also query session status views specific to your database.