MySQL Transactions

As I announced in my previous post, from today I am starting with series of tutorials about those rarely used MySQL features like transactions, referential integrity, fulltext searches etc. Developers working with MySQL don’t use these features very much. Many of them don’t know that those features even exists, so very often we have a situation that on various advocacy like discussions MySQL is declared as “uncomplete” database, because it lack support for above features. However, a lot of people doesn’t know that MySQL support transactions since version 3.23.15 (which is out since May 2000). Simmilar stands with other features I mentioned above.

My goal in this and follow up tutorials is to break some prejudices about MySQL, as well to encourage developers to start using all features available in their favorite RDBMS.

Today we are starting with transactions.

In SQL parlance, transaction stands for series of sql queries that should be run as a whole. If all transaction queries are succesfully executed, transaction is succesfull, otherwise transaction has failed, and we are reverting to previous state before transaction began. Many people think that transaction is something money related, so I will use such example to describe this whole thing in easiest possible way.

Imagine a situation where John Doe needs to pay it’s client Mickey a 1000USD for some service. Here is how this situation can be described with tipical MySQL parlance:

update bank_accounts set amount = amount - 1000
where user = 'JohnDoe';

update bank_accounts set amount = amount + 1000
where user = 'Mickey';

Now, what if some of queries above has failed? We would have a situation that John left short for 1000 bucks without to paying Mickey for his services, or that Mickey get free credit from his bank in case first query is not done. By using transactions in case some error occured, system is reverting back to previous state (immediately before transaction started), so in this particular scenario, both queries or none will be executed.

RDBMS’s with transaction capability typically needs to provide 4 behaviours, which are usually refered as ACID:
Atomicity
Either all of the tasks of a transaction are performed or none of them are.
Consistency
Database is in a legal state before as well as after transaction end.
Isolation
Transaction doesn’t have any effect to other processes. That also means that effect of single queries are not visible to other clients untill transaction is finished.
Durability
Once transaction success, it persist and can’t be undone.

Now, enough theory, let’s do some real life usage. Syntax you will see here is customized to work with MySQL 4.1.x. Depending of your version of MySQL server, you might need to make certain changes. If you want to find out which version you use, run following query in your mysql client program:

select version();

Using transactions is currently possible only with InnoDB and BDB tables. If you’re unsure whether your server supports them, use show engines query which will list supported table types. If you need to enable support for InnoDB (BDB) tables, please consult MySQL Manual how to do that (or contact support if you use MySQL server on your hosting account). In our examples here we are going to use InnoDB tables, but the same principle applies to BDB tables as well.

For start, let’s create one test table:

create table test(
name char(20) not null unique)
engine = innodb;

And let’s try to use one transaction:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(name) values('John');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test(name) values('Mickey');
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from test;
+--------+
| name   |
+--------+
| John   |
| Mickey |
+--------+
2 rows in set (0.02 sec)

As you can see, start transaction is used for beggining of transaction. After 2 succesfully executed queries we used commit for the end of transaction, which is done succesfully in this case. Important thing to note is that none of changes during transaction (let say inserting new name John) were not visible to the other clients until commit has executed. That’s Isolation property I mentioned above.

Now, lets look at one failed transaction:

mysql> select * from test;
+--------+
| name   |
+--------+
| John   |
| Mickey |
+--------+
2 rows in set (0.02 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(name) values('Peter');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test(name) values('John');
ERROR 1062 (23000): Duplicate entry 'John' for key 1
mysql> rollback;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from test;
+--------+
| name   |
+--------+
| John   |
| Mickey |
+--------+
2 rows in set (0.00 sec)

In this case during transaction error has occurred, because we’ve tried to enter duplicate value (‘John’) to a field defined as unique. As a result we got an error, so we issued rollback query. As you can see, after rollback table contains the same records as before transaction begun, which mean the name ‘Peter’ we entered during transaction is not left there, as transaction as a whole has failed.

Starting from MySQL 4.1.1 it is possible to set savepoint during transaction so we can revert to it with rollback command. Here is an example:

mysql> select * from test;
+--------+
| name   |
+--------+
| John   |
| Mickey |
+--------+
2 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(name) values('Peter');
Query OK, 1 row affected (0.00 sec)

mysql> savepoint point1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(name) values('Bryan');
Query OK, 1 row affected (0.00 sec)

mysql> rollback to savepoint point1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(name) values('Dragan');
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from test;
+--------+
| name   |
+--------+
| John   |
| Mickey |
| Peter  |
| Dragan |
+--------+
4 rows in set (0.00 sec)

As you can see, after we entered first name (Peter) we’ve saved position as point1. Other name ‘Bryan’ is not present since we reverted back to point1. Also after reverting we’ve succesfully entered one more name (Dragan) before finishing transaction with commit.

This is all about transactions in this short tutorial, I hope it contains enough informations to get your feet wet, so you can start using them with MySQL. If you are looking for more informations about transactions, you can look at MySQL Manual or try some good book on subject. My personal recommendation is MySQL 3rd Edition by Paul DuBois.

Comments

  1. vijay
    October 9th, 2006 | 7:21 am

    hi,
    How can we get the list of savepoints that set during the transaction? becuase we can set as many savepoints as we need if the transaction is too long and it’s difficult to remember all the savepoints. is there any command in mysql that serve the purpose?

  2. December 6th, 2007 | 4:55 pm

    Nice article. I was searching the net for mysql transaction and I landed on your page. Well explained and it helped me a lot.

  3. I F T I
    April 7th, 2008 | 4:00 pm

    Thanks for this useful article. But I have one more question to you. Why mysql procedure does not ensure me this nature. Its really a manual procedure. Any autmatic rollback system in mysql when any one query of a collection of querys will fail to execure. Again thank you. Waiting for your answer. Thanks in advance.

  4. vijayface
    July 3rd, 2008 | 12:09 pm

    Good Article with simple examples.easy to understand..
    Keep on posting like this…
    Thanks

  5. akioshin
    February 8th, 2009 | 6:37 pm

    nice i luv the way you explain it and by the this is very helpful to me this is my thesis building a ddbms in a ecommerce site and i only have 2 weeks i really need your help, i was wondering if you can give a tutorial using transactions with 2 servers

Leave a reply