Re: Contention preventing locking - Mailing list pgsql-hackers

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). 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?
Another idea suggested by Simon is to lock root tuple (head of hot 
update chain). But there is no efficient way to locate this root in 
Postgres.




-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company