more select-for-update questions

From: "Ed L(dot)" <pgsql(at)bluepolka(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: more select-for-update questions
Date: 2007-08-06 19:09:07
Message-ID: 200708061309.07995.pgsql@bluepolka.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I ran a simple select-for-update test on 8.1.2 and was curious as
to why the semantics are what they are. Specifically, when you
have multiple select-for-update-limit queries on the same rows,
why are rows selected by the blocked query before knowing if
some of those rows will be removed/eliminated by a preceding
select-for-update-limit?

Here's an example. I created this table:

create table foo(
id serial,
done boolean not null default false,
msg varchar);

Then I inserted some data:

select * from foo
id | done | msg
----+------+------------------------------
1 | f | Mon Aug 6 12:09:11 MDT 2007
2 | f | Mon Aug 6 12:09:12 MDT 2007
3 | f | Mon Aug 6 12:09:13 MDT 2007
4 | f | Mon Aug 6 12:09:14 MDT 2007
5 | f | Mon Aug 6 12:09:15 MDT 2007
(5 rows)

Then in transaction A,

begin;

select * from foo where not done for update limit 3;
id | done | msg
----+------+------------------------------
1 | f | Mon Aug 6 12:09:11 MDT 2007
2 | f | Mon Aug 6 12:09:12 MDT 2007
3 | f | Mon Aug 6 12:09:13 MDT 2007
(3 rows)

update foo set done = 't' where id < 4;
UPDATE 3

select * from foo;
id | done | msg
----+------+------------------------------
4 | f | Mon Aug 6 12:09:14 MDT 2007
5 | f | Mon Aug 6 12:09:15 MDT 2007
1 | t | Mon Aug 6 12:09:11 MDT 2007
2 | t | Mon Aug 6 12:09:12 MDT 2007
3 | t | Mon Aug 6 12:09:13 MDT 2007
(5 rows)

Then in transaction B, before committing transaction A,

begin;

select * from foo where not done for update limit 3;
(this blocks transaction B awaiting transaction A commit)

Then, just after commit in transaction A, I see the
previously-blocked query in transaction B returns:

select * from foo where not done for update limit 3;
id | done | msg
----+------+-----
(0 rows)

It returns zero rows when I expected it to return two (id 4 and
5). If I immediately run the same query again in transaction B,
I see what I expected to see in the preceding query:

select * from foo where not done for update limit 3;
id | done | msg
----+------+------------------------------
4 | f | Mon Aug 6 12:09:14 MDT 2007
5 | f | Mon Aug 6 12:09:15 MDT 2007
(2 rows)

So, B is selecting rows for update and applying the limit prior
to knowing which rows will be excluded by A's updates. I know
that is well-documented behavior. It just seems pretty
unintuitive. I'm just wondering if there is some good reason
for it.

TIA.
Ed

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2007-08-06 19:11:06 Re: Template zero xid issue
Previous Message Jeff Davis 2007-08-06 18:32:55 Re: PGSQL internals