autocommit

Background

A transaction is a set of SQL statements that execute as a unit. Either all the statements execute successfully, or none of them have any effect. This is achieved by using the commit and rollback capabilities of MySQL. If all of the statements in the transaction succeed, changes to the record are committed to the database. If an error occurs during the transaction, the changes are rolled back and canceled.

Transactional processing provides stronger guarantees about the outcome of database operations, but requires more overhead in CPU cycles, memory, and disk space. InnoDB is a transaction-safe storage engine.

By default, MySQL runs in autocommit mode, which means that changes made by individual statements are committed to the database immediately to make them permanent. By default, each statement is its own transaction. To perform transactions explicitly, disable autocommit mode and then tell MySQL when to commit or roll back changes.

Objectives

• Create tables with the InnoDB storage engine;
• Run a transaction with autocommit disabled;
• Run a transaction with autocommit enabled;
• Observe the different results.

Steps

1. Create a table
• Create a table to use.

mysql>create table bad(i int) engine = ferrari;

Did you get an error message?

• Look for possible warning messages.

mysql>show warnings;

2. Examine the current SQL mode
• Retrieve the current session sql_mode value.

mysql>SELECT @@session.sql_mode;

• Prevent future warning message.

mysql>SET SESSION sql_mode='NO_ENGINE_SUBSTITUTION';

3. Set the autocommit variable
• By default, autocommit is enabled in MySQL. Find its current value.

  mysql>SELECT @@autocommit;

• If necessary, enable autocommit.

mysql>SET autocommit=1;

4. Start a transaction
• Create a table to use.

mysql>CREATE TABLE t1 (name CHAR(20),
->UNIQUE (name)) ENGINE = InnoDB;

• Issue a START TRANSACTION statement to suspend autocommit mode.

mysql> START TRANSACTION;

• Add two rows to the table.

mysql> INSERT INTO t1 SET name = 'William';

mysql> INSERT INTO t1 SET name = 'Wallace';

• Commit the transaction.

mysql> COMMIT;

• Examine the table.

mysql> SELECT * FROM t1;

• The table should look like this.

+---------+
| name    |
+---------+
| Wallace |
| William |
+---------+

5. Roll back a transaction
• If an error occurs during a transaction, cancel it with ROLLBACK.
• Initiate a transaction.

mysql> START TRANSACTION;

• Add two rows to the table.

mysql> INSERT INTO t1 SET name = 'Gromit';

mysql> INSERT INTO t1 SET name = 'Wallace';

Do you get an error regarding a duplicate entry?

• Roll back transaction.

mysql> ROLLBACK;

• Examine the table.

mysql> SELECT * FROM t1;

• The table should look like this.

+---------+
| name    |
+---------+
| Wallace |
| William |
+---------+

6. Start a different transaction
• Create another table to use.

mysql>CREATE TABLE t2 (name CHAR(20),
->UNIQUE (name)) ENGINE = InnoDB;

• Disable autocommit mode.

mysql> SET autocommit = 0;

• Add two rows to the table.

mysql> INSERT INTO t2 SET name = 'William';

mysql> INSERT INTO t2 SET name = 'Wallace';

• Commit the transaction.

mysql> COMMIT;

• Examine the table.

mysql> SELECT * FROM t2;

• The table should look like this.

+---------+
| name    |
+---------+
| Wallace |
| William |
+---------+

7. Roll back a transaction
• If an error occurs during a transaction, cancel it with ROLLBACK.
• Initiate a transaction.

mysql> START TRANSACTION;

• Add two rows to the table.

mysql> INSERT INTO t2 SET name = 'Gromit';

mysql> INSERT INTO t2 SET name = 'Wallace';

Do you get an error regarding a duplicate entry?

• Roll back transaction.

mysql> ROLLBACK;

• Examine the table.

mysql> SELECT * FROM t2;

• The table should look like this.

+---------+
| name    |
+---------+
| Wallace |
| William |
+---------+

8. Set the autocommit variable
• Enable autocommit.

mysql>SET autocommit=1;

9. Start a different transaction
• Create another table to use.

mysql>CREATE TABLE t3 (name CHAR(20),
->UNIQUE (name)) ENGINE = InnoDB;

• Add two rows to the table.

mysql> INSERT INTO t3 SET name = 'William';

mysql> INSERT INTO t3 SET name = 'Wallace';

• Examine the table.

mysql> SELECT * FROM t3;

• The table should look like this.

+---------+
| name    |
+---------+
| Wallace |
| William |
+---------+

10. Attempt to roll back
• Add two more rows to the table.

mysql> INSERT INTO t3 SET name = 'Gromit';

mysql> INSERT INTO t3 SET name = 'Wallace';

Do you get an error regarding a duplicate entry?

• Roll back transaction.

mysql> ROLLBACK;

• Examine the table.

mysql> SELECT * FROM t3;

What does the table contain?

11. Exit
• Use QUIT to exit out of the server

  mysql>QUIT

Congratulations! You have successfully used a MySQL transaction-safe storage engine.