Transaction handling with Oracle DBD

Hi, I am getting data corruption when I attempt to run
several processes in parallel when making changes to
two tables.

DBD version 1.16, DBI version 1.48, Linux 2.6, Oracle
9i.

I am trying to select a counter value from table 1,
insert it into table 2 and update table 1 counter
value. This comprises a transaction. I do this 1000
times in a 'for' loop. Even after I clear out the
counter value in table 1 and run the test with two
processes, I am getting a value of 3000 in the
counter!

I included the code below. I moved the connect into
the 'for' loop as I read connect automatically starts
a transaction. Please let me know if I am doing
something wrong! Thanks a lot.

#!/usr/bin/perl

use DBI;
# DB name used by Perl Script
$PS_DBNAME = "DBI:Oracle:xyz";
$DB_USERID="abc";
$DB_PASS="def";

$counterval = 0;
$i = 0;
for($i=0; $i<100; $i++)
{
my $dbh = DBI->connect(PS_DBNAME, $DB_USERID,
$DB_PASS)
or die "Couldn't connect to database: " .
DBI->errstr;
$dbh->begin_work;
eval
{
my $sth = $dbh->prepare('SELECT counter FROM
count_tab')
or die "Couldn't prepare statement: " .
$dbh->errstr;

$sth->execute()
or die "Couldn't execute statement: " .
$sth->errstr;

# there should be only one row
while (@data = $sth->fetchrow_array())
{
$counterval = $data[0];
}

$dbh->do("INSERT INTO data_tab (counter)
VALUES($counterval)")
or die "Couldn't prepare statement: " .
$dbh->errstr;

$counterval = $counterval + 1;

$dbh->do("UPDATE count_tab SET counter =
$counterval")
or die "Couldn't prepare statement: " .
$dbh->errstr;

$sth->finish;
};
if ($@)
{
$dbh->rollback;
}
else
{
$dbh->commit;
}
$dbh->disconnect;
}
exit 0;

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
.

Relevant Pages

  • Re: Is user-space AIO dead?
    ... Lots of sequential small-ish writes (call each quanta a transaction) ... This works very well and can easily max out any real device's bandwidth, but incurs more latency ... Do You Yahoo!? ... Mail has the best spam protection around ...
    (Linux-Kernel)
  • Re: Hopefully, kmalloc() will always succeed, but if it doesnt then....
    ... I have seen the same case at many places when allocating in a loop. ... Do You Yahoo!? ... Mail has the best spam protection around ...
    (Linux-Kernel)
  • Re: redhat-config-packages
    ... Mounting the iso on loop gives the error ... where /redhat is mounted as loop of first 3 iso image. ... Do You Yahoo!? ... Mail has the best spam protection around ...
    (RedHat)
  • How to let a loop run for a while before checking for break condition?
    ... I'm afraid Claudio Grondi can't use your solution, ... How about splitting your loop in two? ... Do You Yahoo!? ... Mail has the best spam protection around ...
    (comp.lang.python)
  • perl built in function for mean
    ... to let the job done by using a loop. ... if Perl has the short-cut/built-in function for this. ... Do You Yahoo!? ... Mail has the best spam protection around ...
    (perl.beginners)