I can see that you say but the database behavior is like the block is more general than one tuple.
It is difficult to get a pg_lock snapshot to determine some access exclusive locks on some tables.
Monitoring the database (by Zabbix), when this type of block appears (AccessExclusiveLock) the CPU consumption is extremely high due to it being over one main table for our business.
The UPDATE operation has as target one tuple but the block can affect the complete table? Is it possible?
Furthermore, monitoring other systems, it is strange that this type of block appears, except when we make a release and we edit the database structure, truncate tables, etc.
Best regards,
On Thu, Jul 08, 2021 at 02:35:33PM -0300, Emiliano Saenz wrote:
> Attach the files.
The pg_locks file doesn't show any access exclusive locks on any table?
=$ awk -F, 'NR==1 || $13 == "AccessExclusiveLock"' pg_locks.csv
Locktype,Database,Relation,Page,Tuple,Virtualxid,Transactionid,Classid,Objid,Objsubid,Virtualtransaction,Pid,Mode,Granted,Fastpath
tuple,248043888,248044255,213071,39,NULL,NULL,NULL,NULL,NULL,198/814,21038,AccessExclusiveLock,f,f
tuple,248043888,248044255,213071,39,NULL,NULL,NULL,NULL,NULL,34/90197,21187,AccessExclusiveLock,t,f
tuple,248043888,248044255,213071,39,NULL,NULL,NULL,NULL,NULL,132/957,21007,AccessExclusiveLock,f,f
As you can see all the AccessExclusive locks are on tuples (rows).
Best regards,
depesz