Search code examples
postgresqldeadlock

Are there any ways to get SQL queries by transaction id or process in PostgreSQL log?

I'm trying to resolve some deadlocks, occuring in my production PostgreSQL server. What I have:

  1. Sentry, showing first locking query (process 2840554 in log below)
  2. PostgreSQL deadlock message in logs:
2022-05-24 21:48:52 MSK [2840554-1] carrotquest@carrot_shard_1 LOG:  process 2840554 detected deadlock while waiting for ShareLock on transaction 4153893838 after 1000.029 ms
2022-05-24 21:48:52 MSK [2840554-2] carrotquest@carrot_shard_1 DETAIL:  Process holding the lock: 2879765. Wait queue: .
2022-05-24 21:48:52 MSK [2840554-3] carrotquest@carrot_shard_1 CONTEXT:  while locking tuple (72609310,2) in relation "users_user"
2022-05-24 21:48:52 MSK [2840554-4] carrotquest@carrot_shard_1 STATEMENT:  SELECT "users_user"."id", "users_user"."app_id", "users_user"."props_json", "users_user"."props_events_json", "users_user"."removed", "users_user"."hidden", "users_user"."email_status_id", "users_user"."es_index" FROM "users_user" WHERE "users_user"."id" = 1138071193757156022 LIMIT 21 FOR UPDATE
2022-05-24 21:48:52 MSK [2879765-2] carrotquest@carrot_shard_1 DETAIL:  Process holding the lock: 2840554. Wait queue: 2879765, 2883036.
2022-05-24 21:48:52 MSK [2840554-5] carrotquest@carrot_shard_1 ERROR:  deadlock detected
2022-05-24 21:48:52 MSK [2840554-6] carrotquest@carrot_shard_1 DETAIL:  Process 2840554 waits for ShareLock on transaction 4153893838; blocked by process 2879765.
     Process 2879765 waits for ShareLock on transaction 4153893850; blocked by process 2840554.
     Process 2840554: SELECT "users_user"."id", "users_user"."app_id", "users_user"."props_json", "users_user"."props_events_json", "users_user"."removed", "users_user"."hidden", "users_user"."email_status_id", "users_user"."es_index>
     Process 2879765: COMMIT

2022-05-24 21:48:52 MSK [2840554-7] carrotquest@carrot_shard_1 HINT:  See server log for query details.
2022-05-24 21:48:52 MSK [2840554-8] carrotquest@carrot_shard_1 CONTEXT:  while locking tuple (72609310,2) in relation "users_user"
2022-05-24 21:48:52 MSK [2840554-9] carrotquest@carrot_shard_1 STATEMENT:  SELECT "users_user"."id", "users_user"."app_id", "users_user"."props_json", "users_user"."props_events_json", "users_user"."removed", "users_user"."hidden", "users_user"."email_status_id", "users_user"."es_index" FROM "users_user" WHERE "users_user"."id" = 1138071193757156022 LIMIT 21 FOR UPDATE

As you see second process holding the lock is unknown - it is some transaction performing "COMMIT;" blocking transaction 4153893838.

My question is: Is threre a way to find some information about this transaction? SQL, or some other clues which can help me find it in my code?

Thanks

Solution

  • Add %x to your log_line_prefix, then the transaction ID will be logged too. If you log all statements, you can then easily identify all statements that belong to that transaction.

    I know it may not be feasible to log everything, but that's the only way to do that in PostgreSQL.