CODEWITHSUNDEEP
postgresqllocking
user123454321
user123454321

Reputation: 1058

postgres hangs on insert, deadlock?

Config: Postgres 12.1 with read committed isolation level. I try to insert the same record from multiple applications through jdbc with rollback at the end of every transaction (integration tests using common database). Unfortunately, postgres hangs when more than one insert is executed exactly at the same time. After running some queries I've found out that PID 11837 (insert), wait_event_type Lock, wait_event transactionId is blocked by 11790 (SELECT pg_try_advisory_lock(77433833903597)), idle in transaction. There are usually a few pids executing pg_try_advisory_lock(77433833903597), idle in transaction. Is it expected behavior? I suppose executing inserts with different keys would solve the problem, but I don't want to do that if it is not necessary.

Upvotes: 0

Views: 1403

Answers (1)

jjanes
jjanes

Reputation: 44137

PostgreSQL should automatically detect deadlocks and cancel transactions to resolve them. It can only do this if all edges in the deadlock graph are inside the database. If one session is waiting on another within Java code, for example, then you might have an unresolvable deadlock because that edge is invisible to PostgreSQL.

is blocked by 11790 (SELECT pg_try_advisory_lock(77433833903597)), idle in transaction

Well, what is 11790 doing? It took a lock and then decided to take a nap, holding the database hostage. This is a programming issue. Only the programmer can fix it, unless you want to forcibly terminate whatever that session is doing. It is not a deadlock, as 11790 is not blocked by anything (as far as we or the database can tell).

Upvotes: 1

Related Questions