time-lag after DBI->commit

Discussion in 'Perl Misc' started by dn.perl@gmail.com, Nov 8, 2011.

  1. Guest

    I run a script which inserts (say) 50,000 records in a Oracle table
    which had 40,000 records to begin with, but the records show up
    gradually over a period of 20-30 seconds.
    select count(*) from my_table ==> 40,000 records, before running the
    script.
    Run the script, within which I do $dbh->insert-50k-rows, $dbh-
    >commit, the script exits.


    02 seconds after the script exits, select count(*) from my_table ==>
    40,000 records or 40,050 records
    10 seconds after the script exits, select count(*) from my_table ==>
    approx 70,000 records
    40 seconds after the script exits, select count(*) from my_table ==>
    90,000 records

    What exactly is happening here? I would expect ($dbh->commit) to take
    40-50 seconds if that is what it takes, but the moment the perl script
    exits, in my oracle client, I should see 90,000 rows in the table; no?
    Why the delay?
    , Nov 8, 2011
    #1
    1. Advertising

  2. Dr.Ruud Guest

    On 2011-11-08 07:48, wrote:

    > I run a script which inserts (say) 50,000 records in a Oracle table
    > which had 40,000 records to begin with, but the records show up
    > gradually over a period of 20-30 seconds.
    > select count(*) from my_table ==> 40,000 records, before running the
    > script.
    > Run the script, within which I do $dbh->insert-50k-rows, $dbh-
    >> commit, the script exits.

    >
    > 02 seconds after the script exits, select count(*) from my_table ==>
    > 40,000 records or 40,050 records
    > 10 seconds after the script exits, select count(*) from my_table ==>
    > approx 70,000 records
    > 40 seconds after the script exits, select count(*) from my_table ==>
    > 90,000 records
    >
    > What exactly is happening here? I would expect ($dbh->commit) to take
    > 40-50 seconds if that is what it takes, but the moment the perl script
    > exits, in my oracle client, I should see 90,000 rows in the table; no?
    > Why the delay?


    Show the code. Where is your $dbh->begin_work() call?

    --
    Ruud
    Dr.Ruud, Nov 8, 2011
    #2
    1. Advertising

  3. Guest

    One correction: The database in question is MySQL, not Oracle.

    I run a script which inserts (say) 50,000 records in a MySQL table
    which had 40,000 records to begin with, but the records show up
    gradually over a period of 20-30 seconds.
    select count(*) from my_table ==> 40,000 records, before running the
    script.
    Run the script, within which I do
    $dbh->insert-50k-rows,
    then, $dbh->commit,
    then the script exits.

    02 seconds after the script exits, select count(*) from my_table ==>
    40,000 records or 40,050 records
    10 seconds after the script exits, select count(*) from my_table ==>
    approx 70,000 records
    40 seconds after the script exits, select count(*) from my_table ==>
    90,000 records

    What exactly is happening here? I would expect ($dbh->commit) to take
    40-50 seconds if that is what it takes, but the moment the perl script
    exits, in my MySQL client, I should see 90,000 rows in the table; no?
    Why the delay?
    , Nov 8, 2011
    #3
  4. Guest

    "" <> wrote:
    > One correction: The database in question is MySQL, not Oracle.


    Well, there is a big difference. Which storage engine are you using with
    MySQL? Not one of the transactional ones, I bet.


    > What exactly is happening here? I would expect ($dbh->commit) to take
    > 40-50 seconds if that is what it takes, but the moment the perl script
    > exits, in my MySQL client, I should see 90,000 rows in the table; no?
    > Why the delay?


    It sounds like you are expecting transactional behavior from a
    non-transactional application.

    Xho

    --
    -------------------- http://NewsReader.Com/ --------------------
    The costs of publication of this article were defrayed in part by the
    payment of page charges. This article must therefore be hereby marked
    advertisement in accordance with 18 U.S.C. Section 1734 solely to indicate
    this fact.
    , Nov 9, 2011
    #4
  5. On 2011-11-09 01:45, <> wrote:
    > "" <> wrote:
    >> One correction: The database in question is MySQL, not Oracle.

    >
    > Well, there is a big difference. Which storage engine are you using with
    > MySQL? Not one of the transactional ones, I bet.


    And is there replication or a cluster involved? I don't see how that
    behaviour could arise even with MyISAM on a single machine: The insert
    returns only when it is complete, so when the script exits all 50'000
    inserts are complete and a select count(*) should show them.

    But I would expect that behaviour in a replicated environment where
    inserts go to a master and selects to a slave. MySQL replication is
    asynchronous, so the inserts show up on the slave some time later than
    on the master (and since MyISAM doesn't have transactions, each row
    shows up individually; with Innodb all rows would become visible on the
    slave only once the commit was replicated).

    hp
    Peter J. Holzer, Nov 12, 2011
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. JM
    Replies:
    7
    Views:
    563
    sp3d2orbit
    Nov 2, 2005
  2. DC

    Netbeans 4.1 lag time

    DC, Sep 8, 2005, in forum: Java
    Replies:
    3
    Views:
    542
  3. John Nagle
    Replies:
    4
    Views:
    9,894
    John Nagle
    Feb 4, 2008
  4. carmelo
    Replies:
    7
    Views:
    1,004
    Arved Sandstrom
    May 18, 2010
  5. Qubert
    Replies:
    6
    Views:
    131
    Martin DeMello
    Jul 12, 2003
Loading...

Share This Page