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,
