Postgres: How to log down the locks for a transaction that is created by Spring JPA-CodePudding

I have an operation made from Java Spring application, it produces a log like below with the number before LOG: is the transaction ID (%x) https://www.postgresql.org/docs/13/runtime-config-logging.html

2021-11-08 06:55:26.834 UTC [134] 0 LOG:  execute S_4: BEGIN
2021-11-08 06:55:26.836 UTC [134] 0 LOG:  execute <unnamed>: /* insert ENTITY1 */ 
    RETURNING *
2021-11-08 06:55:26.881 UTC [134] 19573 LOG:  execute <unnamed>: /* insert ENTITY2 */ 
    RETURNING *
2021-11-08 06:55:28.165 UTC [134] 19573 LOG:  execute <unnamed>: /* update ENTITY1 */
--Some more things--
2021-11-08 06:55:30.357 UTC [155] 0 LOG:  execute <unnamed>: BEGIN
2021-11-08 06:55:30.357 UTC [155] 0 LOG:  execute <unnamed>: 
    RETURNING *
2021-11-08 06:55:30.400 UTC [155] 19576 LOG:  execute S_1: COMMIT
2021-11-08 06:55:30.452 UTC [134] 19573 LOG:  execute <unnamed>: /* update ENTITY1 */ update ...
2021-11-08 06:55:32.395 UTC [155] 0 LOG:  execute <unnamed>: BEGIN READ ONLY
2021-11-08 06:55:32.396 UTC [155] 0 LOG:  execute <unnamed>: select ...
2021-11-08 06:55:32.438 UTC [155] 0 LOG:  execute S_1: COMMIT
2021-11-08 06:55:32.564 UTC [155] 0 LOG:  execute S_2: BEGIN READ ONLY
2021-11-08 06:55:32.565 UTC [155] 0 LOG:  execute S_3: select ...
2021-11-08 06:55:32.606 UTC [155] 0 LOG:  execute S_1: COMMIT
2021-11-08 06:55:32.648 UTC [155] 0 LOG:  execute <unnamed>: select 
2021-11-08 06:55:32.864 UTC [134] 19573 LOG:  execute S_1: COMMIT

I see that for some reason the transaction is running for really long (a few seconds)

My question:

Given that I only do insert single entity and update always with a WHERE, can this cause table level lock?
Is there a way I can log out either from Postgres side or Java side when and which type of locks is acquired by this operation - and for how long?

CodePudding user response：

Given that I only do insert single entity and update always with a WHERE, can this cause table level lock?

Queries or Updates with a where cause can take time if there is no index on the column used in where column. You can try explain to figure out as well.

Is there a way I can log out either from Postgres side or Java side when and which type of locks is acquired by this operation - and for how long?

Since your code does not look like explicitly taking any database locks - the locks will be taken implicitly during SQL execution by the database engine. Postgres provides different views to query the lock level information. You can refer to this and official docs for different queries to get lock information from Postgres side.