Re: Contention preventing locking - Mailing list pgsql-hackers

On Mon, Mar 5, 2018 at 1:26 PM, Konstantin Knizhnik
<k.knizhnik@postgrespro.ru> wrote:
>
> On 03.03.2018 16:44, Amit Kapila wrote:
>>
>> On Thu, Mar 1, 2018 at 1:22 PM, Konstantin Knizhnik
>> <k.knizhnik@postgrespro.ru> wrote:
>>>
>>> On 28.02.2018 16:32, Amit Kapila wrote:
>>>>
>>>> On Mon, Feb 26, 2018 at 8:26 PM, Konstantin Knizhnik
>>>> <k.knizhnik@postgrespro.ru> wrote:
>>>
>>>
>>> Yes, but two notices:
>>> 1. Tuple lock is used inside heap_* functions. But not in
>>> EvalPlanQualFetch
>>> where transaction lock is also used.
>>> 2. Tuple lock is hold until the end of update, not until commit of the
>>> transaction. So other transaction can receive conrol before this
>>> transaction
>>> is completed. And contention still takes place.
>>> Contention is reduced and performance is increased only if locks (either
>>> tuple lock, either xid lock) are hold until the end of transaction.
>>> Unfortunately it may lead to deadlock.
>>>
>>> My last attempt to reduce contention was to replace shared lock with
>>> exclusive in XactLockTableWait and removing unlock from this function. So
>>> only one transaction can get xact lock and will will hold it until the
>>> end
>>> of transaction. Also tuple lock seems to be not needed in this case. It
>>> shows better performance on pgrw test but on YCSB benchmark with workload
>>> A
>>> (50% of updates) performance was even worser than with vanilla postgres.
>>> But
>>> was is wost of all - there are deadlocks in pgbench tests.
>>>
>>>> I think in this whole process backends may need to wait multiple times
>>>> either on tuple lock or xact lock.  It seems the reason for these
>>>> waits is that we immediately release the tuple lock (acquired by
>>>> heap_acquire_tuplock) once the transaction on which we were waiting is
>>>> finished.  AFAICU, the reason for releasing the tuple lock immediately
>>>> instead of at end of the transaction is that we don't want to
>>>> accumulate too many locks as that can lead to the unbounded use of
>>>> shared memory.  How about if we release the tuple lock at end of the
>>>> transaction unless the transaction acquires more than a certain
>>>> threshold (say 10 or 50) of such locks in which case we will fall back
>>>> to current strategy?
>>>>
>>> Certainly, I have tested such version. Unfortunately it doesn't help.
>>> Tuple
>>> lock is using tuple TID. But once transaction has made the update, new
>>> version of tuple will be produced with different TID and all new
>>> transactions will see this version, so them will not notice this lock at
>>> all.
>>>
>> Sure, but out of all new transaction again the only one transaction
>> will allow to update it and among new waiters, only one should get
>> access to it.  The situation should be better than when all the
>> waiters attempt to lock and update the tuple with same CTID.
>>
>
> I do not argue against necessity of tuple lock.
> It certainly helps to reduce contention... But still is not able to
> completely eliminate the problem (prevent significant performance
> degradation with increasing number of competing transactions).
>

Yeah, that is what I thought should happen.  I think if we have that
kind of solution, it will perform much better with zheap [1] where
TIDs won't change in many of the cases.

> Better
> results can be achieved with:
> a) Replacing TID lock with PK lock and holding this lock till the end of
> transaction
> b) Chaining xact locks.
>
> I failed to make second approach work, it still suffers from deadlocks.
> I wonder if approach with PK locks can be considered as perspective?
> Should I spent some more time to make it more reliable (support different PK
> types, handle cases of non-hot updates and tables without PK,,...)?
> Or it is dead-end solution in any case?
>

I don't know, maybe you can try to completely sketch out the solution
and see how much impact it has.  If it can be done with the reasonable
effort, then maybe it is worth pursuing.


[1] - https://www.postgresql.org/message-id/CAA4eK1%2BYtM5vxzSM2NZm%2BpC37MCwyvtkmJrO_yRBQeZDp9Wa2w%40mail.gmail.com

-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com