how to solve lock_wait_timeout, subsequent rollback and data disappeareance from mysql 5.1.38

Question

i am using a toplink with struts 2 and toplink for a high usage app, the app always access a single table with multiple read and writes per second. This causes a lock_wait_timeout error and the transaction rolls back, causing the data just entered to disappear from the front end. (Mysql's autocommit has been set to one). The exception has been caught and sent to an error page in the app but still a rollback occurs (it has to be a toplink exception as mysql does not have the rollback feature turned on). The raw data files, ibdata01 show the entry in it when opened in an editor. As this happend infreqeuntly have not been able to replicate in test conditions.

Can anyone be kind enough to provide some sort of way out of this dilemma? What sort of approach should such a high access (constant read and writes from the same table all the time)? Any help would be greatly appreciated.

Answer 1

lock_wait_timeouts are a fact of life for transactional databases. the normal response should usually be to trap the error and attempt to re-run the transaction. not many developers seem to understand this, so it bears repeating: if you get a lock_wait_timeout error and you still want to commit the transaction, then run it again.

other things to look out for are:

• persistent connections and not explicitly COMMIT'ing your transactions leads to long-running transactions that result in unnecessary locks.
• since you have auto-commit off, if you log in from the mysql CLI (or any other interactive query tool) and start running queries you stand a significant chance of locking rows and not releasing them in a timely manner.