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;
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;
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;
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;
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;
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;
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;
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;
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.
You can download the full source code of this example here: SQL Commit and Rollback Example
