Announcement Announcement Module
No announcement yet.
Multi-threading datasources and "select last_insert_id& Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Multi-threading datasources and "select last_insert_id&


    Hope that you can help me with a problem that I am having using Spring Jdbc support and MySQL. Basically I have a number of DAO objects that have all been initialised with the same datasource but which each work on a different table in the data base each with auto-incrementing id columns as the primary key. The datasource is configured to use org.apache.commons.dbcp.BasicDataSource as the connection pool.

    Each DAO has an insert method which uses a sub-class of SqlUpdate to insert the row followed by a call to an instantiation of SqlFunction with the SQL "select last_insert_id();" with run() to get back the resulting id from the insert. These operations are called in a transaction with a READ_COMMITTED isolation level.

    Under stress testing with multiple threads of execution every now and then "last_insert_id()" function returns the id from another table. I understand how the problem can occur since presumably a connection is retrieved from the pool for the insert and then released. So that if another DAO executes then it can get the same connection and will do another insert making the last_insert_id() invalid when the original DAO gets to run again. I am imagining that the last_insert_id() function is attached to a connection in some way.

    So my problem is if this is what is happening is how can you lock the insert and retrieval of the id with last_insert_id() together.

    Many thanks for your help

  • #2
    You coluld take advantage of the JDBC 3.0 generated key feature - this requires a recent MySQL JDBC driver.

    Use the following update method:

    	public int update(Object[] args, KeyHolder generatedKeyHolder) throws DataAccessException {
    Use a GeneratedKeyHolder to pass in as the second parameter. Then you can retrieve the actual key by calling getKey on the generatedKeyHolder.


    • #3
      Thanks. Thats a good tip.

      I spoke to Rod who kindly offered the answer to my question which is that when a transaction has been started the thread will always be supplied with the same connection until the transaction has been committed.

      My problem was caused by one of my methods not getting set as requiring a transaction and hence not receiving the same connection.

      Many thanks to everyone for their help.