I am using hibernate, and innodb and mysql. I have a pessimistic lock (on a specific table row) obtained at the beginning of a very long hibernate transaction. On occasion, when I run the program that starts this transaction and am running in eclipse in standalone application mode, and do a hard stop of the application (which forces a threaddeath), I have the following scenario:
MySQL does not realize that the transaction was aborted. The next time I run my application, the function which obtains the pessimistic lock (at the beginning of that long transaction) blocks, even though MySQL should realize that that row is unlocked.
The only way to get things working again is to stop the MySQL daemon and restart it, after which the pessimistic lock is obtained.
This really concerns me. This means that any VM exit other than by exception or clean shutdown can leave MySQL in a broken state that requires restarting MySQL daemon. An e.g. is using the normal way to shutdown and restart Tomcat, which in my understanding involves a threaddeath, just like using eclipse to abort a running application.
Have other people encountered this problem? Is there a clean way to stop tomcat which does not involve threaddeath? Shouldn't MySQL be immune to threaddeath in the middle of a transaction that has obtained a pessimistic lock (select for update)?
I am stopping tomcat with $CATALINA_HOME/bin/shutdown.sh
Andy
I'd say that you didn't wait enough to trigger the transaction timeout. In any database (and MySQL, for certain), you can specify a transaction timeout which will rollback the transaction if it's not committed before then.