wood burning stoves 2.0
The moose likes JDBC and Relational Databases and the fly likes Concurrency Issue with HSQLDB Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Concurrency Issue with HSQLDB" Watch "Concurrency Issue with HSQLDB" New topic

Concurrency Issue with HSQLDB

Shunjie Liu

Joined: Feb 21, 2006
Posts: 7
I am using IDENTITY to auto-generate my ID in one of my tables
and using CALL IDENTITY() to get back the newly created ID.

However, as I am doing this over a network. I noticed that my JDBC code
executes CALL INDENTITY() before my INSERT INTO ... statement is completed and thus returning the the incorrect ID.

when I put a sleep() for 5 sec, everything works fine. So I suppose its a problem with concurrency issue.

I am wondering is there any method to ensure when I call the CALL IDENTITY() function, it will return me the correct id ? A method that when updating the database, the CALL IDENTITY will be blocked before the INSERT statement is completed ?

Thank you
Kaydell Leavitt
Ranch Hand

Joined: Nov 18, 2006
Posts: 689

Here is my insert method. It works in single-user, I have not tried multi-user yet but I believe that it should work. This method makes a couple of assumptions: 1) only one record is inserted in the query, and 2) that the auto-generated key is a long (a BIGINT).

[ July 10, 2007: Message edited by: Kaydell Leavitt ]
Shunjie Liu

Joined: Feb 21, 2006
Posts: 7
Thanks for the reply.

I am sure your code works for mySQL or other heavy weight database.
but this HSQLDB don't support that...oh well have to keep trying
Don't get me started about those stupid light bulbs.
subject: Concurrency Issue with HSQLDB
It's not a secret anymore!