Serializable Isolation and read/write conflict with index and different keys
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?
On Wed, Aug 30, 2017 at 4:28 AM, Luca Looz <luca.looz92@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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.munro@enterprisedb.com>:
Show quoted text
On Wed, Aug 30, 2017 at 4:28 AM, Luca Looz <luca.looz92@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 andthe
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
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.looz92@gmail.com> wrote:
Show quoted text
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.munro@enterprisedb.com>:
On Wed, Aug 30, 2017 at 4:28 AM, Luca Looz <luca.looz92@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 usedand 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