MySQL Transactions: Implicit and Explicit

A Transaction in SQL is a single logical unit of work.
Transactions can make multiple changes to the database like create tables, delete records, update records, etc in the same database transaction.
The transaction is successful when all the operations in it complete and are committed which means that the changes are committed to memory i.e. made permanent.
A transaction fails if any of the operations in it fail and cause a rollback. A rollback means that the changes are discarded and are not permanent.
Commit or Rollback is either implicit or explicit.
In MySQL, transactions with DDL instructions like CREATE, ALTER, RENAME, DROP and TRUNCATE are implicitly committed. The changes once made are permanent and users cannot control this.
Users can switch off auto-commit for DML instructions like INSERT, UPDATE, DELETE and SELECT.
Transactions follow the ACID properties.

MySQL defines the syntax of a transaction as follows:

START TRANSACTION
[transaction_characteristic [, transaction_characteristic] ...]
transaction_characteristic: {
    WITH CONSISTENT SNAPSHOT
  | READ WRITE
  | READ ONLY
}
BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}

MySQL Implicit Commit

In the case of Data Definition Language commands (DDL) i.e Create, Alter, Rename, etc, MySQL performs an implicit commit. This means that even though a user starts a transaction and sets auto_commit to OFF, the DDL statements in the transaction are auto-committed. The user cannot control these. As an example

START TRANSACTION;
SET AUTOCOMMIT = OFF;
 CREATE TABLE CUSTOMERS(
    CUSTOMER_ID INT AUTO_INCREMENT PRIMARY KEY,
    FIRST_NAME VARCHAR(20) NOT NULL,
    LAST_NAME VARCHAR(20),
    CUSTOMER_EMAIL VARCHAR(20),
    CUSTOMER_PHONE INTEGER,
    ENROLLMENT_DATE DATE DEFAULT (CURRENT_DATE())
 );
 rollback;

Despite the Rollback, the table is still created.

MySQL Implicit Rollback

If there is an error during the command execution, the transaction will be rolled back. We cannot control this unless we handle it using Exceptions. An example of implicit rollback is as follows

START TRANSACTION;
SET AUTOCOMMIT = OFF;
CREATE TABLE ACCOUNTS(
  ACCOUNT_ID INT AUTO_INCREMENT PRIMARY KEY,
  ACCOUNT_TYPE INT,
  CUSTOMER_ID INT,
  ACCOUNT_NUMBER VARCHAR(50),
  ACCOUNT_NAME VARCHAR(50),
  FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(CUSTOMER_ID) ON DELETE CASCADE,
  FOREIGN KEY (ACCOUNT_TYPE) REFERENCES ACCOUNT_TYPES(ACCOUNT_TYPE_ID) ON DELETE CASCADE
);

COMMIT;

MySQL Explicit Commit (Update command)

DML i.e Data Manipulation statements like Update, delete, Insert, and select need to explicitly committed or rolled back. MySQL by default set the auto-commit option to ON and hence if we do not start a transaction, then all DML statements are also auto committed. However, when we specify the keywords start transaction we need to specify whether we want to commit or rollback the transaction.

Start transaction;
set autocommit=off;

insert into customers
 values
(default,'John','Doe','[email protected]',3112221816,default),
(default,'John','Smith','[email protected]',3111972097,default);
commit;

A mixture of Implicit and explicit commits in MySQL

When DML and DDL statements are done together in a transaction, the DDL statements are implicitly committed and the DML statements follow the commit or rollback mentioned at the end of the transaction.

start transaction;
set autocommit = off;

alter table customers modify CUSTOMER_EMAIL VARCHAR(70);
insert into customers
 values (default,'Thorin','Oakenshield','[email protected]',NULL,default);
rollback;

Nested transactions in MySQL

Nested transactions are not allowed in MySQL. If we start a transaction within another, all the statements executed in the first transaction till that point are simply committed irrespective of whether they are DDL or DML statements.

Nested transactions with 2 DML statements

This is an example of 2 DML statements with explicit rollbacks to end both transactions.

START TRANSACTION;
SET AUTOCOMMIT = OFF;
INSERT INTO CUSTOMERS
 VALUES
(DEFAULT,'THORIN','OAKENSHIELD','[email protected]',NULL,DEFAULT),
(DEFAULT,'BILBO','BAGGINS','[email protected]',1111111111,DEFAULT),
(DEFAULT,'ARWEN','NOLDOR','[email protected]',1111111111,DEFAULT);

START TRANSACTION;
UPDATE CUSTOMERS
SET CUSTOMER_EMAIL = '[email protected]',
    CUSTOMER_PHONE = 1239087653
WHERE FIRST_NAME = 'ARWEN';
ROLLBACK;
ROLLBACK;

Nested transactions with error in MySQL

This is an example of nested transactions with the inner transaction failing.

start transaction;
set autocommit = OFF;
update customers
set customer_email = '[email protected]',
    customer_phone = 1239087653
where first_name = 'Arwen';
start transaction;
set autocommit = OFF;
delete * from customers;
commit;
rollback;

Savepoint in MySQL

Along with the commit and rollback statements, MySQL also supports Savepoints. The complete documentation is available here. Savepoints are especially useful in large scripts where we can place savepoints at certain points. We can rollback to savepoints in case of errors and not have to roll back the entire transaction. Rollback to a savepoint can be done only during a transaction and we can only roll back to the most recent savepoint.

insert into transaction_types
values
(1,'Debit'),
(2,'Credit'),
(3,'Remittance');

insert into account_types
values
(1,'Savings'),
(2,'Current'),
(3,'Loan'),
(4,'Flexi Deposit account');

savepoint first_savepoint;

insert into accounts
values
(1,2,3,'C0000111134789','ThorinCurrentAccount');
delete from customers where customer_id = 1;
rollback to first_savepoint;

insert into customer_type
values (default,5,'Savings Customer',NULL);
commit;

Release Savepoint in MySQL

Savepoint can be released after the transaction that needs them is completed. We can release savepoint only in the same session and not across the session. To release a savepoint

release savepoint first_savepoint;