Re: High CPU load on Postgres Server during Peak times!!!!

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Gerhard Wiesinger wrote:
Hello Craig,

Are you sure this is correct?

The test program (see below) with autocommit=0 counts up when an insert is done in another session and there is no commit done.

I think with each new select a new implicit transaction is done when no explicit "BEGIN" has been established.

Sorry, I should have been more specific.  A transaction starts when you do something that will alter data in the database, such as insert, update, alter table, create sequence, and so forth.  The Perl DBI won't start a transaction for a select.

But my basic point is still valid: Some languages like Perl can implicitely start a transaction, so if programmers aren't familiar with this behavior, they can accidentally create long-running transactions.

Craig


Can one confirm this behavior?

Thnx.

Ciao,
Gerhard

# Disable autocommit!
my $dbh = DBI->connect($con, $dbuser, $dbpass, {RaiseError => 1, AutoCommit=>0}) || die "Unable to access Database '$dbname' on host '$dbhost' as user '$dbuser'. Error returned was: ". $DBI::errstr ."";

my $sth = $dbh->prepare('SELECT COUNT(*) FROM employee;');

for (;;)
{
  $sth->execute();
  my ($count) = $sth->fetchrow();
  print "count=$count\n";
  $sth->finish();
#  $dbh->commit;
  sleep(3);
}

$dbh->disconnect;

--
http://www.wiesinger.com/


On Thu, 24 Sep 2009, Craig James wrote:

Dave Dutcher wrote:
You need a COMMIT for every BEGIN.  If you just run a SELECT statement
without first beginning a transaction, then you should not end up with a
connection that is Idle in Transaction. If you are beginning a transaction,
doing a select, and then not committing, then yes that is a bug.

The BEGIN can be hidden, though. For example, if the application is written in Perl,

$dbh = DBI->connect($dsn, $user, $pass, {AutoCommit => 0});

will automatically start a transaction the first time you do anything. Under the covers, the Perl DBI issues the BEGIN for you, and you have to do an explicit

$dbh->commit();

to commit it.

Craig



--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance




--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux