Re: Predicate locking

Kevin Grittner wrote:
> Check where the plan goes from a table scan to an indexed access.
> Also look at what is showing for SIRead locks in pg_locks as you go.
> Between those two bits of information, it should become apparent.

OK, so this doesn't look to be an index lock related thing at all here.
Updated test case does this to create the table and show some additional
state:

drop table t;
create table t (id bigint, value bigint);
insert into t(id,value) (select s,1 from generate_series(1,348) as s);
create index t_idx on t(id);
begin transaction;
set transaction isolation level serializable;
explain analyze select * from t where id = 2;
select pid,locktype,relation::regclass,page,tuple from pg_locks where
mode='SIReadLock';
insert into t (id, value) values (-2, 1);
select pid,locktype,relation::regclass,page,tuple from pg_locks where
mode='SIReadLock';

Do the same thing as before on the second process:

begin transaction;
set transaction isolation level serializable;
select * from t where id = 3;
insert into t (id, value) values (-3, 0);
commit;

Then return to the first client to commit. When I execute that with 348
records, the case that fails, it looks like this:

gsmith=# explain analyze select * from t where id = 2;
QUERY
PLAN
--------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..6.35 rows=2 width=16) (actual
time=0.106..0.286 rows=1 loops=1)
Filter: (id = 2)
Total runtime: 0.345 ms
(3 rows)

gsmith=# select pid,locktype,relation::regclass,page,tuple from pg_locks
where mode='SIReadLock';
pid | locktype | relation | page | tuple
------+----------+----------+------+-------
1495 | relation | t | |

So it's actually grabbing a lock on the entire table in that situation.
The other client does the same thing, and they collide with the
described serialization failure.

The minute I try that with table that is 349 rows instead, it switches
plans:

gsmith=# explain analyze select * from t where id = 2;
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on t (cost=4.27..6.29 rows=2 width=16) (actual
time=0.169..0.171 rows=1 loops=1)
Recheck Cond: (id = 2)
-> Bitmap Index Scan on t_idx (cost=0.00..4.27 rows=2 width=0)
(actual time=0.144..0.144 rows=1 loops=1)
Index Cond: (id = 2)
Total runtime: 0.270 ms
(5 rows)

gsmith=# select pid,locktype,relation::regclass,page,tuple from pg_locks
where mode='SIReadLock';
pid | locktype | relation | page | tuple
------+----------+----------+------+-------
1874 | page | t_idx | 1 |
1874 | tuple | t | 0 | 2
(2 rows)

Grabbing a lock on the index page and the row, as Dan explained it
would. This actually eliminates this particular serialization failure
altogether here though, even with these still on the same table and
index page.

So the root problem with Vlad's test isn't the index lock at all; it's
heavy locking from the sequential scan that's executing on the trivial
cases. If he expands his tests to use a larger amount of data, such
that the plan switches to a realistic one, his results with the new
serialization mode may very well be more satisfying.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us