pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAre long term never commited SELECT statements are a problem?
7+ messages / 5 participants
[nested] [flat]
* Are long term never commited SELECT statements are a problem?
@ 2005-07-21 13:57 Erik Wasser <erik.wasser@iquer.net>
2005-07-21 14:21 ` Re: Are long term never commited SELECT statements are a Achilleus Mantzios <achill@matrix.gatewaynet.com>
2005-07-21 15:18 ` Re: Are long term never commited SELECT statements are a problem? Michael Fuhr <mike@fuhr.org>
0 siblings, 2 replies; 7+ messages in thread
From: Erik Wasser @ 2005-07-21 13:57 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Are long term never commited SELECT statements are a
2005-07-21 13:57 Are long term never commited SELECT statements are a problem? Erik Wasser <erik.wasser@iquer.net>
@ 2005-07-21 14:21 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 0 replies; 7+ messages in thread
From: Achilleus Mantzios @ 2005-07-21 14:21 UTC (permalink / raw)
To: Erik Wasser <erik.wasser@iquer.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Are long term never commited SELECT statements are a problem?
2005-07-21 13:57 Are long term never commited SELECT statements are a problem? Erik Wasser <erik.wasser@iquer.net>
@ 2005-07-21 15:18 ` Michael Fuhr <mike@fuhr.org>
2005-07-21 15:40 ` Re: Are long term never commited SELECT statements are a problem? Erik Wasser <erik.wasser@iquer.net>
1 sibling, 1 reply; 7+ messages in thread
From: Michael Fuhr @ 2005-07-21 15:18 UTC (permalink / raw)
To: Erik Wasser <erik.wasser@iquer.net>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Are long term never commited SELECT statements are a problem?
2005-07-21 13:57 Are long term never commited SELECT statements are a problem? Erik Wasser <erik.wasser@iquer.net>
2005-07-21 15:18 ` Re: Are long term never commited SELECT statements are a problem? Michael Fuhr <mike@fuhr.org>
@ 2005-07-21 15:40 ` Erik Wasser <erik.wasser@iquer.net>
2005-07-21 16:13 ` Re: Are long term never commited SELECT statements are a problem? Alvaro Herrera <alvherre@alvh.no-ip.org>
2005-07-21 16:23 ` Re: Are long term never commited SELECT statements are a problem? Michael Fuhr <mike@fuhr.org>
0 siblings, 2 replies; 7+ messages in thread
From: Erik Wasser @ 2005-07-21 15:40 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Are long term never commited SELECT statements are a problem?
2005-07-21 13:57 Are long term never commited SELECT statements are a problem? Erik Wasser <erik.wasser@iquer.net>
2005-07-21 15:18 ` Re: Are long term never commited SELECT statements are a problem? Michael Fuhr <mike@fuhr.org>
2005-07-21 15:40 ` Re: Are long term never commited SELECT statements are a problem? Erik Wasser <erik.wasser@iquer.net>
@ 2005-07-21 16:13 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
1 sibling, 0 replies; 7+ messages in thread
From: Alvaro Herrera @ 2005-07-21 16:13 UTC (permalink / raw)
To: Erik Wasser <erik.wasser@iquer.net>; +Cc: pgsql-sql
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")
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Are long term never commited SELECT statements are a problem?
2005-07-21 13:57 Are long term never commited SELECT statements are a problem? Erik Wasser <erik.wasser@iquer.net>
2005-07-21 15:18 ` Re: Are long term never commited SELECT statements are a problem? Michael Fuhr <mike@fuhr.org>
2005-07-21 15:40 ` Re: Are long term never commited SELECT statements are a problem? Erik Wasser <erik.wasser@iquer.net>
@ 2005-07-21 16:23 ` Michael Fuhr <mike@fuhr.org>
1 sibling, 0 replies; 7+ messages in thread
From: Michael Fuhr @ 2005-07-21 16:23 UTC (permalink / raw)
To: Erik Wasser <erik.wasser@iquer.net>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Are long term never commited SELECT statements are a pr
@ 2005-07-22 12:57 KÖPFERL Robert <robert.koepferl@sonorys.at>
0 siblings, 0 replies; 7+ messages in thread
From: KÖPFERL Robert @ 2005-07-22 12:57 UTC (permalink / raw)
To: 'Erik Wasser' <erik.wasser@iquer.net>; pgsql-sql
You may get problems. At least we did.
Having a long term transaction which seemingly just was one Begin with
nothing, we encountered a siginifficant decrease of performance after some
days (70 tx/sec)
During that the pg_subtrans dir filled up with files and the IO-reads of the
disk as well. After closing the particular connection the normal performance
reappeared.
And the files in pg_subtrans became one file again.
So far.
BTW. You will see the DB with a glasses that shows you the time when the
transaction started.
|-----Original Message-----
|From: Erik Wasser [mailto:erik.wasser@iquer.net]
|Sent: Donnerstag, 21. Juli 2005 15:58
|To: pgsql-sql@postgresql.org
|Subject: [SQL] 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
|
|---------------------------(end of
|broadcast)---------------------------
|TIP 2: Don't 'kill -9' the postmaster
|
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-07-22 12:57 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-21 13:57 Are long term never commited SELECT statements are a problem? Erik Wasser <erik.wasser@iquer.net>
2005-07-21 14:21 ` Re: Are long term never commited SELECT statements are a Achilleus Mantzios <achill@matrix.gatewaynet.com>
2005-07-21 15:18 ` Michael Fuhr <mike@fuhr.org>
2005-07-21 15:40 ` Erik Wasser <erik.wasser@iquer.net>
2005-07-21 16:13 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2005-07-21 16:23 ` Michael Fuhr <mike@fuhr.org>
2005-07-22 12:57 Re: Are long term never commited SELECT statements are a pr KÖPFERL Robert <robert.koepferl@sonorys.at>
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