CODEWITHSUNDEEP
sqlpostgresqltransactions
Fabien Henon
Fabien Henon

Reputation: 823

Idle in transaction and lock with PostgreSQL

I have a query that is locked. Thus my web application is locked and I don't really understand why.

Looking at the PgAdmin I have this :

Activities

If I understand correctly, the orange query has been blocked by the query just above (which is in "idle in transaction" state).

Does it mean that the "idle in transaction" transaction is not committed ? And is it because it is not committed that the other transaction is locked ?

If I commit the "idle in transaction" transaction, will it unlock the locked transaction ?

To be honest I don't really understand how I should interpret this. Can you help me ?

Upvotes: 4

Views: 8415

Answers (2)

Chetan
Chetan

Reputation: 39

two things need to be updated on postgresql.conf

idle_transaction_timeout

shared_memory

will solve the problem.

Upvotes: -1

user330315
user330315

Reputation:

Yor assumptions are correct. "idle in transaction" means the session (in this case PID=16686) started a transaction and is holding some locks on the goodscontract table.

Note that the select that you see for PID=16686 is most probably not the statement that is blocking the update (writers are not blocked by readers in Postgres). It is highly likely that PID=16686 ran a different statement previously that acquired the lock (or the select contains a for update clause - as your screenshot doesn't show the full statement this is impossible to tell).

In general it is highly recommended to end a transactions as soon as possible (using commit or rollback) - that includes read-only transactions that only do selects.

Upvotes: 5

Related Questions