MySQL Transaction

A MySQL transaction is a sequence of one or more SQL statements executed as a single unit of work.

• Ensures data consistency and reliability.
• Saves changes permanently using COMMIT.
• Reverts changes using ROLLBACK if an error occurs.

ACID Properties

The reliability of MySQL transactions is based on the ACID properties.

• Atomicity: A transaction is treated as a single unit of work. Its changes are either committed together or rolled back before they are committed.
• Consistency: A transaction moves the database from one valid state to another while enforcing all constraints and rules.
• Isolation: Multiple transactions execute independently without affecting each other's results.
• Durability: Once a transaction is committed, the changes remain permanent even if the database server crashes.

MySQL Transaction Control Commands

Transaction control commands are used to start, save, undo or finalize transactions.

START TRANSACTION Command

SQL statements executed after START TRANSACTION are part of the transaction until it is committed or rolled back.

Syntax

START TRANSACTION;

Example: Transfer Money Between Accounts

Suppose there are two bank accounts. We want to transfer ₹500 from one account to another.

Query:

START TRANSACTION;

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

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

COMMIT;

• The amount is deducted from account 101 and credited to account 102. Since both statements execute successfully, the transaction is committed.
• If any statement fails before COMMIT, execute ROLLBACK to undo all changes.

COMMIT Command

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

Syntax

COMMIT;

Example:

Assume the following table:

Delete all students whose age is 20 and save the changes.

Query

START TRANSACTION;

DELETE FROM student
WHERE age = 20;

COMMIT;

Output:

• Rows where age = 20 are permanently deleted.

ROLLBACK Command

The ROLLBACK command undoes the changes made during the current transaction that have not been committed.

Syntax

ROLLBACK;

Query:

START TRANSACTION;

DELETE FROM student
WHERE age = 20;

ROLLBACK;

Output:

• The deleted rows are restored because the transaction was rolled back.

SAVEPOINT Command

A SAVEPOINT creates a checkpoint inside a transaction. It allows you to roll back only a portion of the transaction instead of canceling the entire transaction.

Syntax

SAVEPOINT savepoint_name;

Query

START TRANSACTION;

SAVEPOINT SP1;

DELETE FROM student
WHERE age = 20;

Output:

• A savepoint named SP1 is created before deleting the records.

ROLLBACK TO SAVEPOINT

The ROLLBACK TO SAVEPOINT command restores the transaction to a previously created savepoint.

Syntax

ROLLBACK TO SAVEPOINT SP1;

Query:

START TRANSACTION;

SAVEPOINT SP1;

DELETE FROM student
WHERE age = 20;

ROLLBACK TO SAVEPOINT SP1;

COMMIT;

Output:

• The deleted rows are restored, but the transaction continues from the savepoint.

RELEASE SAVEPOINT Command

The RELEASE SAVEPOINT command removes a savepoint from the current transaction.

Syntax

RELEASE SAVEPOINT SP1;

Query

START TRANSACTION;

SAVEPOINT SP1;

UPDATE student
SET age = 21
WHERE student_id = 1;

RELEASE SAVEPOINT SP1;

COMMIT;

Output:

• The savepoint is removed and can no longer be used for rollback.

Types of MySQL Transactions

MySQL supports different types of transactions based on their purpose.

• Read Transactions: These transactions only retrieve data using SELECT statements.
• Write Transactions: These transactions modify data using INSERT, UPDATE or DELETE statements.
• Autocommit Transactions: When autocommit mode is enabled, each statement that is not part of an explicit transaction is committed automatically after successful execution.
• Explicit Transactions: Transactions started manually using START TRANSACTION and completed using COMMIT or ROLLBACK.