sql

SQL Commit and Rollback Example

1. Introduction

In this article, we will look at the Commit and Rollback commands. SQL transactions use Commit and Rollback commands. We will see how to use the Commit and Rollback commands in MySQL RDBMS.

2. What is a Transaction in SQL?

  • 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.
  • When all the operations inside a transaction complete successfully, MySQL commits the changes to memory and marks the transaction as complete.
  • A transaction fails if any of the operations in it fail and cause a rollback. A rollback discards changes, they are not permanent.
  • Commit or Rollback is either implicit or explicit.
  • In MySQL implicitly commits transactions with DDL instructions like CREATE, ALTER, RENAME, DROP and TRUNCATE. 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.

3. What are ACID properties?

All database systems follow the ACID properties. ACID is the acronym for Atomicity, Consistency, Isolation, and Durability. These properties together ensure that the operations on the data in a database are processed correctly and the database has consistent and reliable data.

All transactions in a database are atomic in nature.

3.1 Atomicity

Atomicity is equivalent to the “All or nothing” rule. MySQL transactions are atomic transactions i.e. the transaction processes completely or MYSQL discards the entire transaction. There is no midway i.e. transactions do not occur partially. It involves two operations.

  • Abort: If a transaction aborts, changes made to the database are discarded and hence not visible i.e. ROLLBACK.
  • Commit: If a transaction commits, changes made are permanent and visible i.e. COMMIT.

4. Syntax

As per the My SQL documentation the syntax of a transaction is 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} 

The keywords start transaction disable autocommit. Otherwise, MYSQL autocommits by default.

5. Setup for example

Forgoing through the examples related to the Commit and rollback transactions, we will consider a new database called “banking_database”. The banking_database has the following tables: customers, customer_type, accounts, account_types, transactions, and transaction_types.

For running the queries, we will use multiple sessions of the MySQL Command line client whose documentation is available here.

6. Examples with Commit and Rollback

Here, we will see examples of how transaction management works in MySQL. As mentioned earlier, commit and rollback can be implicit or explicit.

6.1 Implicit Commit

In the case of Data Definition Language commands 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, My SQL auto commits the DDL statements in the transaction. Hence, the user cannot control this. For 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;
SQL Commit and Rollback - Implicit Commit
Implicit Commit

Despite the Rollback, MySQL will create the table.

6.2 Implicit Rollback

If there is an error during commands, MySQL rolls back the transaction. 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;
SQL Commit and Rollback - Implicit Rollback
Implicit Rollback

6.3 Explicit Commit, DML commands

DML i.e Data Manipulation statements like Update, delete , Insert and select need to explicitly committed or rolled back. MySQL by default auto commits and hence if we do not start a transaction, then MYSQL also auto commits all DML statements. However, when we specify the keywords start transaction we need to specify whether we want to commit or rollback the transaction. Given below is an example of Update statement. The same concept applies for Insert, delete, and select statements

Start transaction;
set autocommit=off;

insert into customers
 values
(default,'John','Doe','john.doe@abc.com',3112221816,default),
(default,'John','Smith','john.smith@axz.com',3111972097,default);

commit;
SQL Commit and Rollback - Explicit Commit
Explicit Commit

6.4 Mixture of Implicit and explicit commits

When we have DML and DDL statements together in a transaction, MYSQL implicitly commits the DDL statements 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','thorin.oakenshield@abc.com',NULL,default);
 
rollback;
SQL Commit and Rollback - Mixture of DDL and DML statements in a transaction
Mixture of DDL and DML statements in a transaction

6.5 Nested transactions

MYSQL does not allow Nested transactions. If we start a transaction within another, MySQL auto commits all the statements executed in the first transaction till that point irrespective of whether they are DDL or DML statements.

6.5.1 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','THORIN.OAKENSHIELD@ABC.COM',NULL,DEFAULT),
(DEFAULT,'BILBO','BAGGINS','BILBO.BAGGINS@AXZ.COM',1111111111,DEFAULT),
(DEFAULT,'ARWEN','NOLDOR','BILBO.BAGGINS@SDF.COM',1111111111,DEFAULT);
 
START TRANSACTION; 
UPDATE CUSTOMERS 
SET CUSTOMER_EMAIL = 'ARWEN.NOLDOR@GMAIL.COM',
    CUSTOMER_PHONE = 1239087653
WHERE FIRST_NAME = 'ARWEN';

ROLLBACK;
ROLLBACK;
Nested transactions
Nested transactions

6.5.2 Nested transactions with error

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

start transaction;
set autocommit = OFF;
update customers 
set customer_email = 'arwen.noldor@gmail.com',
    customer_phone = 1239087653
where first_name = 'Arwen';
start transaction;
set autocommit = OFF; 
delete * from customers;
commit;
rollback;
Nested transactions with error
Nested transactions with error

6.6 Savepoint

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 save point.

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;
Savepoint
Savepoint

6.7 Release Savepoint

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;
release savepoint
release savepoint

7. Summary

In the article we saw syntaxes related to the Commit and Rollback transactions in MySQL. The Commit and Rollback transactions are a part of transaction management in MySQL and uphold the Atomicity property of the database.

8. Download the Source Code

This was an example of the SQL Commit and Rollback Example using MySQL RDBMS.

Download
You can download the full source code of this example here: SQL Commit and Rollback Example

Reshma Sathe

I am a recent Master of Computer Science degree graduate from the University Of Illinois at Urbana-Champaign.I have previously worked as a Software Engineer with projects ranging from production support to programming and software engineering.I am currently working on self-driven projects in Java, Python and Angular and also exploring other frontend and backend technologies.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
Back to top button