Re: Transaction isolation levels

On Sat, Jul 09, 2005 at 01:13:13PM +0200, Geert Jansen wrote:

> "Read Committed is the default isolation level in PostgreSQL. When a
> transaction runs on this isolation level, a SELECT query sees only data
> committed before the query began; it never sees either uncommitted data
> or changes committed during query execution by concurrent transactions."
>
> Therefore, in this isolation level, I should not see data committed by
> another concurrent transaction.

Wrong. You _should_ see committed data. That's why it's called "read
committed." What you should not see is data that has not been committed
yet, or data that was committed after the current _query_ began. Note
that it says "query," not "transaction."

You can try it with a cursor, because for all purposes, all fetches from
one cursor effectively behave like they were a single query (they all
use one "database snapshot".) So you can insert pauses in the query
while you commit other transactions in the middle.

sess 1:
alvherre=# create table foo (a int);
CREATE TABLE
alvherre=# insert into foo values (1);
INSERT 0 1

sess 2:
alvherre=# begin;
BEGIN
alvherre=# declare foo cursor for select * from foo;
DECLARE CURSOR
alvherre=# fetch 1 from foo;
a
---
1
(1 fila)

sess 1:
alvherre=# insert into foo values (2);
INSERT 0 1

sess 2:
alvherre=# fetch 1 from foo;
a
---
(0 filas)

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"And as an added bonus, now my computer goes to the toilet for me, leaving me
free to spend time on more useful activities! yay slug codefests!" (C. Parker)