default resultset concurrency?
Posted by: Chris Cheshire
Date: September 04, 2012 06:12PM

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).

From http://dev.mysql.com/doc/refman/5.5/en/innodb-transaction-model.html

Quote

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?

Thanks

Chris

Options: ReplyQuote


Subject
Written By
Posted
default resultset concurrency?
September 04, 2012 06:12PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.