Categories
Uncategorized

Class::DBI, PostgreSQL and Transactions

Once again, I managed to see the dreaded ”<IDLE> in transaction” message from PostgreSQL today. This means that I have a connection to the database open, which is currently inside a transaction. This is bad because locks are held open in a transaction. So, what to do about it? Firstly, examine the documentation.

  1. <a href="http://search.cpan.org/dbdpg/DBD-Pg-1.43/Pg.pm#Transactions”>DBD::Pg on transactions.
  2. <a href="http://search.cpan.org/timb/DBI-1.48/DBI.pm#Transactions”>DBI on transactions.
  3. <a href="http://search.cpan.org/tmtm/Class-DBI-v3.0.4/lib/Class/DBI.pm#TRANSACTIONS”>Class::DBI on transactions (which I’m amused to see that I contributed when I first came across this problem several years ago)

Now, the application I’m working on at present has no particular need for transactions for the most part. So it would definitely be easier to turn on <a href="http://search.cpan.org/timb/DBI-1.48/DBI.pm#AutoCommit”>AutoCommit and only drop into a transaction when I absolutely have to.

What do I know so far?

  • AutoCommit defaults to on.
  • But Class::DBI disables it by default for PostgreSQL.
  • My application is running as part of an HTML::Mason web page.
  • When I make any call that goes near the database on a Class::DBI object, DBD::Pg executes an implicit BEGIN before the SQL that it has been sent executes. Only the first time though. This is because DBD::Pg is a database where a transaction must be explicitly started. DBI says the driver will automatically begin an explicit transaction when AutoCommit is turned off.
  • In a nutshell, I need to get AutoCommit turned on until I need it.

There, wasn’t that simple?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s