Hello, I have a problem when trying to get the last inserted ID's for manually exectued SQL queries when using the SQLQuery class.

The table i'm querying gets created at runtime and the columns it contains can change at a moments notice so i can't use traditional pojo/entity classes for this table.

The code listed below works fine when inserting records one at a time but once a decent amount of traffic hits there's a race condition causing the IDs to get mixed up. (the IDs returned go 130, 131, 133, 133 for example rather then 130,131,132,133).

I tried wrapping the thing in a transaction but that causes locking exceptions when there's a decent amount of traffic. That and a transaction shoudn't be needed as I am pretty sure the jdbc returns the correct value.(java.sql.PreparedStatement.getGeneratedKeys()) I'm just not sure how to access this value when using the hibernate SQLQuery class.

Can someone tell me a way to find the id of the record i just inserted that isn't sensitive to race conditions and does perform well with dozens of concurrent updates?

Hibernate version:
3.2.6

Code between sessionFactory.openSession() and session.close():
SQLQuery insertQuery = session.createSQLQuery("INSERT INTO foo VALUES ('bar')");
insertQuery.executeUpdate();
Integer id = (Integer)session.createSQLQuery("SELECT MAX(id) FROM foo").uniqueResult();

Name and version of the database you are using:
IBM DB2 9.1