Using Transactions In MySQL (Part 1) - Artificial Intelligence (
Page 7 of 8 )
By default, MySQL operates in what is known as "autocommit mode". Simply, this means that MySQL treats every single SQL command as a single-statement transaction, and internally issues a COMMIT after each query to save it to disk. If this is not what you require, you can turn this feature off, by setting the special AUTOCOMMIT variable to 0, as below:
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.03 sec)
Once this is done, you will need to explicitly issue a COMMIT after every command to have its modifications saved to disk. A failure to do so will result in all your changes automatically being rolled back when you exit the session.
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT * FROM users;
+----+------+------------------+
| id | name | pass |
+----+------+------------------+
| 3 | alan | 5af23f026beddb81 |
| 4 | john | 2ca0ede551581d29 |
+----+------+------------------+
2 rows in set (0.00 sec)
mysql> INSERT INTO users (name, pass) VALUES ('tim', PASSWORD('hoo'));
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO users (name, pass) VALUES ('jim',
mysql> PASSWORD('i283kh'));
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM users;
+----+------+------------------+
| id | name | pass |
+----+------+------------------+
| 3 | alan | 5af23f026beddb81 |
| 4 | john | 2ca0ede551581d29 |
| 14 | tim | 7ae94f60221f748f |
| 15 | jim | 342cc9873ccd6d02 |
+----+------+------------------+
4 rows in set (0.01 sec)
mysql> exit
-- reconnect --
mysql> SELECT * FROM users;
+----+------+------------------+
| id | name | pass |
+----+------+------------------+
| 3 | alan | 5af23f026beddb81 |
| 4 | john | 2ca0ede551581d29 |
+----+------+------------------+
2 rows in set (0.00 sec)
You can always restore the default setting by setting the AUTOCOMMIT variable back to 1, as below:
mysql> SET AUTOCOMMIT=1;
Query OK, 0 rows affected (0.03 sec)
Obviously, the AUTOCOMMIT variable has no impact on non-transactional table types like MyISAM; every change made to those tables is saved to disk immediately and cannot be rolled back.
