MySQL 5.5.23, InnoDB, file per table.
When creating a prepared or callable statement via JDBC, what is the default resultset concurrency? See http://docs.oracle.com/javase/6/docs/api/java/sql/Connection.html#prepareStatement(java.lang.String
, int, int)
Is it ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE ?
I am getting the "Lock wait timeout exceeded; try restarting transaction" exception being thrown in one app that is trying to update a record in a table at the same time another app (separate connection) is running a long running query on that table (purely select with some custom function calls in it, not a select for update).
In the InnoDB transaction model, the goal is to combine the best properties of a multi-versioning database with traditional two-phase locking. InnoDB does locking on the row level and runs queries as nonlocking consistent reads by default, in the style of Oracle.
This was recently migrated from Oracle where this problem never occurred. None of the prepared statements were ever created with the resultset concurrency parameter set.
I have since updated the innodb_lock_wait_timeout parameter from its default of 50 to 600, but that is still a bandaid fix. If I have to run a query that takes longer than that it'll happen again. Do I need to go back through all my code and add the ResultSet concurrency parameters in?