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, String thread) 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.
I am not an expert in databases, but I believe there is a misinterpretation of transaction and record lock here. You see, a transaction just isolates the operations performed in it from others; it also joins several operations as if they were one in the sense that either they are all commited or rolled back. But a lock (whether for a table or row) prevents other changes to that element. As far as I understand, when you do "select ... for update", you actually acquire a lock to the selected rows, so any other update in any transaction will have to wait until you release the lock (by commiting the transaction or closing the result set?). Another solution would be to alter the transaction isolation to allow dirty reads or non-repeatable reads (your MySQL version may not support this). Also some of the things you do by executing statements like "set autocommit=0" can -- and probably should -- be done through the JDBC API. Take another look at the Connection class javadocs. Here is a piece of external information that might help you: http://www.expresscomputeronline.com/20040426/techspace01.shtml . Again, I am not an expert in this. But since nobody else answered so far...
Henrique Sousa<br />SCJP 1.4<br /> <br />All men die, not all men really live - Braveheart, 1995
Yes, "for update" acquires row locks. They are held until a commit or a rollback. They can also in some databases (like Oracle) live longer than your code, e.g. your app crashes, until some server-determined timeout period or until your DBA toasts the client session for your database connection.
There are actually two notions of isolation. Only one is relevant if you are writing JDBC code, the other comes into play if you are using entity beans in EJB. Basically "for update" ensures that you are the only one playing with the row(s) of data until you are done with them. For EJBs there is a potential distinction between actually doing a "for update" versus the EJB server keeping track of who is accessing the various rows of a table. The latter is done as part of a performance trade-off where you find it safe to assume that your EJB app is the only one making database changes while the app is running.