On Fri, Jun 28, 2019 at 12:55 PM Ashwin Agrawal <aagrawal@pivotal.io> wrote:
> On Thu, Jun 27, 2019 at 4:33 PM Thomas Munro <thomas.munro@gmail.com> 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
islogically internal code optimization and not so much user functionality, so ideally feels shouldn't affect
serializablebehavior. But it does currently, again, due to index locking. Disable HOT update and 4 isolation tests fail
dueto "could not serialize access due to read/write dependencies among transactions" otherwise not. If the tuple
happensto 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
onthe 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