Re: Are long term never commited SELECT statements are a

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