Definition of a transaction
A transaction is an atomic unit of database operations against the data in one or more databases. The effects of all the SQL statements in a transaction can be either all committed to the database or all rolled back.
MySQL supports several storage engines. The InnoDB is fully ACID compliant. The ACID stands for Atomicity, Consistency, Isolation and Durability. Reliable transactions must support all these four properties.
Operations within a transaction must be atomic. This means, that either all operations succeed or fail. This is all or nothing rule. The consistency property ensures that the database is in a consistent state after the transaction is finished. The data is valid and there are no half-finished records. For example there are no customers left with no payment records or there are no payment records without customers. Isolation is the requirement that other operations cannot access data that has been modified during a transaction that has not yet completed. The question of isolation occurs in case of concurrent transactions. Without isolation, the data may end up in inconsistent state. Durability is the ability of the database system to recover the committed transaction updates against any kind of system failure.
The default transaction isolation level for MySQL is repeatable read.
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
The current isolation level is stored in the tx_isolation server variable.
mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
mysql> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE |
+----------------+
We can change the isolation level with the
SET TRANSACTION ISOLATION LEVEL statement.Autocommit
MySQL also automatically commits statements that are not part of a transaction. The results of any UPDATE or INSERT statement not preceded with a START will immediately be visible to all connections.
mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
The autocommit variable is set by default.
mysql> SET autocommit=0;
mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
The autocommit can be turned off.
Now we are going to demonstrate the autocommint variable.
mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
CREATE TABLE Test(Num INTEGER NOT NULL) engine=InnoDB;
The autocommit is set. We create a simple Test table with InnoDB storage engine, which supports transactions.
mysql> INSERT INTO Test VALUES (1), (2), (3);
mysql> SELECT * FROM Test;
+-----+
| Num |
+-----+
| 1 |
| 2 |
| 3 |
+-----+
We insert three rows into the column of the table. The values are immediately committed.
mysql> SET autocommit=0;
mysql> INSERT INTO Test VALUES (4), (5);
mysql> SELECT * FROM Test;
+-----+
| Num |
+-----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+-----+
Now we set the autocommit variable to false. We insert two values and select all data from the table. We have now 5 rows in the table.
mysql> ROLLBACK;
mysql> SELECT * FROM Test;
+-----+
| Num |
+-----+
| 1 |
| 2 |
| 3 |
+-----+
However, the data is not permanently written to the table. With the
ROLLBACK statement, we take them back.mysql> INSERT INTO Test VALUES (4), (5);
mysql> COMMIT;
mysql> ROLLBACK;
mysql> SELECT * FROM Test;
+-----+
| Num |
+-----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+-----+
Now we insert value 4, 5 again. This time, the rows are committed with the
COMMIT statement. Subsequent rollback statement has no effect.Starting transactions
With autocommit enabled, each single SQL statement is wrapped automatically in its own transaction. To start our own transaction, we issue the START TRANSACTION statement. The transaction is later finished with the COMMIT or ROLLBACK statements. Multiple statements may be issued in the body of the transaction. All are either committed or rolled back as one unit.
mysql> TRUNCATE Test;
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT * FROM Test;
Empty set (0.00 sec)
We will work with the same Test table. We truncate the data in the table.
mysql> START TRANSACTION;
mysql> INSERT INTO Test VALUES (1), (2);
mysql> INSERT INTO Test VALUES (3), (4);
mysql> SELECT * FROM Test;
+-----+
| Num |
+-----+
| 1 |
| 2 |
| 3 |
| 4 |
+-----+
In the above code, we start a transaction and insert four rows into the table. The values are not yet committed. From the current connection the rows are visible.
$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 65
Server version: 5.1.41-3ubuntu12.9 (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT * FROM mydb.Test;
Empty set (0.00 sec)
However, from a different connection, the Test table is empty. We launch a new instance of a mysql client program. This is a different connection to the MySQL database. From this connection, the values are not yet visible.
mysql> COMMIT;
Finally, the
COMMIT statement commits the data to the table. The rows are visible from both connections.We start another transaction. This time the data will be rolled back.
mysql> START TRANSACTION;
mysql> INSERT INTO Test VALUES (5), (6);
mysql> INSERT INTO Test VALUES (7), (8);
mysql> ROLLBACK;
mysql> SELECT * FROM Test;
+-----+
| Num |
+-----+
| 1 |
| 2 |
| 3 |
| 4 |
+-----+
In the above SQL code, we start a new transaction. We insert four values into the Test table. We roll the changes back with the
ROLLBACK statement. Subsequent select from the table shows that the data was not committed to the table.
