wood burning stoves 2.0*
The moose likes JDBC and the fly likes database access pattern Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "database access pattern" Watch "database access pattern" New topic
Author

database access pattern

edo picardi
Greenhorn

Joined: Feb 01, 2008
Posts: 4
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

Joined: Aug 13, 2004
Posts: 1081

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 ]

    Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
    edo picardi
    Greenhorn

    Joined: Feb 01, 2008
    Posts: 4
    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

    Joined: Dec 20, 2006
    Posts: 2491
        
        8

    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.


    OCUP UML fundamental and ITIL foundation
    youtube channel
    edo picardi
    Greenhorn

    Joined: Feb 01, 2008
    Posts: 4
    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

    Joined: Dec 20, 2006
    Posts: 2491
        
        8

    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

    Joined: Feb 01, 2008
    Posts: 4
    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
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: database access pattern
     
    Similar Threads
    Application-Managed Transaction without joining
    Identity keys, WebLogic and EJB CMP CMR
    interview question
    BMP Entity EJBs and create()
    Race condition