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@xxxxxxxxxx <mailto:depesz@xxxxxxxxxx>> 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.klaver@xxxxxxxxxxx
