Commit, Rollback and AutoCommit in MySQL

Check Autocommit Value in MySQL

SELECT @@autocommit;

Enable or Disable autocommit in MySQL

--To make autcommit false
SET AUTOCOMMIT=false;
--or
SET AUTOCOMMIT=0;

--To make autcommit true
SET AUTOCOMMIT=true;
--or
SET AUTOCOMMIT=1;

Commit:

If autocommit is false, then transaction is not commit until you fire manually commit command. It will show data without commit data in same connection session.

Session 1:
mysql> create table test1 (id int);
Query OK, 0 rows affected (0.21 sec)
mysql> SET autocommit=0;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test1 values(1);
Query OK, 1 row affected (0.02 sec)

Session 2:
mysql> select * from test1;
Empty set (0.00 sec)

Session 1:
mysql> commit;
Query OK, 0 rows affected (0.02 sec)

Session 2:
mysql> select * from test1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Rollback:

If anything went wrong in your query execution, ROLLBACK in used to revert the changes.

mysql> insert into test1 values (4);
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.