MySQL - COMMIT Statement

Quiz

In general, transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.

A database transaction is the propagation of one or more changes as a single action on the database.

For example, if you consider the transfer of certain amount from one account to other as a single transaction. This basically contains three steps

Reading the money to be transferred.
Verifying whether the amount is available in the senders account.
Crediting money from the sender account.
Debiting money to the receiver account.

For the amount to be transferred all the above operations should be successfully completed. To make sure this happens you can use Transactions and treat all these steps as a single action.

MYSQL provides supports for transactions using the SET autocommit, START TRANSACTION, COMMIT, and ROLLBACK statements.

The COMMIT statement

The COMMIT statement saves all the modifications made in the current transaction since the last commit or the START TRANSACTION statement.

Syntax

Following is the syntax of the MySQL COMMIT statement −

COMMIT

Example

MySQL saves the changes done after the execution of each statement. To save changes automatically, set the autocommit option as shown below −

SET autocommit=0;

Assume we have created a table with name Players in MySQL database using CREATE statement as shown below −

CREATE TABLE Players( 
   ID INT,
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Date_Of_Birth date,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255),
   PRIMARY KEY (ID)
);

Now, we will insert 7 records in Players table using INSERT statements −

Insert into Players values
(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India'),
(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica'),
(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka'),
(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India'),
(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India'),
(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India'),
(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

Following query saves the changes −

COMMIT;

Now, let us delete the last record from the table −

Delete from Players where id = 7;

You can retrieve the contents of the above create table using the SELECT statement as shown below −

SELECT * FROM Players;

Output

The above query produces the following output −

ID	First_Name	Last_Name	Date_Of_Birth	Place_Of_Birth	Country
1	Shikhar	Dhawan	1981-12-05	Delhi	India
2	Jonathan	Trott	1981-04-22	CapeTown	SouthAfrica
3	Kumara	Sangakkara	1977-10-27	Matale	Srilanka
4	Virat	Kohli	1988-11-05	Delhi	India
5	Rohit	Sharma	1987-04-30	Nagpur	India
6	Ravindra	Jadeja	1988-12-06	Nagpur	India

Following statement reverts the changes after the last commit.

ROLLBACK;

All the changes done past the last commit will be reverted if we rollback a transaction. Since we have deleted the 7th record after setting the commit, this delete is reverted at the time of roll back.

SELECT * FROM Players;

Output

Following is the output of the above query −

ID	First_Name	Last_Name	Date_Of_Birth	Place_Of_Birth	Country
1	Shikhar	Dhawan	1981-12-05	Delhi	India
2	Jonathan	Trott	1981-04-22	CapeTown	SouthAfrica
3	Kumara	Sangakkara	1977-10-27	Matale	Srilanka
4	Virat	Kohli	1988-11-05	Delhi	India
5	Rohit	Sharma	1987-04-30	Nagpur	India
6	Ravindra	Jadeja	1988-12-06	Nagpur	India
7	James	Anderson	1982-06-30	Burnley	England

Print Page