From: | Luca Looz <luca(dot)looz92(at)gmail(dot)com> |
---|---|
To: | Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com> |
Cc: | Forums postgresql <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Serializable Isolation and read/write conflict with index and different keys |
Date: | 2017-08-30 16:37:47 |
Message-ID: | CAHXaXTy=aJROA0pXXifb=jkvP+2_62s9+ictzqkmfhOJVjHWxg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)looz92(at)gmail(dot)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(dot)munro(at)enterprisedb(dot)com>:
>
>> On Wed, Aug 30, 2017 at 4:28 AM, Luca Looz <luca(dot)looz92(at)gmail(dot)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
>>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Alan Millington | 2017-08-30 17:40:47 | Re: plpython2.dll missing from Enterprise DB Postgres distribution |
Previous Message | Ron Johnson | 2017-08-30 13:54:40 | Re: Veritas cluster management |