A transaction is a group of SQL operations that are treated as one logical unit of work. A transaction is useful when several related operations must succeed together.
A transaction is a sequence of SQL statements that MySQL handles as one logical operation.
For example, transferring money between two accounts may require:
UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 2;Both operations belong to the same logical transaction.
Transactions help maintain data consistency when multiple related operations are performed.
Transactions are commonly described using four ACID properties:
Atomicity means a transaction is treated as one logical unit of work.
Suppose a payment system performs two operations:
UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 500
WHERE account_id = 2;If the transaction is rolled back before commit, the changes made by the transaction can be undone.
Consistency means a completed transaction should preserve the database rules and constraints that apply to it.
For example, if an order and its payment are required to follow certain application rules, the transaction should leave the database in a valid state.
Isolation controls how concurrent transactions interact with each other.
MySQL provides transaction isolation levels such as:
InnoDB uses REPEATABLE READ as its default isolation level.
Durability means that once a transaction has been successfully committed, its changes are intended to remain stored even if a failure occurs, subject to the storage engine and server configuration.
COMMIT;COMMIT permanently completes the transaction from the application's transaction perspective.
You can explicitly start a transaction using:
START TRANSACTION;You can then execute one or more SQL statements before using COMMIT or ROLLBACK.
BEGIN can also be used to start a transaction.
BEGIN;
UPDATE students
SET status = 'Active'
WHERE student_id = 10;
COMMIT;For normal transaction control, BEGIN and START TRANSACTION are commonly used to begin an explicit transaction.
COMMIT successfully completes the current transaction and makes its changes permanent.
START TRANSACTION;
UPDATE students
SET status = 'Active'
WHERE student_id = 10;
COMMIT;After COMMIT, the transaction's changes are no longer available for rollback through that transaction.
ROLLBACK cancels changes made by the current transaction that have not yet been committed.
START TRANSACTION;
UPDATE students
SET status = 'Inactive'
WHERE student_id = 10;
ROLLBACK;The UPDATE made within the transaction is undone.
START TRANSACTION;
UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 2;
COMMIT;Both UPDATE statements are part of the same transaction.
START TRANSACTION;
UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 2;
ROLLBACK;If the transaction has not been committed, ROLLBACK can undo the transaction's changes.
MySQL normally operates with autocommit enabled for individual statements.
You can check its value using:
SELECT @@autocommit;A value of 1 means autocommit is enabled, while 0 means it is disabled for the session.
You can disable autocommit for the current session:
SET autocommit = 0;Now changes to transactional tables can remain part of an ongoing transaction until COMMIT or ROLLBACK is issued.
Autocommit can be enabled again:
SET autocommit = 1;When autocommit is enabled, each statement that is transactional and completes successfully is generally committed automatically unless it is inside an explicit transaction.
A SAVEPOINT creates a named point inside a transaction.
START TRANSACTION;
UPDATE students
SET status = 'Active'
WHERE student_id = 1;
SAVEPOINT student_update;
UPDATE students
SET status = 'Inactive'
WHERE student_id = 2;You can later roll back to the savepoint instead of rolling back the entire transaction.
Use ROLLBACK TO SAVEPOINT to undo changes made after a savepoint.
START TRANSACTION;
UPDATE students
SET status = 'Active'
WHERE student_id = 1;
SAVEPOINT student_update;
UPDATE students
SET status = 'Inactive'
WHERE student_id = 2;
ROLLBACK TO SAVEPOINT student_update;
COMMIT;The second UPDATE is undone, while the first UPDATE can remain part of the transaction.
You can remove a savepoint when it is no longer needed.
RELEASE SAVEPOINT student_update;After releasing a savepoint, you cannot roll back to that savepoint.
Transactions can contain INSERT statements when the table uses a transactional storage engine such as InnoDB.
START TRANSACTION;
INSERT INTO students
(name, email, city)
VALUES
('Amit Kumar', 'amit@example.com', 'Patna');
COMMIT;The INSERT becomes part of the transaction and is committed when COMMIT is executed.
DELETE operations can also be included in a transaction.
START TRANSACTION;
DELETE FROM students
WHERE student_id = 10;
ROLLBACK;For a transactional table, the DELETE can be undone before the transaction is committed.
MySQL supports four standard transaction isolation levels:
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLEIsolation level controls how one transaction can see data changed by other concurrent transactions.
READ UNCOMMITTED is the least restrictive standard isolation level.
A transaction may be able to read changes made by another transaction before those changes are committed. This can result in phenomena such as dirty reads.
It is generally used only when the application can tolerate very weak read consistency.
With READ COMMITTED, a transaction reads committed data from other transactions rather than uncommitted changes.
The same query executed at different times within a transaction can potentially see newly committed changes from other transactions.
REPEATABLE READ is the default isolation level for InnoDB.
It provides consistent reads within a transaction using InnoDB's transaction model.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;The exact behavior also depends on the type of read and locking used.
SERIALIZABLE is the most restrictive standard isolation level.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;It provides stronger isolation by making concurrent access more restrictive, which can reduce concurrency.
You can inspect the current transaction isolation level with:
SELECT @@transaction_isolation;This helps you understand which isolation level is currently configured for the session.
Consider two accounts:
CREATE TABLE accounts (
account_id INT PRIMARY KEY,
account_name VARCHAR(100),
balance DECIMAL(12,2)
) ENGINE=InnoDB;Transfer ₹1,000 from account 1 to account 2:
START TRANSACTION;
UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 2;
COMMIT;If the application detects a problem before COMMIT, it can use:
ROLLBACK;This is a common example of why transactions are important: multiple related changes should be handled as one logical unit.
CREATE TABLE accounts (
account_id INT PRIMARY KEY,
account_name VARCHAR(100),
balance DECIMAL(12,2)
) ENGINE=InnoDB;
INSERT INTO accounts
(account_id, account_name, balance)
VALUES
(1, 'Amit', 10000.00),
(2, 'Rahul', 5000.00);
START TRANSACTION;
UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 2;
COMMIT;
SELECT *
FROM accounts;If an error occurs before COMMIT, the application can instead execute:
ROLLBACK;You can also use a savepoint:
START TRANSACTION;
UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;
SAVEPOINT transfer_step;
UPDATE accounts
SET balance = balance + 500
WHERE account_id = 2;
ROLLBACK TO SAVEPOINT transfer_step;
COMMIT;This example demonstrates transactions, COMMIT, ROLLBACK, SAVEPOINT, and InnoDB.
Question: Which MySQL command is used to permanently complete the current transaction?