Postgres Pro
Downloads
Home   >   mailing lists

Re: [GENERAL] Serializable Isolation and read/write conflict withindex and different keys - Mailing list pgsql-general

From Luca Looz
Subject Re: [GENERAL] Serializable Isolation and read/write conflict withindex and different keys
Date
Msg-id CAHXaXTxzpZuYyFPUEzfYbL=-L8FXCSNnCQxrUmmozyUKPxC8eA@mail.gmail.com
Whole thread Raw
In response to Re: [GENERAL] Serializable Isolation and read/write conflict withindex and different keys  (Thomas Munro <thomas.munro@enterprisedb.com>)
List pgsql-general
Tree view
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 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

pgsql-general by date:

Previous
From: Joe Wildish
Date:
Subject: [GENERAL] CTE in a Recursive Union
Next
From: Christoph Berg
Date:
Subject: Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) fortoast value 76753264 in pg_toast_10920100