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

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 o开发者_JAVA技巧f approach should such a high access (constant read and writes from the same table all the time)? Any help would be greatly appreciated.

What is the nature of your concurrent reads/updates? Are you updating the same rows constantly from different sessions? What do you expect to happen when two sessions update the same row at the same time?

If it is just reads conflicting with updates, consider reducing your transaction isolation on your database.

If you have multiple write conflicting, then you may consider using pessimistic locking to ensure each transaction succeeds. But either way, you will have lot of contention, so may reconsider your data model or application's usage of the data.

See, http://en.wikibooks.org/wiki/Java_Persistence/Locking

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.