time-lag after DBI->commit

[dn.perl]

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?

 

[Dr.Ruud]

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-

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?

 

[dn.perl]

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?

 

[xhoster]

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.

 

[Peter J. Holzer]

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