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?
Thanks for your help!
--
So long... Fuzz