How To Switch between Autocommit-On and Autocommit-Off Modes in MySQL?

By default, your connection session will be in Autocommit-On mode, where every server executable statement will start a new transaction, and end the transaction when the execution ends. Changes will be committed. So consider this mode as single-statement autocommitted transaction mode.

If you don't like the default Autocommitt-On mode, you can use the "SET AUTOCOMMIT = 0" to switch to the Autocommitt-Off mode. Of course, you can switch back the Autocommitt-On mode with the "SET AUTOCOMMIT = 0" command. The following tutorial exercise shows some good examples:

>\mysql\bin\mysql -u dev -piyf fyi

mysql> -- You are in the default Autocommit-On mode now

mysql> SET AUTOCOMMIT = 0;
Query OK, 0 rows affected (0.03 sec)

mysql> -- You are in the Autocommit-Off mode now

mysql> SET AUTOCOMMIT = 0;
Query OK, 0 rows affected (0.03 sec)

mysql> -- You are back in the Autocommit-On mode now

⇒Transaction Management: Commit or Rollback in MySQL

⇒⇒MySQL Database Tutorials