Re: Transaction handling with Oracle DBD



Personally, I would rewrite the sql to perform inside of do's

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

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

However, the OP is probably testing out the waters and eventually wants to use the counter in some manipulation in perl. In that case,

1. Move the connect statement outside of the loop, you are unnecessarily creating and destroying it. This will speed execution of the code immeasurably. (well its measurable, but don't bother)
2. Since you are executing the same statements in a loop, consider using a prepared statement. This will also speed the code as you are not compiling the sql each time.
3. Batching the transactions together into groups of 100 or more may add more speed to the operation. This is because the overhead of the update to the one table can be resolved by the server's transaction manager and only the final update of the batch applied. Also, servers work better when you are doing something over and over and it can be done is a result set oriented manner as opposed to one row/transaction at a time.

Following code is untested! YMMV, UAYOR!

#!/usr/bin/perl

use DBI;
# DB name used by Perl Script
$PS_DBNAME = "DBI:Oracle:xyz";
$DB_USERID="abc";
$DB_PASS="def";
my $dbh = DBI->connect(PS_DBNAME, $DB_USERID, $DB_PASS)
or die "Couldn't connect to database: " . DBI->errstr;

my $sel_stmt = $dbh->prepare( "SELECT counter FROM count_tab" )
or die "Couldn't prepare statement: " . $dbh->errstr;

my $ins_stmt = $dbh->prepare( "INSERT INTO data_tab (counter) VALUES(?)")
or die "Couldn't prepare statement: " . $dbh->errstr;

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

$counterval = 0;
$i = 0;
for($i=0; $i<100; $i++)
{
$dbh->begin_work;
eval
{

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

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

$ins_stmt->execute( $counterval )
or die "Couldn't execute insert statement: " . $dbh->errstr;

$counterval = $counterval + 1;

$upd_stmt->execute($counterval)
or die "Couldn't execute update statement: " . $dbh->errstr;
};
if ($@)
{
$dbh->rollback;
}
else
{
$dbh->commit;
}
}
$dbh->disconnect;
exit 0;


Your Friendly Neighborhood DBA,

Chuck


-----Original Message-----
From: RKris <caissark@xxxxxxxxx>
To: dbi-users@xxxxxxxx
Sent: Sat, 23 Apr 2005 08:30:56 -0700 (PDT)
Subject: 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: ADO.NET Transaction
    ... Can you tell me more about your "bad" experience of client transactions I am ... Hitchhiker's Guide to Visual Studio and SQL Server ... I need to execute several Stored Procedures under one transaction. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Recordset.AddNew and the recordset objects data retaining
    ... You may be right about the transaction. ... round trip per each loop. ... with one database update trip PER RECORD is catastrophic. ... paging issues to keep the memory resources under control if you need better ...
    (microsoft.public.data.ado)
  • Re: Newbie: VB-ADO help
    ... I Do have some SQL knowledge and because the calculations are a bit ... the first loop ... are too numerous for memory storage. ... I do not want to deal with SQL and the like. ...
    (microsoft.public.vb.general.discussion)
  • Re: Straight SQL always put perform PL/SQL?
    ... If you cannot do it in a single SQL Statement, ... end loop; ... analyze table test1 compute statistics; ...
    (comp.databases.oracle.server)
  • Invalid cursor state on second $sth->execute
    ... I have written that a script that opens a cursor on a table in a DB2 database then processes each record in the result set inside a loop. ... In the course of the loop I prepare and execute another SQL statement to get a maximum value from the same table for a given condition. ... I then decided that to speed up the script I would prepare this lookup SQL outside the loop using placeholders, so I can just re-execute the same SQL with the different values plugged in each time. ...
    (perl.beginners)