Re: Predicate locking
От | Greg Smith |
---|---|
Тема | Re: Predicate locking |
Дата | |
Msg-id | 4DC0C083.20204@2ndquadrant.com обсуждение исходный текст |
Ответ на | Re: Predicate locking ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>) |
Список | pgsql-hackers |
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.35rows=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 HeapScan 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@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
В списке pgsql-hackers по дате отправления: