Professional Web Applications Themes

DB2 commit synchronization - IBM DB2

Hello, I am developing a java application (client-server), that is supposed to support lots of users (over 100). I am encoutering a problem with DB2 commit statement. There is an portion of code on the server side, that needs to be thread safe. Therefore I've made that method synchronized. Inside that method, i have to do an "select max(some_id) from .... " and imediately after i do an "insert some_id+1 ...". I close the connection (it is obtained through JNDI datasource), and exit the method. However, if multiple clients try to access that method at the same time, they are ...

  1. #1

    Default DB2 commit synchronization

    Hello,
    I am developing a java application (client-server), that is supposed
    to support lots of users (over 100). I am encoutering a problem with
    DB2 commit statement.

    There is an portion of code on the server side, that needs to be
    thread safe. Therefore I've made that method synchronized. Inside that
    method, i have to do an "select max(some_id) from .... " and
    imediately after i do an "insert some_id+1 ...". I close the
    connection (it is obtained through JNDI datasource), and exit the
    method.
    However, if multiple clients try to access that method at the same
    time, they are put on hold until one of them terminates (witch is the
    behaviour i expect). But, if a very short time passes between 2 runs
    in that method (less than 20ms), the second client, when does the
    "select max(some_id) ..." it gets the old id, not the one updated by
    the previous client. I have tried in many ways to get the damn thing
    working (setted autocommit false, and just before closing doing an
    manual commit), but it seems that the JDBC driver (at least) is
    returning from the commit method, and not actually doing the commit
    (if i wait 30ms everything is fine, but this is not what i want). I'm
    sure i can find a workaround for exactly this situation (static
    variables,etc.) but what will I do if i encounter a situation in
    witch this workaround doesn't apply?

    Is there any way to force DB2 to make a commit when i tell it so, and
    not to return from the statement until everything is written on the
    disk?

    I'm using DB2 v7.1 on an RedHat 7.3 machine with an ext3
    filesystem(dual proc ... etc.).

    Thanks,
    Sergiu.
    sergiu Guest

  2. #2

    Default Re: DB2 commit synchronization

    Inline...

    sergiu wrote:
     
    By server side, do you mean that this is a trusted java stored proc? If
    not, can you elaborate?
     
    Where is the commit driven?
     
    DB2 will always commit when a commit statement is driven. :-) You might
    want to try running with a statement event monitor to see the order of
    events...maybe your reader is getting onto the table before the insert
    is driven (might want to check your cursor stability settings too).
     

    Sean Guest

  3. #3

    Default Re: DB2 commit synchronization

    Thanks for your reply.
    In order to make the things a little bit clearer, i'll post an example
    code:

    public class SomeClass{

    //so that only one client can enter at a time
    public synchronized static void sampleMethod(DataSource ds){
    Connection con=null;
    try{
    con=ds.getConnection();
    con.setAutoCommit(false);//i'll do the commit myself
    Statement stm1=con.createStatement();
    ResultSet rs=stm1.executeQuery("select max(some_id) as id
    from X");
    int id=0;
    if(rs.next()){
    id=rs.getInt("id");
    }
    System.out.println("selected id is:"+id);
    id++;
    rs.close();
    stm1.close();
    Statement stm2=con.createStatement();//why do i need a second
    statement?
    stm2.executeUpdate("insert into X values("+id+")");
    stm2.close();
    con.commit();//save the changes in the database
    //here I expect that it doesn't exit from the commit()
    method, until
    //everything is written on the disk
    }catch(SQLException e){
    e.printStackTrace();
    }
    finally{
    try{con.close();}catch(Exception ignored){}
    }
    }
    }

    This code is part from a class in an mostly-jsp application , that is
    running on WebSphere 5.0, with that DB2 backend. the method is called
    from within an jsp.

    OK, and here's what's hapening:
    Two clients click a button that leads to the execution of this code at
    the same time (exactly the same time. the method takes very few ms to
    complete).
    One enters the method, makes the select, and the insert,
    commits,closes and exists the method. Immediately the second one ,
    enters too in the method, but ... surprise, the id that the select
    returns to him is the same that the first one got.It seems like the
    insert statement didn't executed (even though no exception was
    thrown).
    Very strange behaviour. and ... whats even worse, it's pretty hard to
    te. It doesn't happen all the time.
    Now ... i'm not DBA, i'm just an programmer, and I will talk with the
    DBA to make any adjustements to the database that are needed to
    prevent this thing from happening, even if that means to slow down the
    response of the application. I really need that commit() method to
    work.
    If you have any ideeas what could be wrong (DB2 conf, my code,
    anything), please post them.


    Thank you,
    Sergiu.
    sergiu Guest

  4. #4

    Default Re: DB2 commit synchronization

    "sergiu" <ro> wrote in message
    news:google.com... 

    Try adding FOR UPDATE to the end of the select statement. This will place a
    lock on the row for another execution of the same statement, so that the
    other execution will wait until the current one is finished.


    Mark Guest

  5. #5

    Default Re: DB2 commit synchronization

    Sergiu,

    Probably the insert statement hasn't occured at the point of the second
    scan. Since this is a jsp on v7 each invocation of the procedure is
    happening in a seperate edu/jvm. Thus, the java sycronization method
    does not come into play...you need an 'sql' mechanism to syncronize,
    such as what Mark suggests.

    sergiu wrote: 

    Sean Guest

  6. #6

    Default Re: DB2 commit synchronization

    ro (sergiu) writes: 
    [...] 

    I don't know if this will work in a java stored procedure, but nasty stuff
    like this should be wrapped in a BEGIN ATOMIC/END ATOMIC pair. *
    --
    * PV something like badgers--something like lizards--and something
    like corks.
    Paul Guest

  7. #7

    Default Re: DB2 commit synchronization

    >Since this is a jsp on v7 each invocation of the procedure is 
    Well, i think that Websphere doesn't create a new jvm for each client
    that is using my application (since is only one application, and with
    more than 100 clients would be a waste of resources), and thats why i
    declared the method static, to be sure that the synchronized part does
    come into play. And, i've printed some System.currentTimeMillis() at
    the end and the beggining of the method, and indeed, one client exits
    the method at 1064994600000 and the next client enters the method at
    1064994600001 (for example) . I have done a workaround for this thing
    at one time, but now I'm confronted with a similar situation, but
    where i cannot apply my previous workaround.
    Yes, i think that you are right, probably the sync should be done at
    sql level.

    But, anyway, this is a very strange thing happening, but probably is
    determined by other factors (the ext3 linux jounalized filesystem, or
    the stars movement or the war in Irak :) (just joking) ), and probably
    it isn't DB2's or the java driver's fault.

    I will try to come up with a method for synchronizing at the sql level
    (FOR UPDATE, BEGIN/END ATOMIC, or anything else), to see if there will
    be any difference.

    Thank you all for your time and ideeas.
    Sergiu.
    sergiu Guest

  8. #8

    Default Re: DB2 commit synchronization

    If it's a java stored procedure or udf each connection has its own
    jvm...really...

    sergiu wrote: 
    >
    > Well, i think that Websphere doesn't create a new jvm for each client
    > that is using my application (since is only one application, and with
    > more than 100 clients would be a waste of resources), and thats why i
    > declared the method static, to be sure that the synchronized part does
    > come into play. And, i've printed some System.currentTimeMillis() at
    > the end and the beggining of the method, and indeed, one client exits
    > the method at 1064994600000 and the next client enters the method at
    > 1064994600001 (for example) . I have done a workaround for this thing
    > at one time, but now I'm confronted with a similar situation, but
    > where i cannot apply my previous workaround.
    > Yes, i think that you are right, probably the sync should be done at
    > sql level.
    >
    > But, anyway, this is a very strange thing happening, but probably is
    > determined by other factors (the ext3 linux jounalized filesystem, or
    > the stars movement or the war in Irak :) (just joking) ), and probably
    > it isn't DB2's or the java driver's fault.
    >
    > I will try to come up with a method for synchronizing at the sql level
    > (FOR UPDATE, BEGIN/END ATOMIC, or anything else), to see if there will
    > be any difference.
    >
    > Thank you all for your time and ideeas.
    > Sergiu.[/ref]

    Sean Guest

  9. #9

    Default Re: DB2 commit synchronization

    Sean McKeough <ca.ibm.com> wrote in message news:<blelin$sqc$torolab.ibm.com>... 
    At what connection are you reffering to: java-DB2 connection or
    browser-jsp server connection?
    Let's take those two connections into consideration:
    1. browser-websphere (jsp server) connection:
    From my tests, when 2 clients click the submit button (the one
    that leads to the execution of that code) at the same time, one of
    them waits patiently for the first one (who got lucky) to finish
    executing the method and only after that it enters the method (20-30
    ms distance) and executes it. This (IMO) means that the synchronized
    mechanism in java comes into play. And is only one jvm that controles
    this. Witch is natural.
    2. java-DB2 connection:
    OK, the DB2 connection creates another jvm to handle the
    connection to the database (strange,hard to believe, but if u say so
    .... ). Then, why the con.commit() method returns before it completes
    its job? even if there is another jvm ... it's still not normal.


    And ... i'm not using any stored procedures or anything like this. I'm
    only using the JNDI service that Webshere provides me (configured
    through a locally installed client). The sample code that I provided
    is very close to the truth (I've changed very little in it, such as
    tables names, variable names, etc.), so ...
    What problem could i have? I would be more than happy to ask my DBA to
    provide me with a configuration text of DB2, the system, or anything
    else, if that can help you.
    Thank you for your time,
    sergiu.
    sergiu Guest

  10. #10

    Default Re: DB2 commit synchronization

    Sorry, I'm not very familliar with the WAS stuff...for pure db2
    connections, on v7, you have 1 jvm per concurrently running java routine
    (this has been changed in v8 with the addition of threaded routine support).

    Maybe you have some sort of WAS connection pooling issue going on here?
    Again, my suggestion would be to run with a statement event monitor and
    see the order of operations as they are appearing at the database...good
    luck!

    sergiu wrote: 
    >
    > At what connection are you reffering to: java-DB2 connection or
    > browser-jsp server connection?
    > Let's take those two connections into consideration:
    > 1. browser-websphere (jsp server) connection:
    > From my tests, when 2 clients click the submit button (the one
    > that leads to the execution of that code) at the same time, one of
    > them waits patiently for the first one (who got lucky) to finish
    > executing the method and only after that it enters the method (20-30
    > ms distance) and executes it. This (IMO) means that the synchronized
    > mechanism in java comes into play. And is only one jvm that controles
    > this. Witch is natural.
    > 2. java-DB2 connection:
    > OK, the DB2 connection creates another jvm to handle the
    > connection to the database (strange,hard to believe, but if u say so
    > ... ). Then, why the con.commit() method returns before it completes
    > its job? even if there is another jvm ... it's still not normal.
    >
    >
    > And ... i'm not using any stored procedures or anything like this. I'm
    > only using the JNDI service that Webshere provides me (configured
    > through a locally installed client). The sample code that I provided
    > is very close to the truth (I've changed very little in it, such as
    > tables names, variable names, etc.), so ...
    > What problem could i have? I would be more than happy to ask my DBA to
    > provide me with a configuration text of DB2, the system, or anything
    > else, if that can help you.
    > Thank you for your time,
    > sergiu.[/ref]

    Sean Guest

Similar Threads

  1. rollback or commit?
    By kt03 in forum Coldfusion - Getting Started
    Replies: 6
    Last Post: October 25th, 08:27 PM
  2. database commit
    By KJLTerry in forum Coldfusion Database Access
    Replies: 1
    Last Post: March 4th, 09:12 PM
  3. Commit ... Rollback in Access
    By Astra in forum ASP Database
    Replies: 0
    Last Post: April 30th, 11:41 AM
  4. Commit Control
    By Marco in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 5th, 10:36 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139