database access pattern

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

Hi edo,

Welcome to JavaRanch.

Did you look at java-docs for Connection class. connection.prepareStatement has an answer for you. This will also help you to avoid reading SEQUENCE.CURRVAL from database after every insert.

Please check the URL if you are using preparedStatement

http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Connection.html#prepareStatement(java.lang.String,%20int)

This states

public PreparedStatement prepareStatement(String sql,
int autoGeneratedKeys)
throws SQLException

Parameters:
sql - an SQL statement that may contain one or more '?' IN parameter placeholders
autoGeneratedKeys - a flag indicating whether auto-generated keys should be returned; one of Statement.RETURN_GENERATED_KEYS or Statement.NO_GENERATED_KEYS

If you are using statement then you can visit

http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Statement.html#executeUpdate(java.lang.String,%20int)

Note

These method are available JDK 1.4 onwards

I haven't practically tested this concept

Thanks,
Shailesh Chandra
[ February 05, 2008: Message edited by: Shailesh Chandra ]

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

Just one more question... is the use of autogenerated keys thread-safe?

Yes. No two records will receive the same key.

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

No. The key generation algorithm is database dependent. Many databases don't guarantee that the keys are given in sequence, without gaps.
Even if you don't us multithreading.

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

I want to avoid that the two threads read the same key, while the ids of the rows they inserted are obviously different!

How do you read those keys? If done properly, this should not be an issue. As long as you don't select the max value for that column to get the generated keys, you will be fine. Look for Statement.getGeneratedKeys().

Regards, Jan

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