File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

database access pattern

 
edo picardi
Greenhorn
Posts: 4
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
     
    edo picardi
    Greenhorn
    Posts: 4
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
     
    Jan Cumps
    Bartender
    Posts: 2576
    11
    C++ Linux Netbeans IDE
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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.
     
    edo picardi
    Greenhorn
    Posts: 4
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
     
    Jan Cumps
    Bartender
    Posts: 2576
    11
    C++ Linux Netbeans IDE
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
     
    edo picardi
    Greenhorn
    Posts: 4
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic