DML Locking behavior Issues for Partition Table FIXME
Postgres V12 merge is approaching the end. I spend some time on the DML locking behavior for partition table based on the top commit in gpdb-postgres-merge repo (https://github.com/greenplum-db/gpdb-postgres-merge/commit/01f2d101e876747248abb6cb7655d031b5e00726)
For DMLs, there are several places to hold locks:
parse analyze to set target relation
during planning it may expand the partition table that has subclass, this will hold locks on sub tables
InitPlan will init resulterlation, it will try to get partition qual and this will hold locks
QEs will hold locks during InitPlan and Executor code for insert
This report mainly shows the behavior now and clarifies the logic here. We can discuss in this thread what is the best design for locking behavior of partition tables in Greenplum.
I come up with a rule of locking behavior in Greenplum:
QE holds a lock on relation T in M mode => QD holds a lock on relation T in higher level mode than M
In short, QD should hold all locks and in highest level mode. The motivation is:
If without GDD, we should not make waiting on QEs happen
DistributedSnapshot can only be got on QD and then dispatch to QEs.
So if my suggestion is reasonable, we might fix like:
Save the correct lockmode in RTEs
Even for insert, hold all locks for each leaf in QD
Consider if ORCA should hold locks (I vote for not)
GDD impacts the lock level for update and delete on heap tables. As the report’s result, it only impact the RangeVar’s relation. When expanding the root relation, it uses the mode in RTE. Also in InitPlan.
This report only shows the result under planner, as far as I can see, it will hold locks for a table and all its inheritors in the function cdb_estimate_partitioned_numtuples. Maybe it should take lock mode dynamically, not just hard-coded as AccessShareLock.
See detailed results below, the comments starting with ---- is added by me as analysis.
That would be good. Although in general I think we should turn GDD on by
default, and deprecate running without GDD. Is there any reason to not
use GDD?
How is DistributedSnapshot related to this?
That sounds bad. If I understand correctly, normally when you INSERT
into a partitioned table, you only need to lock the partitioned table
itself, and the partition where the new row goes. Locking *all*
partitions on every insert would be a lot more locking.