edo picardi

Ok, thanks a lot! If each statement can get its generated key the interleaving should not be a problem.. I'll do some tests

..Moreover, this shouldn't need transactions, hence better performance :-)

Thanks again,

edo

Yes, thanks... I guessed it depends on the DB in use.
Is there any general way to solve this problem? I want to avoid that the two threads read the same key, while the ids of the rows they inserted are obviously different!
Do you think that use of transactions could solve the problem? For instance, each thread could start a transaction with isolation level=serializable...

TIA

Thanks a lot, i had a look at the java doc you suggested
Just one more question... is the use of autogenerated keys thread-safe?

In other words, what happens if 2 threads run the method containing insert/get generated key at the same time? I want to avoid interleaving of statements like this:

thread 1: insert
thread 2: insert
thread 1: get generated key
thread 2: get generated key


Thanks in advance

Hi everybody,

while writing a multithreaded UI application to access a DB I came up with a question I haven't managed to answer yet... please have a look, any hint would be greatly appreciated :-)

I have a DB table: Customer(ID, name, surname, ...) where ID is a primary key automatically inserted by the DBMS (e.g. autoincrement SQL Server or sequence Oracle).

I wrote a data access object that maps DB rows into entity objects (es. Customer.java, Order.java, etc.). The point is.. since the ID is automatically inserted by the DBMS, after inserting a new item I have to query the table again in order to get the ID of the newly inserted item

...so far so good... the problems come when i have a multithreaded scenario. Say I want to insert a new 'customer' into the DB, I write the method:

//returns ID of the Customer inserted
public int addCustomer(Customer customer){

//operation 1 - insert Customer into DB

//operation2 - read ID of the last inserted customer
//(e.g. in Oracle this can be obtained with SEQUENCE.CURRVAL)

return ID;

}//end addUser

the problem is... what if 2 threads run the method addCustomer at the same time? This is a disaster in case of interleaving of the statements like this:

thread 1: operation 1
thread 2: operation 1
thread 1: operation 2
thread 2: operation 2

.. as you can see, in this scenario the two threads read the same value of the ID, that is to say "thread 1" gets the wrong value.

I'm aware there are a few work-arounds, but I'm actually looking for a general solution. I also tried to define a transaction but it didn't work fine, apparently the threads that find a previous transaction running don't wait for it to finish and just die...

Besides, I don't want to be too strict and I'd like to avoid declaring the method addUser as synchronized since the application has to take full advantage of DB's parallelism...

Any hint?

TIA, any suggestion would be really appreciated

Edo