« Beware of what you post online | Home | Why i won't use Google Apps ever »
February 14, 2009
MySQL savepoint for transactions
Sometimes we start using a language or product feature because someone introduced it to us but not all the time we do some research about it to fully understand it's power. At least this is what happens to me.
Yesterday i started planning a new project database and when i was planning the transactions i thought it was a good time to actually read about it.
First of all, what is a transaction?
I'm not going to talk about money or banks but a transaction is a group of actions. If the group of actions is successful then the transaction is ok, otherwise it is not. We don't have half transactions.
When we talk about databases sometimes we start doing a group of actions but in the middle one of them fails. In this case we need a way to quickly rollback the actions before the error.
This is what a transaction permits us in MySQL.
For example, let's create a new table:
create table tester ( title varchar(100) unique ) engine = innodb;
Oh! BTW, this is available on InnoDB database engine so if you are using MyIsam you are out of luck!
mysql> insert into tester values ('one');
This will successfully insert a new record. Now lets create a transaction:
mysql> start transaction;
mysql> insert into tester values ('two');
mysql> insert into tester values ('one');
Since "one" is already on the table an error will be thrown. The thing is that i don't want the "two" entry to be there if "one" is unsucessful:
mysql> rollback;
mysql> select * from tester;
We'll see that the "two" value has not been inserted. Nice!
Let's repeat the inserts but first lets clean the table:
mysql> truncate table tester;
mysql> start transaction;
...
The insertions are correct but they are not "live" since we have flush the transaction. They way of doing so is:
mysql> commit;
Schweet! _
Now into what i discovered yesterday. We can create "savepoints" in the middle of the transaction and rollback to that specific position anytime before the commit:
mysql> start transaction;
mysql> insert into tester values ('two');
mysql> savepoint twoInserted;
mysql> insert into tester values ('one');
ERROR! Rollback to savepoint
mysql> rollback to savepoint twoInserted;
mysql> commit;
You can have as many savepoints as you want. They will be deleted when you commit. Remember that some mysql actions will auto-commit.
If you use a savepoint name twice the oldest will be replaced. If you want to remove a savepoint manually:
mysql> release twoInserted;
Not a real example but anyway, MySQL is fun!
Hope this helps someone!
--fernando
