Обсуждение: The Curious Case of the Table-Locking UPDATE Query

On 7/5/21 4:22 PM, Emiliano Saenz wrote:
> 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

FYI. 9.4 is ~1.5 years past EOL

Please don't post images. It would have just as easy to copy and paste 
the output and would have saved hand building the below.

Where is temp.querys_ejecutandose.csv coming from?

Above you mention querying  pg_locks.

What is the query you are using?

 From here:

https://www.postgresql.org/docs/9.4/explicit-locking.html

"ACCESS EXCLUSIVE

     Conflicts with locks of all modes (ACCESS SHARE, ROW SHARE, ROW 
EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, 
EXCLUSIVE, and ACCESS EXCLUSIVE). This mode guarantees that the holder 
is the only transaction accessing the table in any way.

     Acquired by the DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM 
FULL, and REFRESH MATERIALIZED VIEW (without CONCURRENTLY) commands. 
Many forms of ALTER TABLE also acquire a lock at this level (see ALTER 
TABLE). This is also the default lock mode for LOCK TABLE statements 
that do not specify a mode explicitly.
"



-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Mon, Jul 05, 2021 at 08:22:39PM -0300, Emiliano Saenz wrote:
> 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


To be able to help we will need pg_stat_activity data for the for
backend that has this lock, and pg_locks information for it too.

And, please, send text, and not screenshot.

Best regards,

depesz

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

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