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
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 |