MySQL 5.6 Transactions

The concept of transactions is a new topic added to the MySQL 5.6 Developer exam it wasn’t on the old 5.0 Developer exams and hence is not covered in the Developer chapters of the MySQL 5.0 Certification Study Guide (It is however in Chapter 29, in the DBA part of the book). Here are my study notes on transactions, cobbled together from various books and online resources.

Transactions

A transaction is defined as a set of SQL statements that must execute as a complete unit. Either all the statements execute successfully, or if one of them fails, the system should cancel the entire transaction. Chapter 11 of Head First SQL and Chapter 2 of MySQL Developer’s Library cover the theory in detail, as does Chapter 29 of the MySQL 5.0 Certification Study Guide.

But it is worth mentioning the four ACID characteristics of transactions:

Atomicity
All of the statements in a transaction must execute, or none of them must execute. You can’t execute half a transaction.
Consistency
The database must be left in a consistent state before and after the transaction executes.
Isolation
Transactions should have no effect on another transaction, each should see a consistent view of the database.
Durability
After a transaction executes, the data must be saved to the database.

Remember that transactions in MySQL currently only work with InnoDB tables – you can’t use them with MyISAM or MEMORY tables.

Using Transactions

To start a transaction, you can use either

START TRANSACTION;

or

BEGIN TRANSACTION;

then follow that with the SQL statements that make up the transaction. Finally you have the option to either

COMMIT;

which will commit the changes to the database, or if one of the SQL statements has resulted in an error you can

ROLLBACK;

which will undo any changes made and revert the database back to how it was before the transaction was started.

To see transactions in action, it is useful to open up two MySQL prompts, run some transactions in one prompt and look at the effects in the other prompt. The attached script has some demo queries you can use.

Using Savepoints

Generally you should try to make transactions as small as possible, so they execute quickly and release the database tables for other transactions to use. But sometimes you might have a long transaction and rather than rolling back the entire transaction, you want to rollback part of the transaction. You can do this via savepoints:

START TRANSACTION;
INSERT INTO transtest(name) VALUES('Aunt Patty');
SAVEPOINT savepoint1;
INSERT INTO transtest(name) VALUES('Aunt Selma');
ROLLBACK TO savepoint1;
COMMIT;

Here’s an example script which demonstrates transaction savepoints:

MySQL Autocommit Mode

By default, MySQL runs in autocommit mode – this means that when you type in a command followed by a semi-colon and hit return, the command is immediately committed to the database.

When you start a transaction, you temporarily suspend autocommit mode, so that the commands you type are not committed to the database right away. The commands are only saved to the database when you commit the transaction.

To check what the current autocommit system variable value is:

SELECT @@autocommit;

You can then disable autocommit:

SET autocommit = 0;

This has the same affect as starting a transaction, you’d have to remember to commit after every SQL statement:

SET autocommit = 0;
INSERT INTO transtest(name) VALUES('Kent Brockman');
COMMIT;

And don’t forget to turn autocommit back on again:

SET autocommit = 1;

MySQL Commands Which Implicitly Commit Transactions

Transactions end when they are committed or rolled back, or when autocommit mode is enabled as described above. A transaction will also end if the client’s session ends before the transaction is committed. In this case, the transaction is rolled back automatically when the session is ended.

There are also certain MySQL commands which, if present in a transaction, which cause the transaction to commit. These include statements which alter the structure of database objects:

ALTER TABLE, RENAME TABLE, CREATE INDEX, DROP TABLE, 
DROP INDEX, DROP DATABASE, TRUNCATE TABLE

Note that TRUNCATE TABLE is listed. Truncate is equivalent to dropping and recreating a table, so it makes sense that it would cause a transaction to commit. The DELETE command however does not cause a transaction to commit.