|
Using Transactions In Mysql / PHP for New Users
Author: HumanX | Monday March 15, 2004
So you need safety, you need to know that a group of SQL operations either survive together or die together, you need MySQL. Transactions in MySQL or in SQL are relatively simple and their are just a few things to know for you to get off the ground running.
First of all, transactions allow you to modify your database in such a way that any data manipulation is practically guaranteed, but, that does not mean guaranteed to be stored. What this means is that if any part of your SQL statement/s fail, the entire process is rolled back to the beginning as if the transaction never took place. *More on autoindex.
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
mysql_query(“delete from abc where id=2”)
mysql_query(“update abc where id=4”)
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.
How you can get started
Download and Install MySql 4 or later. The default install of MySql does not come with transactions active. You can change this by editing the my.conf or my.ini file and uncommenting the INNODB set settings in the mysql configuration file. Those values could be set to a a 1 gig transaction *file on your server, check the values and adjust to your needs.
* INNODB data is stored in a single file, unlike the default MyISAM tables (Mysql Default Table). Actually. INNODB could be spread accoss multiple files. You can read more on MySql's web site regarding this.
If you have mysql installed already, just follow the above. Do not forget to restart MySql. If you are working from an old database, do not forget to backup. Now convert the tables that you would like to use transactions on to INNODB.
Example:
ALTER TABLE TYPE=INNODB (Do not include <>)
If you are creating a new table:
CREATE TABLE (variable info) TYPE=INNODB
That is all that it takes, you are ready for transactions. Just use the begin,commit and rollback statements like in the previous statements and start having fun!
The Good, The Bad and The Ugly
Some things you should know.
1.Transactions are more processor intensive the non-transactional statements. That is the nature of the beast. Maybe 5%-10%.
2.Remember, auto_increment / autoindex fields do not roll the numbers back. They continue to increment.
3.Multi-User environments require careful implementation Incorrect usage of table locking can wreak havoc on your environment.
4.INNODB takes up more disk space.
If you have read this article and experimented with transactions it is now time for you to move to MySql's web site and read the MySql manual regarding transactions. There is a a lot of good information regarding table locking usage as well as some more inside information on transactional approaches.
Article by HumanX, http://www.linuxdig.com, please feel free to use or post this article as long as the article is posted in its original form. You can contact me at LinuxDig.Com through the contact page.
|
