This is propably the most bizarre behaviour of any java application ever..
At some time ago I studied the transaction model of the mysql-database. I come to conclusion that one must use "for update" in select clauses inside transaction to prevent "lost update" problem..
Example of the "lost update" is following.
1. First transaction starts 2. Second transaction starts 3. First transaction reads money amount from bank account 1 and gets value of 1000. 4. First transaction "transfers" money from account 1 to account 2 with 500 (update account set money=money-500 where id=1;update account set money=money+500 where id=2) 5. Second transaction reads money amount from bank account 1 and gets value of 1000. (even first transaction changed that value in the 4th point) 6. Keep in mind that we don't want negative money-accounts. 7. Second transaction thinks that account 1 has 1000 money 8. Second transaction "transfers" money from account 1 to account 2 with 600 (update account set money=money-600 where id=1;update account set money=money+600 where id=2) 9. Conclusion, account 1 has balance of -100 10. If "for update" was used in select clauses this mess would have been prevented
Ok, that was quite simple (I hope). I made a java program to test these assumptions. This programs consists of two classes (dbThread and dbClass)
dbClass has method called updateDatabase(double amount, Stringthread) which tries to do issues mentioned before. dbThread just creates three threads of dbClass and then calls it updateDatabase-method at the same time. Both classes are listed at the end of this document.
The program worked like I assumpted (eg "for update"-clause is needed to prevent "lost update"). Program worked ok and I was happy about it. Then I forgot it. And started to study servlets. I installed tomcat 4.1 and then yesterday I tried this program I made earlier. I removed "for update"-clause and surprise, it DID NOT cause "lost update". (like it should, eg. account1 should have negative balance after each transaction) Not even I tried it many times..(except one exception, which I introduce next) What makes things veeery scary is that it sometimes needs those "for update" -clauses, for example, if I start my windows 95 and MySQL and then first run of this program causes "wanted" result (negative account), but not the rest ones.. How can this be possible? I have JDK 1.2.xxxx and MySQL 3.23.39. Does tomcat servlet replace some jdbc-classes or is it just my computer getting old? Any help would be greatly appreciated.
As far as I can tell, you are starting three threads in parallel and do nothing to regulate their execution - so why would you expect them to be executed in the same sequence every time?
The soul is dyed the color of its thoughts. Think only on those things that are in line with your principles and can bear the light of day. The content of your character is your choice. Day by day, what you do is who you become. Your integrity is your destiny - it is the light that guides your way. - Heraclitus
posted 12 years ago
Ilja Preuss, yes I can see your point, but this program used to work as I expect some time ago..and I have made no changes to it