Simulating Transactions in MySQL
Wed, Sep 20, 2000; by John Lim.


Life doesn't have to be logical. We fall in love, get attached to someone, promise ever-lasting love, and then we break up. And the worst part is of course the breaking up.

It's the same with databases. We decide we like this database, so we store records into it. We promise ourselves that the database will hold our records for ever and ever, and then the database crashes; now we want to strangle the database server...

This is where transactions are better than real-life love. Transactions is a technology that ensure that if you have to update multiple tables and you crash midway, you can rollback the data to a consistent state just before the crash. Imagine doing that with your loved one!

MySQL is the most popular open source database on Earth. The current stable release, 3.22 does not support transactions, but with a little bit of intelligence and discipline, we can simulate transactions.


How Transactions Work
An example of how we use transactions is a shopping cart system after checkout. Here we are generating an invoice and the invoice items based on the contents of a shopping_cart_items table.

Suppose we crash after creating the invoice record, but before all the invoice items are created. Or suppose we crash before we can delete all shopping cart items. Then we will be double-counting the items: once in the shopping cart, the other in the invoice.

Transactions help solve the problem, as can be seen below in pseudo-code:


begin tran;
INSERT INTO invoice (...) values (...);
$parent_id = mysql_inserted_id();
for each shopping_cart_items
   INSERT INTO invitems (...,invoice,..) VALUES (...,$parent_id,... );

DELETE FROM shopping_cart_items WHERE cart_id = ?
commit tran;

Now if we crash before we insert all the invoice items, the database will notice that a transaction was taking place, and will rollback the data to the state it was before the begin tran. So the invoice is not generated because the commit tran was never executed, and the shopping cart remains intact.


MySQL
The current stable version of MySQL (3.22) does not support transactions. This is a feature currently in testing for 3.23, but I forsee that many web hosts will not be upgrading for some time to come. Not to worry, we'll simulate them using some techniques developed long ago for older databases.

There are 2 types of transactions we can simulate, record creation transactions, and update/delete transactions.


Record Creation Transactions
In the above example, we created the invoice record first. Then we used mysql_inserted_id() generated from the invoice record to provide the link from the child invitems to the parent invoice record.
In simulated transactions, we do things the other way round. The child records must be created first, then the parent.

Why? Because normally we view information from top-down, parent to child. In a simulated transaction, child records are treated as invalid if the parent record has not been created yet. To do this we also need to generate synthetic keys in advance as primary keys for the invoice table.

For example, assuming we have a function called generate_key() to generate the synthetic keys:

$parent_id = generate_key();
for each shopping_cart_items
   INSERT INTO invitems (...,invoice,..) VALUES(...,$parent_id,... );
INSERT INTO invoice (id,...) VALUES ($parent_id,...);

In this case, if we crash while updating the invitems, the invoice record is not created. Provided we never access the invitems records without joining to the parent table, we are ok. The simulated transaction will work.
To be safe, we can run a batch job in background to delete orphaned child records.

Now y