Re: The Curious Case of the Table-Locking UPDATE Query

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 8, 2021 at 2:42 PM hubert depesz lubaczewski <depesz(at)depesz(dot)com>
wrote:

> 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
>
>