On 03.03.2018 16:44, Amit Kapila wrote:
On Thu, Mar 1, 2018 at 1:22 PM, Konstantin Knizhnik
<k.knizh...@postgrespro.ru> wrote:
On 28.02.2018 16:32, Amit Kapila wrote:
On Mon, Feb 26, 2018 at 8:26 PM, Konstantin Knizhnik
<k.knizh...@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
