Why Transactions

Anywhere data integrity is an absolute, transactions will be there. Banks, eCommerce sites, inventory systems, financial systems or any ofther kind of data application that requires data to be stored safely. Transactions are not for everyone or for every situation. Their is a performance hit with transactions. So if you are storing hundreds of thousands of log entries and a few missing ones would not hurt, then transactions are not for you. But if you are building an inventory system that connects accounting and the shipping departments together and any loss of data could mean lost money, well, the answer is clear.

Example 1 Non-Transaction

$userid = 2; $child = $userid * 3;
mysql_query(“delete from account where id=” . $userid)
mysql_query(“update account set ballance=1000 where id=” . $child)

Lets say the above transaction failed or the server shut down just after the delete, this would mean the following update would have never taken place. Depending on your database, synchronization may now be lost.

Example 2 Transaction

mysql_query(“begin”)
mysql_query(“delete from abc where id=2”)
mysql_query(“update abc where id=4”)
if (mysql_error())
mysql_query(“rollback”)
else
mysql_query(“commit”)

In this situation a failure could be captured and any modifications could be safely stored or removed. The beauty of a transaction. That is right, a failure would allow you to remove ALL changes that took place, effectively restoring your database to its previous state.

AUTOINDEX NOTE:
Autoindex or primary auto_increment fields are not rolled back to the previous number. The data is removed from the database, but the autoindex persists. This is for multi-user saftey and redundancy purposes.