File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
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 Head First Android this week in the Android 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
jQuery in Action, 3rd edition
subject: Concurrency Issue with HSQLDB
It's not a secret anymore!