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

temp.querys_ejecutandose_csv is temporally table where we have put the
result of the next query:

select
a.datname,
l.relation::regclass,
l.transactionid,
l.mode,
l.GRANTED,
a.usename,
a.query,
a.query_start,
age(now(), a.query_start) as "age",
a.pid
from
pg_stat_activity a
join pg_locks l on
l.pid = a.pid
order by
a.query_start;

This query gets the level of block by pid according to pg_stat_activity and
pg_locks.
Attach the original file temp.querys_ejecutandose_csv.

The query that we are running it is a simple UPDATE by primary key:

UPDATE factura SET rubro = 13,aux_tipo_fac = 0 WHERE id_factura = 11580435

This UPDATE gets an ACCESS EXCLUSIVE block.

Bye.

On Mon, Jul 5, 2021 at 9:34 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

Attach the files.

Best regards,

On Tue, Jul 6, 2021 at 6:24 AM hubert depesz lubaczewski <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

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 <depesz@depesz.com>
wrote:

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