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 you are probably saying, we didn't cover the delete. What happens if we crash before the delete? Then we would have the purchased items both in the shopping cart and being shipped to the end-user.
Update/Delete Transactions
This sort of transaction is harder to handle. We need to implement a status field that tells us whether we are outside or inside a transaction.
Then after a crash or whenever the database is restarted, we perform a scan to detect whether any transaction occured when we crashed, and perform a repair if it is so.
For our example:
UPDATE shopping_cart_items SET status='IN_TRANS' WHERE id = ?;
$parent_id = generate_key();
for each shopping_cart_items
INSERT INTO invitems (...,invoice,cartid,..)
VALUES (...,$parent_id,$cartid,... );
INSERT INTO invoice (id,...) VALUES ($parent_id,...);
DELETE FROM shopping_cart_items WHERE id = ?;
Then in your repair pseudo-code:
select cartid,id from shopping_cart_items,invitems
where status = 'IN_TRANS'
and invitems.cartid=shopping_cart_items.cartid
INTO $cartid,$id;
for each ($cartid,$id)
SELECT id FROM invoice WHERE invoice.id = $id;
if no records returned
DELETE FROM invitems WHERE cartid = $cartid;
else
DELETE FROM shopping_cart_items WHERE cartid = $cartid;
The same method of repair used for deletes is also used for updates.
If your MySQL database is out-sourced, and you are not informed when the MySQL database is restarted, then you need to do periodic scans and repairs. Sounds a bit like fsck or scandisk doesn't it?
In MySQL, we can generate the synthetic key using a special table to hold the last valid key number. Then whenever we want a new key, we lock the table, increment the key number by one, read the latest value, then unlock the table.
For example, assuming we create a table called invoiceid with one record containing one field called id. In pseudo-code:
function generate_key()
{
LOCK TABLES invoiceid WRITE;
UPDATE invoiceid SET id=id+1;
SELECT id FROM invoiceid INTO $id;
UNLOCK TABLES;
return $id;
}
Conclusion
So all you broken-hearted lovers out there -- get jealous. Transactional databases do it better. They can rollback to the starry-eyed time when lovers are still in love. And MySQL can do it too with a little bit of love and care.
Feedback
Since this article was released, I have received some feedback: mostly concerns about the risks involved in using MySQL. Well, I have actually used these techniques and they work, but I also agree that they are not a complete substitute for a database system that fully supports transactions.
You need to think about the risks involved in using these techniques with MySQL or similar databases. If you don't feel comfortable, I suggest you invest some money in getting a Relation Database Management System that supports transactions such as Interbase, Oracle or MSSQL 7.
You will still need to code with discipline even if you are using Oracle. It is possible to write buggy transaction code that will corrupt the data integrity on rollback. There's no substitute for good code.
Best wishes, and let's hope we never have to rollback our love-life.