Types of Transactions in MySQL with Practical Examples

The article, inspired by a recent Oracle‑to‑MySQL migration, explains the different transaction categories supported by MySQL.

Classification

1. Ordinary transaction – started with BEGIN or START TRANSACTION and ended with COMMIT or ROLLBACK , optionally using SAVEPOINT .

2. Chain transaction – controlled by the completion_type parameter; a COMMIT WORK makes the following statement part of the same atomic block.

3. Nested transaction – multiple BEGIN/COMMIT blocks with a parent‑child relationship; MySQL does not truly support nested transactions, so a second BEGIN is implicitly converted to COMMIT; BEGIN .

4. Autonomous transaction – not natively supported, but can be simulated by using a non‑transactional engine (e.g., MyISAM) for logging.

Examples

1. Ordinary transaction

truncate c1;

BEGIN;

INSERT INTO c1 VALUES (1,20,NOW());

SAVEPOINT s1;

INSERT INTO c1 VALUES (2,30,NOW());

SAVEPOINT s2;

INSERT INTO c1 VALUES (3,40,NOW());

ROLLBACK TO SAVEPOINT s2;

COMMIT;

SELECT * FROM c1;

2. Chain transaction

TRUNCATE TABLE c1;

SET completion_type=1;

INSERT INTO c1 VALUES (4,50,NOW());

INSERT INTO c1 VALUES (5,60,NOW());

-- sql 1

COMMIT WORK;

-- sql 2

INSERT INTO c1 VALUES (6,70,NOW());

ROLLBACK;

SELECT * FROM c1;

3. Nested transaction (simulated)

TRUNCATE TABLE c1;

BEGIN;

INSERT INTO c1 VALUES (7,80,NOW());

BEGIN;   -- becomes COMMIT; BEGIN;

INSERT INTO c1 VALUES (8,90,NOW());

ROLLBACK;

SELECT * FROM c1;

4. Autonomous transaction (simulation)

CREATE TABLE log(err_msg VARCHAR(200)) ENGINE MyISAM;

BEGIN;

INSERT INTO t1 VALUES (100);

INSERT INTO log VALUES ('This record should not be inserted');

ROLLBACK;

SELECT * FROM log;

Conclusion: The article provides a concise overview of MySQL transaction categories and demonstrates typical usage scenarios with clear SQL examples, helping readers understand how to apply each transaction type in real‑world applications.