Hello,

Could someone help me please, I've hard time figuring out this transaction isolation level thing.

I'm using MySQL and InnoDB for my testing. They support all four different isolation levels

If current isolation level is repeatable read then one transaction can't see updates made in another transaction before they BOTH commit, on the other hand if current isolation level is committed read then one transaction can see updates made also in another transaction (T2) after T2 has committed. This behaviour would make it logical to set isolation level to "committed read" in following example. Example is sort of "banking application". where desired sum of money is transferred from one account to another. E.g. first application checks that the "source" account has at least desired sum of money, then it decreases the "source" account with desired sum of money and increases the "target" account with desired sum of money. Should one of these conditions fail is everything cancelled (rollbacked). Here it's how it is done.

1. Transaction starts
2. Transaction SELECTS a row (source) from the database
3. Application checks that source row has enough money, if not then it calls rollback
4. Application tries to decrease money from source account. If this fails, it calls rollback
5. Application tries to increase money to targer account. If this fails, it calls rollback
6. If all worked fine call commit

It would be logical if SELECT-query in the 2nd phase returned up-to-time-information about the source account (E.G. isolation level should be committed read). Remember that current transaction reading the database and also every other transaction involved must call commit before changes are visible to the current transaction if isolation level is repeatable read. On the other hand if isolation level is committed read then it is enough that other (not the current) transactions commit.

However I made a simple PHP/MySQL-script to check out what these different isolation levels do. This script does basically same as the pseudo-code example before and it is called all every second with HTML's meta http-equiv='refresh' content='1,page.htm'-tag. Next thing was that I opened three Internet-explorer's with this page. If SELECT-query for the source account returned too low account to be transferred then this script wrote error on another database table. What I found out was that with repeatable read isolation level everything seemed to be working nicely, but with read committed isolation level things worked mysteriously. I tried both multiple times and they worked same way every time..

I would be very grateful if someone could clarify some things for me..