Understanding Database Transactions and MySQL Transaction Control

Database transactions are a sequence of operations that must be executed completely or not at all, providing the ACID properties—Atomicity, Consistency, Isolation, and Durability—illustrated with a simple fund‑transfer example.

In MySQL, only the InnoDB storage engine supports transactions; it is the default engine in MySQL 5.7. The server runs in AUTOCOMMIT mode, treating each individual SQL statement as a transaction unless explicitly disabled.

To check the current autocommit setting you can run:

SHOW VARIABLES LIKE 'AUTOCOMMIT';

To turn off autocommit, set the variable to 0:

SET AUTOCOMMIT=0;

When autocommit is disabled, you must manually start, commit, or roll back transactions.

MySQL provides several transaction control statements:

BEGIN or START TRANSACTION – start a new transaction

COMMIT – make all changes permanent

ROLLBACK – undo all changes

SAVEPOINT identifier – create a named savepoint

ROLLBACK TO SAVEPOINT – revert to a specific savepoint

RELEASE SAVEPOINT – delete a savepoint

SET TRANSACTION – define transaction characteristics such as isolation level

A practical example demonstrates creating a simple table, inserting data, and using a savepoint to roll back part of a transaction:

CREATE TABLE `t_employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_employee` (`name`) VALUES ('Tom');
INSERT INTO `t_employee` (`name`) VALUES ('John');
INSERT INTO `t_employee` (`name`) VALUES ('Hans');

START TRANSACTION;
UPDATE t_employee SET name='full stack' WHERE id=1;
SELECT * FROM t_employee WHERE id=1;
SAVEPOINT s1;
UPDATE t_employee SET name='full stack new' WHERE id=1;
SELECT * FROM t_employee WHERE id=1;
ROLLBACK TO s1;
SELECT * FROM t_employee WHERE id=1;

MySQL supports four transaction isolation levels to control how concurrent transactions interact:

Read Uncommitted – changes are visible to other transactions even before commit.

Read Committed – changes become visible only after the transaction commits.

Repeatable Read – ensures that repeated reads within the same transaction return the same result.

Serializable – the strictest level, forcing transactions to execute sequentially.

Further articles will delve deeper into the implementation details of these isolation levels.