Thread: Are long term never commited SELECT statements are a problem?
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
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
On Thu, Jul 21, 2005 at 03:57:56PM +0200, Erik Wasser wrote: > 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. If you set AutoCommit to 0 then all statements are inside a transaction. As you've discovered, SELECT acquires certain locks that persist for the duration of the transaction, so you must commit or roll back the transaction to release those locks (read up on transaction theory to learn more about the rationale for this). -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Thursday 21 July 2005 17:18, Michael Fuhr wrote: > If you set AutoCommit to 0 then all statements are inside a > transaction. As you've discovered, SELECT acquires certain locks > that persist for the duration of the transaction, so you must commit > or roll back the transaction to release those locks (read up on > transaction theory to learn more about the rationale for this). What URL should I read at first? I began with 'http://www.postgresql.org/docs/8.0/interactive/transaction-iso.html'. Is this the right page for starters? BTW: I quote from the page: > When a transaction is on the serializable level, a SELECT query sees > only data committed before the transaction began; it never sees either > uncommitted data or changes committed during transaction execution by > concurrent transactions. (However, the SELECT does see the effects of > previous updates executed within its own transaction, even though they > are not yet committed.) So I opened up two shells and started 'psql' on both shells (marked the command with 1 and 2): 1:begin 2:begin; 1:SELECT * FROM test;id | name ----+------ (0 rows) 2: INSERT INTO test (name) VALUES ('foo'); 1: SELECT * FROM test;id | name ----+------ (0 rows) 2: commit; 1: SELECT * FROM test;id | name ----+------ 1 | foo (1 row) Why do I see in the first transaction data from the commited second transaction? Doesn't prove that the documentation on the above URL wrong? -- So long... Fuzz
On Thu, Jul 21, 2005 at 05:40:57PM +0200, Erik Wasser wrote: > > When a transaction is on the serializable level, a SELECT query sees > > only data committed before the transaction began; it never sees either > > uncommitted data or changes committed during transaction execution by > > concurrent transactions. (However, the SELECT does see the effects of > > previous updates executed within its own transaction, even though they > > are not yet committed.) > Why do I see in the first transaction data from the commited second > transaction? Doesn't prove that the documentation on the above URL > wrong? Because it says "in the serializable level," which they acquire when you execute "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE". -- Alvaro Herrera (<alvherre[a]alvh.no-ip.org>) Officer Krupke, what are we to do? Gee, officer Krupke, Krup you! (West Side Story, "Gee, Officer Krupke")
On Thu, Jul 21, 2005 at 05:40:57PM +0200, Erik Wasser wrote: > Why do I see in the first transaction data from the commited second > transaction? Doesn't prove that the documentation on the above URL > wrong? No, it doesn't. The quote you posted refers to the SERIALIZABLE isolation level, but the default is READ COMMITTED. Unless you've changed the default (and it appears that you haven't), then transactions that don't set an isolation level will default to READ COMMITTED. The following command shows what isolation level the current transaction is using: SHOW transaction_isolation; Try your example again, but issue the following command in transaction 1 before doing anything else: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- Michael Fuhr http://www.fuhr.org/~mfuhr/