Skip site navigation (1) Skip section navigation (2)

Re: Are long term never commited SELECT statements are a problem?

From: Erik Wasser <erik(dot)wasser(at)iquer(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Are long term never commited SELECT statements are a problem?
Date: 2005-07-21 15:40:57
Message-ID: 200507211740.57999.erik.wasser@iquer.net (view raw or flat)
Thread:
Lists: 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

In response to

Responses

pgsql-sql by date

Next:From: Alvaro HerreraDate: 2005-07-21 16:13:34
Subject: Re: Are long term never commited SELECT statements are a problem?
Previous:From: Michael FuhrDate: 2005-07-21 15:18:11
Subject: Re: Are long term never commited SELECT statements are a problem?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group