• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

database access pattern

 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • 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
     
    Bartender
    Posts: 2661
    19
    Netbeans IDE C++ Linux
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • 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
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • 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: 2661
    19
    Netbeans IDE C++ Linux
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • 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
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • 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
     
    reply
      Bookmark Topic Watch Topic
    • New Topic