I thinking that i have found the culprit. SSI locks leaf pages on the btree index so if the different values are covered by the same page i will get a serialization failure Is this correct?
On mer 30 ago 2017, 08:45 Luca Looz <luca.loo...@gmail.com> wrote: > I have inserted a row that i don't touch, runned ANALYZE and tried again > but with the same result. > I'm using postgres 9.6.3 and EXPLAIN ANALYZE shows this: > > Index Scan using users_username_idx on users (cost=0.13..8.14 rows=1 > width=4) (actual time=0.020..0.020 rows=0 loops=1) > > Index Cond: (name = 'bob'::text) > > > 2017-08-30 0:13 GMT+02:00 Thomas Munro <thomas.mu...@enterprisedb.com>: > >> On Wed, Aug 30, 2017 at 4:28 AM, Luca Looz <luca.loo...@gmail.com> wrote: >> > I'm trying to use the serializable isolation but i'm getting read/write >> > dependencies error even if i have an unique index on the column used >> and the >> > transactions are using different keys. >> > For an example see this gist: >> > https://gist.github.com/nathanl/f98450014f62dcaf0405394a0955e18e >> > Is this an expected behavior? >> >> Take a look at the EXPLAIN plan for the SELECT. You'll get fine >> grained SIREAD locks if it's doing a btree index scan, but table-level >> SIREAD locks for other kinds of plans. Sometimes simple SSI tests can >> show a lot of false positives just because of empty tables or missing >> statistics (ANALYZE). >> >> -- >> Thomas Munro >> http://www.enterprisedb.com >> > >
