We are using MySQL v5.1.67 under Solaris (SmartOS) in a zone. We are using Java EE and Jetty as our servlet container (Hibernate, Ehcache). Sometimes we get: "Could not commit JPA transaction; nested exception is javax.persistence.RollbackException: Error while commiting the transaction" and then "Lock wait timeout exceeded; try restarting transaction". We have the database as one file instead of one file per table. The file is large about 20 GB. There are millions of rows in many tables. One table has 30 million rows. Some table has varchar(255)s. Some others have texts. It seems like cells in the database has been broken. Because if you try to update the same entry in the DB you get a "Lock wait timeout exceeded; try restarting transaction", just by issuing a MySQL query. The problem persists until you restart the DB server. Sometimes they start appearing again after a while. Now they seem to come more and more often. There are a lot of threads in the Java EE .war application. We send more and more JMS messages. There is also PHP-code accessing the database. How do we stop this problem from occuring?
This is a DBMS-centered problem, not a JDBC one, not yet. Your problem needs to be pursued with a DBA at the center, to determine if there are really locks being held by one transaction that are blocking other transactions, and if so, to analyze the flow of work to find which transactions are blocking which others, and why. You may need a way to re-architect it so locks are always gotten in the same order by all applications and transactions, and held for as short a time as possible.
I don't think it's threads deadlocking. I've seen this problem many times before and I've always been able to fix it by restarting the MySQL-server. Then it executes normally day after day. Then something special happens and then it fails every day until I restart mysql. I've also encountered the same problem using the test database I have on my local machine. Only one job executes, the one I'm testing. First it works every run. Something special happens usually triggered by errant code. I fix the error. The job continues to fail with the same message "Could not commit JPA transaction; nested exception is javax.persistence.RollbackException: Error while commiting the transaction". If try to do a simple UPDATE SQL query accessing exactly the row that's been corrupted, I get a lock wait timeout. But if I restart the database it starts working again. The update works and the Java job works both locally and on the server. But now a special job has experienced more severe problems of this nature. I restart mysql and the job works when triggered manually but not when triggered chronologically on schedule. Actually all failed first and then a few worked but most failed. It's the same job executing multiple times.
Joe didn't say it was threads 'deadlocking'. Reread his reply.
He said there is likely a lock being held by one transaction that is blocking other transactions. Those other transactions then timeout waiting for the lock to be released.
Everything you just posted confirms what Joe said. You likely have a transaction that has a row locked and other transactions try to update the row that is locked and they won't be able to.
If you are content with 'fixing it' by restarting the DBMS, fine, but in order to prevent this from happening again, you will need to know exactly who is blocking who, for what resources, and why the threads that are holding needed resources aren't continuing and releasing them. Do these applications synchronize on anything outside the DBMS? If so, you could have a deadlock that involves a ring of DBMS and non-DBMS resources, so it would just look like waiting from the viewpoint of the DBMS. Does the DBMS sometimes lock data at a page level as opposed to a row level? If so, one thread may be locking data that it doesn't even refer to in it's work, blocking logically separate transactions. This can happen when index pages are locked. You want a DBA.
If so, you could have a deadlock that involves a ring of DBMS and non-DBMS resources, so it would just look like waiting from the viewpoint of the DBMS
.Except that a deadlock won't result in 'Lock wait timeout exceeded'. A deadlock will just wait forever. If it didn't then there wouldn't really be a deadlock since one of the sessions would give up.
Do you ever get 'lock wait timeout' from a JDBC call, or only from some non-Java interface? Especially if the latter, then this reinforces your need for a MySQL DBA/expert. I could make up stories, such as a DBMS internal process being hung, failing, or thrashing trying to write to a log or where the DBMS itself, or some preexisting user sessions that have not been killed/interrupted by the DBMS yet, even though the external clients have gone away) may be holding locks.