Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2002
    Posts
    37

    Question Unanswered: Selects during a Transaction

    Hello,

    When coding a web application, I always try to retrieve only one database connection from the connection pool per request.

    but now I'm in an situation where I need help and advice, I've never coded that before and think that I'm making a mistake :

    the app. runs in a cluster environment with 2 nodes. on every node there's a thread running every 20 seconds. when woke up, the threads select records from a table, say Table A, with a 'for update' clause at the end.
    /*** in Java ***/
    Connection cn = getConnectionFromPool();
    cn.setAutocommit(false);
    List records = getListFromTableAWithForUpdateClause(cn);
    //.. do some stuff with the list
    for (int i = 0; i < records.size(); ++i) {
    String xField = getXFieldFromTableB(cn); // WOULD THIS BE AN ERROR ?
    // do something with xField
    updateStatusFieldOfTableA(cn, ((Person) record.get(i)).getKey(), STATUS_HANDLED);
    cn.commit();
    }
    cn.setAutocommit(true);
    // close connection in finally etc..

    To retrieve xField from Table B using the same connection, would this be an error ?

    Since I have to select the records with an for-update clause (already selected records shouldn't be selected by the other thread running on the other node), do I have to worry about the transaction level of the connection the connection pool returned to me ?

    Note : the database is Oracle 10g, app server is Websphere 6.1.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by msegmx View Post
    Since I have to select the records with an for-update clause (already selected records shouldn't be selected by the other thread running on the other node)
    FOR UPDATE will not prevent other sessions to read those rows!

  3. #3
    Join Date
    Oct 2002
    Posts
    37
    thanks for you answer. and sorry, couldn't reply immediately.

    Quote Originally Posted by shammat View Post
    FOR UPDATE will not prevent other sessions to read those rows!

    but to establish this behavior, the other session has to select with a transaction level of commited read, right ?

    1) is that correct ?
    2) if yes, in DB2, one can add a "with ur" clause at the end of the select statement. is there an equivalent clause in Oracle ?
    3) another solution would be to use the setTransactionIsolation() method of java.sql.Connection interface, but I don't want to rely on this solution. Especially, when getting the connection from pool of an AppServer (in this cas WAS 6.1), since it could be an implementation specific behavior, but i have to be sure on this.

    i have little experience with all that, and rather theoretically and hence need some guidance.

    What would your advice be ?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by shammat View Post
    FOR UPDATE will not prevent other sessions to read those rows!
    It will if the other sessions are also using the FOR UPDATE clause, which I think is the intention here. You'd want to use SKIP LOCKED to avoid simply waiting (or raising exception) for locked rows.

  5. #5
    Join Date
    Oct 2002
    Posts
    37
    Quote Originally Posted by andrewst View Post
    It will if the other sessions are also using the FOR UPDATE clause, which I think is the intention here. You'd want to use SKIP LOCKED to avoid simply waiting (or raising exception) for locked rows.
    Thank you very much

    FOR UPDATE SKIP LOCKED did the trick

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by msegmx View Post
    2) if yes, in DB2, one can add a "with ur" clause at the end of the select statement. is there an equivalent clause in Oracle ?
    No.
    Oracle will never allow to read uncommitted data.

    It will if the other sessions are also using the FOR UPDATE clause
    Correct, I forgot to mention that.

  7. #7
    Join Date
    Oct 2002
    Posts
    37
    I really appreciate you support, thanks.

    I have to ask this again;

    1- cn.setAutocommit(false);
    2- // "select .. for update skip locked"
    3- // perform another select using the same connection
    4- // perform an update "update tablename set column where ID = ?"
    5- cn.commit();
    6- cn.setAutocommit(true);

    the code at line 3, would it cause trouble ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •