On Fri, Jun 28, 2019 at 12:55 PM Ashwin Agrawal <[email protected]> wrote:
> On Thu, Jun 27, 2019 at 4:33 PM Thomas Munro <[email protected]> wrote:
>> I wonder if it might be possible to avoid page locks on both the heap
>> *and* index in some limited cases, as I mentioned over here (just an
>> idea, could be way off base):
>>
>> https://www.postgresql.org/message-id/CA%2BhUKGJGDVfhHmoUGzi-_J%2BN8FmRjmWTY0MCd1ZV5Fj9qdyb1w%40mail.gmail.com
>
> I am in same boat as you. One can get serializable conflict error today based
> on tuple gets HOT updated or not. HOT is logically internal code optimization
> and not so much user functionality, so ideally feels shouldn't affect
> serializable behavior. But it does currently, again, due to index locking.
> Disable HOT update and 4 isolation tests fail due to "could not serialize
> access due to read/write dependencies among transactions" otherwise not. If
> the tuple happens to fit on same page user will not get the error, if the
> tuple gets inserted to different page the error can happen, due to index page
> locking. I had discussed this with Heikki and thinking is we shouldn't need
> to take the lock on the index page, if the index key was not changed, even if
> it was a non-HOT update. Not sure of complications and implications, but just
> a thought.
Oh, I think the idea I was suggesting might be the same as this item
from README-SSI (unrelated to HOT, but related to index uniqueness,
particularly in index-only-scan where you might be able to skip the
btree page lock):
"Several optimizations are possible, though not all are implemented yet:
...
* An index scan which is comparing for equality on the entire key
for a unique index need not acquire a predicate lock as long as a key
is found corresponding to a visible tuple which has not been modified
by another transaction -- there are no "between or around" gaps to
cover."
--
Thomas Munro
https://enterprisedb.com
