From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Erik Wasser <erik.wasser@iquer.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Are long term never commited SELECT statements are a
Date: Thu, 21 Jul 2005 17:21:05 +0300 (EEST)
Message-ID: <Pine.LNX.4.44.0507211655210.2935-100000@matrix.gatewaynet.com> (raw)
In-Reply-To: <200507211557.58822.erik.wasser@iquer.net>
O Erik Wasser Ýãñáøå óôéò Jul 21, 2005 :
> Hello List,
>
> I've written an application in perl using DBI with MySQL (no transaction
> support). Then we decide to migrate it to postgresql
> (postgresql-8.0.1-r4).
>
> At first we were using 'AutoCommit => 1' with the application. That
> means that every statement will be commited right away.
>
> Then I discovered the 'magic of transactions' and set AutoCommit to 0.
> Then I rewrite many UPDATE and INSERT statements with support for
> commit and rollback. BUT: the SELECT statements were untouched (and
> that was mistake I think).
>
> Now I've got here a blocking problem. Severel SQL statements (like
> renaming a field or UPDATE of a field) are blocked until I kill a
> certain task. This task DOES only the INSERTS and UPDATES with a
> transaction and the SELECT statements are not within an transaction.
> And this task is a long term running task (some kind of daemon) so the
> SELECT transactions will never be commited. Are long term never
> commited SELECT statements are a problem and could that lead to
> blocking other queries?
>
> To put it in annother way: what kind of thing I produced with the
> following pseudocode?
>
> # open database
> $DBH = DBI->connect(...,USERNAME,PASSWORD, { RaiseError => 1, AutoCommit
> => 0 });
>
> while (true)
> {
> # do some select
> SELECT ... FROM ...
> # do some more
> SELECT ... FROM ...
>
> if (condition)
> # do an UPDATE/INSERT
> eval {
> UPDATE/INSERT/...
> $DBH->commit;
> };
> if ($@) {
> warn "Transaction aborted: $@";
> eval { $DBH->rollback };
> }
> }
> }
>
> Is this some kind of nested transaction? Can there be a problem with
> this code?
You mean savepoints?
In 8.x there is the feature of nested xactions.
But apparrently in your script you dont use them.
In general when working with BEGIN/COMMIT/ROLLBACK blocks
always be sure that you either rollback or commit
your transaction.
The need for this is more visible when using connection pools.
I am not familiar with the DBI semantics, but
in your case it would be quite possible for
some job to block if another job's xaction has
already managed to update a row which the 1st job's xaction
tries to update too.
In any case, pure selects dont need to be in a xaction
unless you want to lock these rows, in which case
you use "FOR UPDATE".
In general you must dig a little deeper into PostgreSQL's
xaction mechanisms and policies,
since migrating from mysql requires some effort
regarding all new (to you) postgresql features.
The documentation (in the usual url) is superb.
>
> Thanks for your help!
>
>
--
-Achilleus
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: achill@matrix.gatewaynet.com, erik.wasser@iquer.net
Subject: Re: Are long term never commited SELECT statements are a
In-Reply-To: <Pine.LNX.4.44.0507211655210.2935-100000@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox