[Update:March 2008]
The MyTransaction library is now available to download under the MIT Open Source License.
If you use the library, please let me know how you get on...
Everyone who's ever built a MySQL database app has made a choice about which database engine to use. You may not realise it of course but you have.
In short the difference is; if you need ACID transactions, foreign key-constraints and row-level locking use InnoDB, otherwise use MyISAMs.
There are also a couple of more subtle differences which can bite you later on like COUNTS. SELECT COUNT(*) FROM big_table; will return in a milisecond from a MyISAM but the same query requires real work from the InnoDB engine.
Like many people I started with MyISAM; it's fast, it uses less disk, memory and effort. I never missed the foreign key constraints because Spirit's aesthetics engine can deal with that for us and I've not yet found we really need row-level locking. The one thing though that would be really handy to have in MyISAM would be transactions.
A transaction is simply a way of telling your database "All or nothing at all". It's about database integrity.
Take for example the following sequence of commands, following Johnny paying Billy £5:
At the end of the week you can look at Jonny's starting balance, his current balance and all the items in the ledger which mention him and you know that all is right in the world.
Now it doesn't matter what order you do this in, if the process stops half way through your database is going to be a mess, your totals at the end of the week will be all wrong and you won't have a clue why.
Transactions are part of the answer to the problem because although you issue your commands as normal, with a transactional database your changes are only pencilled into the database and only right at the end, when you're sure you happy with everything do you tell the database to commit it to pen.
Well - in our case yes. Testing!
Many of the sites we run walk the user through a long sequence of web-forms. At the end of the form the system has collected a lot of data together and has to comit it to the database. It may be committing a dozens of records to dozens of different tables so the queries can get pretty complex.
When you're testing a new process the last thing you want to do is to click through the whole process filling out forms every time you make a little change. And when you get to the end you don't want the system to be doing something different the second time based on the data that went in the last time.
Wouldn't it be lovely if, when in test mode, your website could do everything, 100% as normal... but if anything goes wrong, or even if everything goes right, it would undo everything it's done and leave you back exactly where you started so you could test it again!?
And so I set about writing a transaction & rollback mechanism for MyISAM... and it works.
If your DB class is abstracted properly (and is written in PHP) you should be able to add in this functionality with just a few lines of code.
It's really not very exciting or clever and it works like this:
First you start a transaction, for which you get a numeric ID.
Then rather than executing your statements yourself you pass your statement to the transaction object which takes care of executing it for you.
Just before it executes your statement it records, within the database, the state of what you're about to change.
If you want to rollback your transaction the transaction library simply executes another sequence of statements on the database to undo the changes you've just made and revert the database back to it's original state.
If you're happy with your changes and wish to 'commit' them to the database then the transaction library simply does nothing.
Finally, by default, if your process completes and no commit() has been received the transaction library will automatically rollback all transactions.
If your process dies completely and the on_exit_handler isn't called then you're stuck back where you were. Also if you disconnect from the database you're equally scuppered. (See above: Caveat Emptor)
Of course not. It's an invaluable testing tool. It allows you to re-make the same request again and again in the knowledge that, when you're done, the database will be back exactly the way it was.
I haven't benched it (yet) but it should be pretty fast depending on the amount of data you're changing. It stores the data in temporary tables and has little or no requirement for indexes etc.
Because this is easier, because other people may be working in other parts of the database. Because don't want to keep re-imaging every step.
Yes. No support for aliases such as UPDATE foo AS t1, bar as t2 SET t1.xxx = 1 WHERE etc. as yet ( partly becuase I don't tend to use them )
Also on the list to add is management of LOCK TABLE WRITE which at the moment it doesn't bother with.
Yes, by all means. I need to upload it somewhere really, if there's any interest. For now if you want a look at the source just nudge me (jim) and I'll email it over.
Oh - and silly me, I should mention.. it's PHP (4) although it's not very long or complicated so it could easily be ported elsewhere.
A few suggestions...
I just checked it out, and it works awesome so far!
I noticed/did a couple of things:
Your idea is really good and you also worked it out very good!
Thanks
Paul
Great!
I've stumbled upon this, and just want to cheer you up. Seems great for implementing a undo system for a webapp.
The only thing that stops me for trying it, it's the alias thingy. All my queries use alias, so it would be a little pain in the ass to change them.
How difficult would be to implement it?
Great Indeed
Sounds great, MyISAM is my choice for most things but I often end up using a library that provides fake transaction support. This one looks like it's worth looking into!
@Paul: Yeah, I hear what you're saying about the auto-increment keys not being rolled back. I think that you're right and that the expected behaviour in native (InnoDB or Berkely DB) transactional support is to NOT roll back auto-incrementing keys in event of a booboo.
I guess you could design something that would automatically roll the keys back, but I don't see the point. I guess if you needed monthly stats for some special project you could just run some kind of re-keying function over a copy it that one time. Probably be safer anyway, particularly on a big table..........