Re: Transaction handling with Oracle DBD

On 04/23/2005 08:30 AM, RKris said:

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'll restate that to make sure I understand. You are running two processes that read and update the same tables. You expect them to each loop 1000 times which would make the counter 2000, but you get a counter value of 3000 instead.

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.

You did not include the code. Your connect would have failed immediately and the loop counter you give below is 100 instead of 1000.

Saying that connect() restarts a transaction understates what it does by a long shot. It establishes an entirely new connection to the database.

The documentation for DBI and DBD::Oracle are available at CPAN.

http://search.cpan.org/dist/DBI/DBI.pm

http://search.cpan.org/~timb/DBD-Oracle/Oracle.pm

#> #!/usr/bin/perl

#!/usr/bin/perl -w
use strict;

# It's hard to have too much error checking.

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

# The connect() definitely does not belong inside the loop.
# Further, you didn't turn off autocommit in your example, so
#   each INSERT or UPDATE statement gets committed immediately.
my $dbh = DBI -> connect( $PS_DBNAME, $DB_USERID, $DB_PASS,
   { AutoCommit => 0, RaiseError => 1, PrintError => 0 } )
   or die "Couldn't connect to database: $DBI::errstr\n";

# You should also prepare all your statements outside the loop.
# I set RaiseError so individual error checks aren't needed.
my $sth  = $dbh -> prepare( 'SELECT counter FROM count_tab' );
my $sthI = $dbh -> prepare(
   "INSERT INTO data_tab (counter) VALUES (?)" );
my $sthU = $dbh -> prepare( "UPDATE count_tab SET counter = ?" );

$counterval = 0;
$i = 0;

#> for($i=0; $i<100; $i++)

for ( $i = 1; $i < 1000; ++$i )

{ 
#>    my $dbh = DBI->connect(PS_DBNAME, $DB_USERID,
#> $DB_PASS)
#>       or die "Couldn't connect to database: " .
#> DBI->errstr;
#>    $dbh->begin_work;

# begin_work isn't needed in this case.

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];
#>    }

   # If you only want one row, just fetch one.
   $counterval = $sth -> fetchrow_array();
   $sthI -> execute( $counterval );

#>    $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;

   $sthU -> execute( $counterval );

$sth -> finish; 

# Finish is not needed here since you immediately
# reexecute the statement.
# It might be useful immediately before the disconnect
# to avoid a complaint about an open statement.

};
if ($@) {
$dbh->rollback; 

      # eval{} keeps error messages from being written to STDOUT,
      # so pass the error message along.
      die $@;

}
else {
$dbh->commit;
}
#> $dbh->disconnect;
} 

# Connecting and disconnecting are both very expensive,
# so don't do them inside a data loop.
$sth -> finish if $sth;
$dbh -> disconnect;

exit 0; 

--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
.

Relevant Pages

  • 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: Transactions - Infinite Loops
    ... This still doesn't sound like an infinite loop. ... > It was an unusual SQL Server response that we were unable to duplicate. ... > transaction if the wrong number of felds are being updated (i.e., ... > the query ran okay. ...
    (microsoft.public.sqlserver.programming)
  • Re: Transactions - Infinite Loops
    ... If you're really in a loop, you will not be able to enter any more commands. ... If you just forgot to commit, SQL Server cannot tell you that, as it has no ... of the same transaction. ... COMMIT TRAN. ...
    (microsoft.public.sqlserver.programming)
  • Re: SqlDataReader performance
    ... so its best to get the ordinalbefore the loop begins. ... The query performes acceptable in the Query analyzer. ... As you can see most of the time is spendt getting data from the DataReader ... What effect has a transaction on this? ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: "cant open any more tables"
    ... In a perfect world you would be right: all objects would dereference ... use them in a loop or in a query, ... Assign the workspace variable, then the transaction, ... If you do need to close a recordset, ...
    (microsoft.public.access.modulesdaovba)