Re: Predicate locking

Dan Ports wrote:
> Yes, you're right -- the current implementation of SSI only locks
> indexes at the granularity of index pages. So although those
> transactions don't actually access the same records, they're detected
> as a conflict because they're on the same index page.

Let's try to demonstrate that with an update to Vlad's example. Run
this on a first client to generate the same type of table, but with an
easy to vary number of rows in it:

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;
select * from t where id = 2;
insert into t (id, value) values (-2, 1);

Execute this on the second client:

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

And then return to the first one to run COMMIT. I'm getting a
serialization failure in that case. However, if I increase the
generate_series to create 349 rows (or more) instead, it works. I don't
see where it crosses a page boundary in table or index size going from
348 to 349, so I'm not sure why I'm seeing a change happening there. In
both cases, there's only one non-header index block involved.

I don't think Vlad is being unreasonable here; he's provided a test case
demonstrating the behavior he'd like to see, and shown it doesn't work
as expected. If we can prove that test does work on non-trivial sized
tables, and that it only suffers from to-be-optimized broader locks than
necessary, that would make a more compelling argument against the need
for proper predicate locks. I don't fully understand why this attempt I
tried to do that is working the way it does though.

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