Manual transaction management in DBD::Pg

Question

I'm having a hard time getting manual transactions to work as documented in DBD::Pg, or I'm simply misunderstanding said documentation.

My understanding is that if I want to manually manage transactions, I should turn off AutoCommit.

$dbh->{AutoCommit} = 0;
$dbh->begin_work;

But when I do this, I get continual errors

DBD::Pg::db begin_work failed: Already in a transaction

To get this to work, I need to turn on AutoCommit first.

$dbh->{AutoCommit} = 1;
$dbh->begin_work;

But that doesn't seem to agree with any of the documentation.

Am I simply misunderstanding it?

Answer 1

My understanding is that if I want to manually manage transactions, I should turn off AutoCommit.

Correct.

However, DBD::Pg automatically starts your transactions for you. You can't start the transactions manually. Your best option is to leave autocommit off and then just do:

 $dbh->commit;

when you are ready to commit. This will both commit the existing transaction and start a new transaction.

Now if you set autocommit to on, then anything that exists outside of a transaction becomes its own transaction, one transaction per statement. If you want to be assured of manually managing transactions, you want to set it off.

Answer 2

My understanding is that if I want to manually manage transactions, I should turn off AutoCommit.

No, quite the opposite. Setting AutoCommit to 0 starts a transaction, so you want to set it to 1. To have your changes committed automatically (AutoCommit => 1) is to have the database not use transactions, which is the opposite of what you want.