Tuesday, February 28, 2012

How to use transactions in MySql?

Today I struggled a few hours just making transactions work.
But first: why should we use transactions? I will give you a short example.
Let's suppose we want to transfer points, money, credit from one user to another.
So we would have two queries:

update user set credits='100' where userId = 1;
update user set credits='0' where userId = 2;

So, in case when first query throws an error all queries run must be `reversed`. So in this case transactions are very useful.
Here is an example I use, it is a very basic one:

SET autocommit=0;

select * from product where deleted=0 and name='Juice Coke';

insert into product(name, fk_category, fk_packaging, fk_stock, active, decimals, price, has_special_price, special_price_description, added_date, deleted)
values ('aJuice Coke','2','4','1','1','2','100.244','1','DESC SPECIAL PRICE','2012-02-28 08:26:47', 0 );

select * from product where deleted=0 and name='Juice Pepsi';

I have some select queries and an insert. Case when insert throws error it should be roll backed all transaction queries. However in these query lines i don't catch errors or so..is just for you to see the idea how it works.
Now, after you catch the idea there might be a problem :) You will not be able to run transactions if your table's engine is MyIsam ! Must be InnoDB !!!
As I found a helpful post I will write here the basics about those two engines and give you the url of the source where you can find more.


Let's start with MyISAM since it is the default engine with MySQL. MyISAM is based on the older but proven ISAM code but has been extended to be fully-featured while retaining the reliability. Data in MyISAM tables is split between three different files on the disk. One for the table format, another for the data, and lastly a third for the indexes.
The maximum number of rows supported amounts to somewhere around ~4.295E+09 and can have up to 64 indexed fields per table. Both of these limits can be greatly increased by compiling a special version of MySQL.
Text/Blob fields are able to be fully-indexed which is of great importance to search functions.
Much more technical information can be found on MySQL's MyISAM Manual Page.


InnoDB is relatively newer so the scene than MyISAM is so people are still weary about its use in environments than run fine under MyISAM. InnoDB is transaction-safe meaning data-integrity is maintained throughout the entire query process. InnoDB also provides row-locking, as opposed to table-locking, meaning while one query is busy updating or inserting a row, another query can update a different row at the same time. These features increase multi-user concurrency and performance.
Another great feature InnoDB boasts is the ability to use foreign-key constraints. FK constraints allows developers to ensure that inserted data referencing another table remains valid. For example, if you had an authors table and a books table and you wanted to insert a new book while referencing the author. The author would have to exist in the authors table before inserting them in the books table because a foreign key was specified in the books table. At the same time you would not be able to delete an author from the authors table if they had any entries associated with them in the books table. More on this in a later article...
Because of its row-locking feature InnoDB is said to thrive in high load environments. Its CPU efficiency is probably not matched by any other disk-based relational database engine.
Here is the link source: http://www.mikebernat.com/blog/MySQL_-_InnoDB_vs_MyISAM
I will give you more details about transactions only if you request :)

No comments:

Post a Comment