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@depesz.com <mailto:depesz@depesz.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.klaver@aklaver.com