Re: Transactions: DBI vs. SQL

On 3/26/06 4:00 AM, Martin J. Evans wrote:
Tom Mornini wrote:
You need to turn AutoCommit off with:

my $dbh = DBI->connect('dbi:Pg:dbname=test', 'postgres', '...',
{ PrintError => 1, RaiseError => 0,
AutoCommit => 0 });

You should not have to turn autocommit off with begin_work:

Exactly. Even with AutoCommit initially on, that doesn't excuse the
behavior where calls to begin_work() and rollback() both appear to work and
give affirmative return values, but actually do nothing. Here's that code
extracted, but with fatal errors attached:

$dbh->begin_work or die "Could not begin work\n";
$dbh->do('INSERT INTO t1 (id, t2_id) VALUES (2, 1)');
$dbh->rollback or die "Could not roll back\n";

It sails right through that code without dying, but it doesn't actually
begin and then roll back a transaction. Here's the DBI trace of that
section of code:

-> begin_work for DBD::Pg::db (DBI::db=HASH(0x1833704)~0x1869bf0)
1 -> FETCH for DBD::Pg::db (DBI::db=HASH(0x1869bf0)~INNER 'AutoCommit')
dbdpg: dbd_db_FETCH (AutoCommit) dbh=25449128
1 <- FETCH= 1 at /usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/DBI.pm
line 1655 via new.pl line 34
1 -> STORE for DBD::Pg::db (DBI::db=HASH(0x1869bf0)~INNER 'AutoCommit' 0)
dbdpg: dbd_db_STORE (AutoCommit) (0)
1 <- STORE= 1 at /usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/DBI.pm
line 1657 via new.pl line 34
1 -> STORE for DBD::Pg::db (DBI::db=HASH(0x1869bf0)~INNER 'BegunWork' 1)
dbdpg: dbd_db_STORE (BegunWork) (1)
STORE DBI::db=HASH(0x1869bf0) 'BegunWork' => 1
1 <- STORE= 1 at /usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/DBI.pm
line 1658 via new.pl line 34
<- begin_work= 1 at new.pl line 34
-> do for DBD::Pg::db (DBI::db=HASH(0x1833704)~0x1869bf0 'INSERT INTO t1
(id, t2_id) VALUES (2, 1)')
dbdpg: pg_quickexec (INSERT INTO t1 (id, t2_id) VALUES (2, 1))
dbdpg: _sqlstate
<- do= 1 at new.pl line 36
-> rollback for DBD::Pg::db (DBI::db=HASH(0x1833704)~0x1869bf0)
dbdpg: dbd_db_rollback (AutoCommit is 0) (BegunWork is 1)
dbdpg: dbd_db_txn_status
dbdpg: Warning: invalid done_begin turned off
<- rollback= 1 at new.pl line 38

-John


.

Relevant Pages

  • Re: Transactions: DBI vs. SQL
    ... I've never used Postgres under Perl but that: ... <- rollback= 1 at new.pl line 38 ... You should not have to turn autocommit off with begin_work: ... dbdpg: dbd_db_STORE ...
    (perl.dbi.users)
  • Re: Transactions: DBI vs. SQL
    ... I am able to enact transaction processing in sybase with the DBI/DBD::Sybase combination with autocommit set to on. ... "Enable transactions (by turning AutoCommit off) until the next call to commit or rollback. ...
    (perl.dbi.users)
  • Re: Prepared Statment seems to run but doesnt affect database
    ... Autocommit is turned on. ... familiar with that function or what you mean by a rollback, ... to run just fine but when I check to see the affected rows nothing is ... [CODE BLOCK for config.php] ...
    (comp.lang.php)
  • Re: Transactions: DBI vs. SQL
    ... "Enable transactions (by turning AutoCommit off) until the next call to commit or rollback. ...
    (perl.dbi.users)