MySQL - SET autocommit Statement

---

---

SET autocommit Statement

The COMMIT statement saves all the modifications made in the current. If you commit a database, it saves all the changes that have been done till that particular point. By default, the MySQL database commits/saves the changes done automatically.

You can turn off/on the auto-commit using the SET autocommit statement.

Syntax

Following is the syntax of the SET autocommit statement −

SET autocommit=0;

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 in MySQL with name EMPLOYEES as shown below −

CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1);

Following query saves the changes −

COMMIT;

Let us insert 4 records in to it using INSERT statements as −

INSERT INTO EMPLOYEE VALUES
('Krishna', 'Sharma', 19, 'M', 2000),
('Raj', 'Kandukuri', 20, 'M', 7000),
('Ramya', 'Ramapriya', 25, 'F', 5000),
('Mac', 'Mohan', 26, 'M', 2000); 

Now, update the age of the employees by one year −

UPDATE EMPLOYEE SET AGE = AGE + 1;
Rows matched: 3 Changed: 3 Warnings: 0

If you retrieve the contents of the table, you can see the updated values as −

select * from EMPLOYEE;

Output

Following is the output of the above program −

FIRST_NAME	LAST_NAME	AGE	SEX	INCOME
Krishna	Sharma	20	M	2000
Raj	Kandukuri	21	M	7000
Ramya	Ramapriya	26	F	5000
Mac	Mohan	27	M	2000

Following statement reverts the changes after the last commit.

ROLLBACK;

Since we have executed the COMMIT statement before inserting records if you verify the contents of the EMPLOYEE table, you will get an empty set as follow −

SELECT * FROM EMPLOYEE;
Empty set (0.06 sec)