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

On 7/8/21 12:09 PM, Emiliano Saenz wrote:
> 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.

Per docs:

https://www.postgresql.org/docs/12/view-pg-locks.html

"The pid column can be joined to the pid column of the pg_stat_activity
view to get more information on the session holding or awaiting each
lock, for example

SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa
ON pl.pid = psa.pid;

Also, if you are using prepared transactions, the virtualtransaction
column can be joined to the transaction column of the pg_prepared_xacts
view to get more information on prepared transactions that hold locks.
(A prepared transaction can never be waiting for a lock, but it
continues to hold the locks it acquired while running.) For example:

SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
ON pl.virtualtransaction = '-1/' || ppx.transaction;

"

So for the information in pg_locks.csv below, pid of 21187. Then you
will find out what is actually causing the lock.

>
> Best regards,
>
>
>
>
> On Thu, Jul 8, 2021 at 2:42 PM hubert depesz lubaczewski
> <depesz(at)depesz(dot)com <mailto: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
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com