Rules of the Game - MySQL

It is important to note that MySQL does not permit nested transactions. As in the example below, if you start a new transaction without ending the previous one with a COMMIT or ROLLBACK, MySQL will automatically commit the previous transaction's data to disk before beginning a new transaction.

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO users (name, pass) VALUES ('john', PASSWORD('john'));
Query OK, 1 row affected (0.57 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM users;
+----+------+------------------+
| id | name | pass |
+----+------+------------------+
| 3 | alan | 5af23f026beddb81 |
| 4 | john | 2ca0ede551581d29 |
+----+------+------------------+
2 rows in set (0.26 sec)


A number of other SQL commands also perform such an implicit COMMIT - dropping, creating and altering tables; dropping and creating indexes; and dropping and creating databases.

That said, it is interesting to note that if you begin a transaction, but exit the session before issuing a COMMIT or ROLLBACK, MySQL does *not* perform an automatic COMMIT; instead, it issues a ROLLBACK. The following example demonstrates:

[me@host]$ mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 5 to server version: 4.0.12-max-debug

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> USE master;
Database changed

mysql> BEGIN;
Query OK, 0 rows affected (0.00 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.02 sec)

mysql> exit
Bye

Now, when you open a new session, and check the "users" table, you'll see that there is no record for Tim, since MySQL issued a ROLLBACK when the previous session exited.

[me@host]$ mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 6 to server version: 4.0.12-max-debug

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> USE master;
Database changed


mysql> SELECT * FROM users;
+----+------+------------------+
| id | name | pass |
+----+------+------------------+
| 3 | alan | 5af23f026beddb81 |
| 4 | john | 2ca0ede551581d29 |
+----+------+------------------+
2 rows in set (0.01 sec)


This is of particular relevance to the next example - so flip the page and let's see how.

>>> More MySQL Articles          >>> More By icarus, (c) Melonfire

blog comments powered by Disqus