Transaction Management in MySQL

MySQL Tutorials - Herong's Tutorial Examples - Version 4.20, by Dr. Herong Yang

MySQL Tutorials - Herong's Tutorial Examples

∟Transaction Management and Isolation Levels

∟Transaction Management in MySQL

This section provides tutorial examples on how MySQL supports transaction management with the InnoDB and BDB storage engine.

MySQL support of transaction management follows the following rules:

Only two storage engines support transaction management: InnoDB and BDB.
The default storage engine, MyISAM, doesn't support transaction management.
To force a table to use a non-default storage engine, you must specify the engine name in the "create table" statement.

Statements related to transaction management:

SET AUTOCOMMIT = 0 | 1;
START TRANSACTOIN;
COMMIT;
ROLLBACK;

Note that:

SET AUTOCOMMIT = 1 - Turns on the auto-commit option. It also commits and terminates the current transaction.
SET AUTOCOMMIT = 0 - Turns off the auto-commit option. It also starts a new transaction
By default, auto-commit option is turned on when a new session is established.
COMMIT - Commits the current transaction.
ROLLBACK - Rolls back the current transaction.
START TRANSACTION - Commits the current transaction and starts a new transaction.

Here is a simple test code on the default transaction:

-- Rollback.sql
-- Copyright (c) 2015, HerongYang.com, All Rights Reserved.
--
SET AUTOCOMMIT = 0;
USE test;
DROP TABLE IF EXISTS User;
CREATE TABLE User (Login VARCHAR(8), Password CHAR(8)) ENGINE=InnoDB;
INSERT INTO User VALUES ('bill','83BF9598');
COMMIT;
INSERT INTO User VALUES ('jack','8IS3KOXW');
ROLLBACK;
INSERT INTO User VALUES ('john','FC93846F');
COMMIT;
INSERT INTO User VALUES ('mike','86D47E70');

Run Rollback.sql on MySQL 5.7 server, you will get:

C:\herong>set "MySQL=\Program Files\MySQL\MySQL Server 5.7"
C:\herong>"%MySQL%\bin\mysql" --user=root --password=TopSecret test 
   < Rollback.sql

Now check the table again in a new session:

C:\herong>"%MySQL%\bin\mysql" --user=root --password=TopSecret test
mysql> SELECT * FROM user;
+-------+----------+
| Login | Password |
+-------+----------+
| bill  | 83BF9598 |
| john  | FC93846F |
+-------+----------+

Observe that:

How InnoDB is specified as the storage engine for the table.
How to turn off the auto-commit flag.
How uncommitted changes can be rolled back.
Uncommitted changes are rolled back at the end of the session.

Here is another simple test code on non-default transactions:

-- Transaction.sql
-- Copyright (c) 2015, HerongYang.com, All Rights Reserved.
--
SET AUTOCOMMIT = 1;
DROP TABLE IF EXISTS User;
CREATE TABLE User (Login VARCHAR(8), Password CHAR(8)) ENGINE=InnoDB;
START TRANSACTION;
INSERT INTO User VALUES ('bill','83BF9598');
COMMIT;
INSERT INTO User VALUES ('jack','8IS3KOXW');
ROLLBACK;
INSERT INTO User VALUES ('john','FC93846F');
COMMIT;
INSERT INTO User VALUES ('mike','86D47E70');
START TRANSACTION;
INSERT INTO User VALUES ('user1','pass1');
START TRANSACTION;
INSERT INTO User VALUES ('user2','pass2');
START TRANSACTION;
INSERT INTO User VALUES ('user3','pass3');
ROLLBACK;
INSERT INTO User VALUES ('user4','pass4');
ROLLBACK;
INSERT INTO User VALUES ('user5','pass5');
COMMIT;
SELECT 'User table:' AS '---';
SELECT * FROM User;

Output:

---
User table:
Login   Password
bill    83BF9598
jack    8IS3KOXW
john    FC93846F
mike    86D47E70
user1   pass1
user2   pass2
user4   pass4
user5   pass5

Observe that:

"jack" is in the table because the previous commit statement ends the non-default transaction. The insert statement on "jack" is automatically committed.
"user1" and "user2" are in the table because transactions can not be nested. A "start transaction" statement commits the changes and ends the current transaction.
"user4" is in the table because the previous rollback statement ends the non-default transaction.

Last update: 2015.

Table of Contents

About This Book

Introduction of SQL

MySQL Introduction and Installation

Introduction of MySQL Programs

Perl Programs and MySQL Servers

PHP Programs and MySQL Servers

Java Programs and MySQL Servers

Datatypes and Data Literals

Operations and Expressions

Character Strings and Bit Strings