Turn off Auto commit in MySql

What is auto commit in mysql: 

When we run DML query in mysql for example we have insert any value into table or update value in table, after each DML statement mysql auto commits the changes by default, that means we cannot rollback the changes we have made. This is called auto commit functionality.

To prevent that we can stop this "auto commit" in mysql in following ways:

Permanent Turing off of auto commit:

Open /etc/mysql/my.cnf file in linux environment using sudo privileges as:

$> sudo gedit /etc/mysql/my.cnf 

and type the following lines in bottom of the file and save it.

[client]

init-command='set autocommit=0'

then restart mysql using following command:

$> sudo service mysql restart

Session wise Turing off auto commit:

type the below command in mysql promt:

mysql > set autocommit=0;

And then if you do below operation then you can realize how changes are not getting persistent:

mysql> select * from student ;

+----+------+

| id | name |

+----+------+

|  1 | Amit |

|  2 | Smit |

|  3 | Lmit |

|  4 | Dmit |

+----+------+

mysql> insert into student values(5,'Cmit');

mysql> rollback;

mysql> select * from student ;

+----+------+

| id | name |

+----+------+

|  1 | Amit |

|  2 | Smit |

|  3 | Lmit |

|  4 | Dmit |

+----+------+

But if you run the following sql:

mysql> select * from student ;

+----+------+

| id | name |

+----+------+

|  1 | Amit |

|  2 | Smit |

|  3 | Lmit |

|  4 | Dmit |

+----+------+

mysql> insert into student values(5,'Cmit');

mysql> commit;

mysql> select * from student ;

+----+------+

| id | name |

+----+------+

|  1 | Amit |

|  2 | Smit |

|  3 | Lmit |

|  4 | Dmit |

|  5 | Cmit |

+----+------+