A transaction is a group of one or more SQL operations that are treated as a single unit of work. A transaction helps ensure that related changes are completed successfully or handled according to the transaction rules of the database.
A transaction is a sequence of SQL statements executed as one logical unit.
START TRANSACTION;
UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;
UPDATE accounts
SET balance = balance + 1000
WHERE id = 2;
COMMIT;Both updates belong to the same transaction.
Transactions are useful when several operations must remain logically consistent.
In MySQL, START TRANSACTION begins a transaction.
START TRANSACTION;
UPDATE students
SET paid_fee = paid_fee + 1000
WHERE id = 1;The transaction remains open until it is committed or rolled back.
BEGIN can also be used to start a transaction in MySQL.
BEGIN;
UPDATE products
SET quantity = quantity - 1
WHERE id = 10;It starts a new transaction just like START TRANSACTION.
COMMIT permanently saves the changes made during the transaction.
START TRANSACTION;
UPDATE students
SET paid_fee = paid_fee + 500
WHERE id = 1;
COMMIT;After COMMIT, the transaction's changes are saved according to the database transaction rules.
ROLLBACK cancels changes made during the current transaction that have not been committed.
START TRANSACTION;
UPDATE students
SET paid_fee = paid_fee + 500
WHERE id = 1;
ROLLBACK;The uncommitted update is undone.
Suppose ₹1,000 is transferred from one account to another.
START TRANSACTION;
UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;
UPDATE accounts
SET balance = balance + 1000
WHERE id = 2;
COMMIT;The two updates are handled as part of the same transaction.
If a problem occurs before COMMIT, the application can roll back the transaction.
START TRANSACTION;
UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;
UPDATE accounts
SET balance = balance + 1000
WHERE id = 2;
ROLLBACK;The uncommitted changes are cancelled.
Database transactions are commonly described using four ACID properties:
Atomicity means a transaction is handled as a logical unit of work.
START TRANSACTION;
UPDATE accounts
SET balance = balance - 500
WHERE id = 1;
UPDATE accounts
SET balance = balance + 500
WHERE id = 2;
COMMIT;The application should decide whether the complete operation can be committed or needs to be rolled back.
Consistency means a transaction should preserve the database's defined rules and constraints when it completes successfully.
START TRANSACTION;
UPDATE students
SET course_id = 5
WHERE id = 10;
COMMIT;Constraints such as foreign keys help protect data consistency.
Isolation controls how one transaction interacts with other transactions running at the same time.
Different isolation levels provide different visibility and concurrency behavior.
START TRANSACTION;
SELECT balance
FROM accounts
WHERE id = 1;The exact behavior depends on the database engine and configured isolation level.
Durability means committed changes are intended to remain stored even after certain failures, subject to the database system's durability guarantees.
START TRANSACTION;
UPDATE students
SET status = 'Active'
WHERE id = 10;
COMMIT;After a successful COMMIT, the application treats the change as committed.
A SAVEPOINT creates a point inside a transaction to which you can later roll back.
START TRANSACTION;
UPDATE students
SET status = 'Active'
WHERE id = 1;
SAVEPOINT point1;
UPDATE students
SET status = 'Inactive'
WHERE id = 2;The transaction can continue after creating the savepoint.
ROLLBACK TO SAVEPOINT reverses changes made after a specific savepoint without ending the entire transaction.
START TRANSACTION;
UPDATE students
SET status = 'Active'
WHERE id = 1;
SAVEPOINT point1;
UPDATE students
SET status = 'Inactive'
WHERE id = 2;
ROLLBACK TO SAVEPOINT point1;
COMMIT;The changes after point1 are rolled back, while the transaction can continue.
RELEASE SAVEPOINT removes a savepoint that is no longer needed.
START TRANSACTION;
UPDATE students
SET status = 'Active'
WHERE id = 1;
SAVEPOINT point1;
RELEASE SAVEPOINT point1;
COMMIT;The savepoint is removed while the transaction can continue.
A transaction can contain several related SQL statements.
START TRANSACTION;
INSERT INTO orders
(customer_id, total_amount)
VALUES
(5, 2000);
UPDATE products
SET quantity = quantity - 2
WHERE id = 10;
INSERT INTO payments
(order_id, amount)
VALUES
(1, 2000);
COMMIT;These statements can be handled as one logical business operation.
INSERT operations can be performed inside a transaction.
START TRANSACTION;
INSERT INTO students
(name, city)
VALUES
('Rahul', 'Patna');
COMMIT;If the transaction is rolled back before commit, the uncommitted INSERT can be undone.
UPDATE operations can also be included in transactions.
START TRANSACTION;
UPDATE students
SET paid_fee = paid_fee + 1000
WHERE id = 10;
COMMIT;This is useful when the update is part of a larger business operation.
DELETE operations can be performed within a transaction.
START TRANSACTION;
DELETE FROM students
WHERE id = 10;
ROLLBACK;Because the DELETE was not committed, it can be rolled back under normal transactional conditions.
MySQL commonly operates with autocommit enabled by default. In this mode, individual statements are automatically committed unless an explicit transaction is started.
SET autocommit = 0;Autocommit behavior can be changed for a session. Applications should manage transaction boundaries explicitly when multiple statements must be handled together.
MySQL supports transaction isolation levels that control how transactions interact.
Different isolation levels provide different trade-offs between consistency and concurrency.
READ COMMITTED allows a transaction to read data committed by other transactions.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT *
FROM students;
COMMIT;The exact behavior depends on the database engine and transaction configuration.
REPEATABLE READ provides consistent reads within a transaction according to the database's transaction model.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT *
FROM students;
COMMIT;InnoDB uses REPEATABLE READ as its default isolation level in MySQL.
SERIALIZABLE provides the strictest standard isolation level among the commonly available levels.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT *
FROM students;
COMMIT;It can reduce concurrency compared with less strict isolation levels.
Transaction behavior depends on the database engine. In MySQL, InnoDB supports transactions and is commonly used when transactional behavior is required.
CREATE TABLE payments (
id INT PRIMARY KEY,
student_id INT,
amount DECIMAL(10,2)
) ENGINE=InnoDB;Always verify the storage engine and transaction support when designing an application.
| Transaction | ROLLBACK |
|---|---|
| Represents a unit of database work | Reverses uncommitted changes |
| Can contain multiple statements | Can undo the current transaction or return to a savepoint |
| Ends with COMMIT or ROLLBACK | Used when changes should not be kept |
A bank transfer commonly requires two balance changes.
START TRANSACTION;
UPDATE accounts
SET balance = balance - 5000
WHERE id = 1;
UPDATE accounts
SET balance = balance + 5000
WHERE id = 2;
COMMIT;If application validation or another required step fails before the transaction is committed, the application can use:
ROLLBACK;This keeps the related operations under one transaction boundary.
Suppose a student makes a fee payment. The application needs to insert a payment record and update the student's paid fee.
START TRANSACTION;
INSERT INTO payments
(student_id, amount)
VALUES
(101, 2000);
UPDATE students
SET paid_fee = paid_fee + 2000
WHERE id = 101;
COMMIT;If an application error occurs before COMMIT:
ROLLBACK;For more complex operations, a savepoint can also be used:
START TRANSACTION;
INSERT INTO payments
(student_id, amount)
VALUES
(101, 2000);
SAVEPOINT payment_saved;
UPDATE students
SET paid_fee = paid_fee + 2000
WHERE id = 101;
COMMIT;This example demonstrates how transactions can group related payment operations into one logical unit.
Question: Which SQL command permanently saves the changes made in a transaction?