Hanging postgres queries?

Question

PostgreSQL

Good afternoon!

Requests hang and because of this it is impossible to perform any action on the site.
I have transactions organized on the server

const connection = getConnection()
const queryRunner = connection.createQueryRunner()
await queryRunner.connect()
await queryRunner.startTransaction()

try {
  await queryRunner.commitTransaction()
} catch (e) {
  await queryRunner.rollbackTransaction()
} finally {
  await queryRunner.release()
}

Upon request

SELECT * FROM pg_stat_activity WHERE state = 'active';

They hang constantly the same thing, all the same and there are 8 of them

spoiler

16384	testbase	25447	10	testbase		172.18.0.5		41262	2020-06-27 18:38:18.768562+00	2020-06-27 18:38:18.795934+00	2020-06-27 18:38:18.819928+00	2020-06-27 18:38:18.819929+00	Lock	tuple	active		9425627	SELECT "FreeGameEntity"."id" AS "FreeGameEntity_id", "FreeGameEntity"."type" AS "FreeGameEntity_type", "FreeGameEntity"."name" AS "FreeGameEntity_name", "FreeGameEntity"."title" AS "FreeGameEntity_title", "FreeGameEntity"."image" AS "FreeGameEntity_image", "FreeGameEntity"."items" AS "FreeGameEntity_items", "FreeGameEntity"."games" AS "FreeGameEntity_games", "FreeGameEntity"."status" AS "FreeGameEntity_status", "FreeGameEntity"."lvls" AS "FreeGameEntity_lvls", "FreeGameEntity"."description" AS "FreeGameEntity_description", "FreeGameEntity"."time_limit" AS "FreeGameEntity_time_limit", "FreeGameEntity"."players" AS "FreeGameEntity_players", "FreeGameEntity"."player" AS "FreeGameEntity_player", "FreeGameEntity"."winner" AS "FreeGameEntity_winner", "FreeGameEntity"."winner_amount" AS "FreeGameEntity_winner_amount", "FreeGameEntity"."created_at" AS "FreeGameEntity_created_at" FROM "free_games" "FreeGameEntity" WHERE title = $1 FOR UPDATE	client backend

Data acquisition code

const c = await query.manager
        .getRepository(FreeGameEntity)
        .createQueryBuilder()
        .useTransaction(true)
        .setLock('pessimistic_write')
        .where('title = :title', {title: this.opts.gameName})
        .getOne()

How can this problem be corrected?

Answer 1

It is clearly visible that you have a request for update, wait_event_type = Lock, wait_event = tuple.
Therefore, the request did not freeze at all, everything is as it should be. Some other transaction has already taken a conflicting lock on this row, and therefore this request is waiting for that transaction to complete.
You can see which session holds the lock, for example, like this: https://github.com/dataegret/pg-utils/blob/master/...