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