more select-for-update questions

Поиск
Список
Период
Сортировка
От Ed L.
Тема more select-for-update questions
Дата
Msg-id 200708061309.07995.pgsql@bluepolka.net
обсуждение исходный текст
Ответы Re: more select-for-update questions
Список 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


В списке pgsql-general по дате отправления:

Предыдущее
От: Jeff Davis
Дата:
Сообщение: Re: PGSQL internals
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Template zero xid issue