MySQL - COMMIT Statement

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 sender’s 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
Advertisements