The Curious Case of the Table-Locking UPDATE Query

[Emiliano Saenz]

Hello!
We have a huge POSTGRES 9.4 database in the production environment (several
tables have more than 100.000.00 registers). Last two months we have had
problems with CPU utilization. Debugging the locks (on pg_locks) we notice
that sometimes simple UPDATE (by primary key) operation takes out
ACCESS_EXCLUSIVE_LOCK mode over these huge tables so POSTGRES DB collapses
and it generates excessive CPU consumption. My question is, How is it
possible that UPDATE operation takes out ACCESS_EXCLUSIVE_LOCK mode?
More information, this system never manifests this behavior before and we
don't make software changes on last 2 years

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

[Emiliano Saenz]

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