Transaction Control: START TRANSACTION, COMMIT, ROLLBACK0%

Transaction Control: START TRANSACTION, COMMIT, ROLLBACK

Intermediate13 min read•Updated: 2026-09-12

Transaction Control: START TRANSACTION, COMMIT, ROLLBACK

By default, MySQL operates in autocommit mode. In autocommit mode, every individual SQL statement (INSERT, UPDATE, DELETE) is treated as an independent transaction that is committed immediately upon execution.

To bundle multiple SQL statements into a single atomic unit, you must use Transaction Control Language (TCL) commands: START TRANSACTION, COMMIT, and ROLLBACK.

Understanding the AUTOCOMMIT Variable

In MySQL, the autocommit session variable dictates transaction boundaries:

1-- Check current autocommit status (1 = ON, 0 = OFF)
2SELECT @@autocommit;
3 
4-- Disable autocommit for current session
5SET autocommit = 0;
6 
7-- Re-enable autocommit
8SET autocommit = 1;

When autocommit = 1 (default), issuing START TRANSACTION temporarily disables autocommit until you explicitly issue COMMIT or ROLLBACK.

Transaction Lifecycle Syntax

1START TRANSACTION;
2-- OR: BEGIN;
3 
4-- Statement 1: Debit funds
5UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
6 
7-- Statement 2: Credit funds
8UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
9 
10-- If both succeed, make changes permanent:
11COMMIT;
12 
13-- If an error occurs, undo all statements:
14-- ROLLBACK;

Practical Walkthrough: Safe E-Commerce Checkout

Consider a checkout workflow that must deduct product stock and record the purchase order atomically:

1-- 1. Begin atomic transaction
2START TRANSACTION;
3 
4-- 2. Deduct inventory
5UPDATE inventory 
6SET stock_count = stock_count - 2 
7WHERE product_id = 501 AND stock_count >= 2;
8 
9-- 3. Create the order header
10INSERT INTO orders (customer_id, order_status, total_amount)
11VALUES (42, 'PAID', 199.98);
12 
13-- 4. Retrieve auto-generated order ID
14SET @new_order_id = LAST_INSERT_ID();
15 
16-- 5. Insert order item details
17INSERT INTO order_items (order_id, product_id, quantity, unit_price)
18VALUES (@new_order_id, 501, 2, 99.99);
19 
20-- 6. Commit all three updates together!
21COMMIT;

If any step fails (e.g., out-of-stock check fails or invalid customer ID), executing ROLLBACK; cleanly resets the inventory and prevents partial orders from being recorded.

Implicit Commits: DDL Statements You Must Avoid Inside Transactions!

A critical architectural pitfall in MySQL is that Data Definition Language (DDL) statements trigger an implicit commit!

If you issue any of the following statements inside an open transaction, MySQL automatically commits all preceding statements immediately:

CREATE TABLE, ALTER TABLE, DROP TABLE
CREATE INDEX, DROP INDEX
TRUNCATE TABLE
CREATE DATABASE, DROP DATABASE
LOCK TABLES, UNLOCK TABLES

Rule: Never execute DDL statements inside an active transaction expecting to be able to roll them back!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What does the COMMIT statement do?

Question 2

What happens to uncommitted changes if ROLLBACK is executed?

Question 3

In MySQL's default state, what is the value of @@autocommit?

Question 4

Which of the following statements triggers an implicit COMMIT in MySQL, preventing subsequent rollbacks?

Question 5

What is the synonym for START TRANSACTION in MySQL?

Next Lesson

Using SAVEPOINT & ROLLBACK TO SAVEPOINT

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
ACID Properties in Relational Databases	Using SAVEPOINT & ROLLBACK TO SAVEPOINT

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Using SAVEPOINT & ROLLBACK TO SAVEPOINT

Transaction Control: START TRANSACTION, COMMIT, ROLLBACK

Intermediate13 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Transaction Control: START TRANSACTION, COMMIT, ROLLBACK

To bundle multiple SQL statements into a single atomic unit, you must use Transaction Control Language (TCL) commands: START TRANSACTION, COMMIT, and ROLLBACK.

Understanding the AUTOCOMMIT Variable

In MySQL, the autocommit session variable dictates transaction boundaries:

1-- Check current autocommit status (1 = ON, 0 = OFF)
2SELECT @@autocommit;
3 
4-- Disable autocommit for current session
5SET autocommit = 0;
6 
7-- Re-enable autocommit
8SET autocommit = 1;

When autocommit = 1 (default), issuing START TRANSACTION temporarily disables autocommit until you explicitly issue COMMIT or ROLLBACK.

Transaction Lifecycle Syntax

1START TRANSACTION;
2-- OR: BEGIN;
3 
4-- Statement 1: Debit funds
5UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
6 
7-- Statement 2: Credit funds
8UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
9 
10-- If both succeed, make changes permanent:
11COMMIT;
12 
13-- If an error occurs, undo all statements:
14-- ROLLBACK;

Practical Walkthrough: Safe E-Commerce Checkout

Consider a checkout workflow that must deduct product stock and record the purchase order atomically:

1-- 1. Begin atomic transaction
2START TRANSACTION;
3 
4-- 2. Deduct inventory
5UPDATE inventory 
6SET stock_count = stock_count - 2 
7WHERE product_id = 501 AND stock_count >= 2;
8 
9-- 3. Create the order header
10INSERT INTO orders (customer_id, order_status, total_amount)
11VALUES (42, 'PAID', 199.98);
12 
13-- 4. Retrieve auto-generated order ID
14SET @new_order_id = LAST_INSERT_ID();
15 
16-- 5. Insert order item details
17INSERT INTO order_items (order_id, product_id, quantity, unit_price)
18VALUES (@new_order_id, 501, 2, 99.99);
19 
20-- 6. Commit all three updates together!
21COMMIT;

If any step fails (e.g., out-of-stock check fails or invalid customer ID), executing ROLLBACK; cleanly resets the inventory and prevents partial orders from being recorded.

Implicit Commits: DDL Statements You Must Avoid Inside Transactions!

A critical architectural pitfall in MySQL is that Data Definition Language (DDL) statements trigger an implicit commit!

If you issue any of the following statements inside an open transaction, MySQL automatically commits all preceding statements immediately:

CREATE TABLE, ALTER TABLE, DROP TABLE
CREATE INDEX, DROP INDEX
TRUNCATE TABLE
CREATE DATABASE, DROP DATABASE
LOCK TABLES, UNLOCK TABLES

Rule: Never execute DDL statements inside an active transaction expecting to be able to roll them back!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What does the COMMIT statement do?

Question 2

What happens to uncommitted changes if ROLLBACK is executed?

Question 3

In MySQL's default state, what is the value of @@autocommit?

Question 4

Which of the following statements triggers an implicit COMMIT in MySQL, preventing subsequent rollbacks?

Question 5

What is the synonym for START TRANSACTION in MySQL?

Next Lesson

Using SAVEPOINT & ROLLBACK TO SAVEPOINT

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
ACID Properties in Relational Databases	Using SAVEPOINT & ROLLBACK TO SAVEPOINT

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next