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?

That does all seem rather odd. Can you show a complete test case and give your DBI and DBD::Pg versions? — Craig Ringer, Jun 13 '13 at 04:08

ikegami · Answer 1 · 2019-08-08T14:18:10.643

4

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.

edited Aug 08 '19 at 14:18

answered Jun 13 '13 at 04:20

ikegami

367,544
15
269
518

Seems rather unintuitive. Turn on autocommit, so I'm able to then start a manual transaction. – Michael Soulier Jun 13 '13 at 04:57
2

To be fair, it only makes sense once you realise autocommit=0 sends a BEGIN for you – Richard Huxton Jun 13 '13 at 08:15

score 4 · Answer 2 · answered Sep 19 '13 at 00:35

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.

Manual transaction management in DBD::Pg

2 Answers2