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

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(dot)klaver(at)aklaver(dot)com>
wrote:

> 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(dot)klaver(at)aklaver(dot)com
>