postgresqllockingpg

What happens when application thread shuts down while having a row lock in postgresql?


If application has acquired row lock and is performing transaction, when shut down (eg. power outage) what will happen to the locked row? Is the lock turned off and whole transaction is rolled back?


Solution

  • In PostgreSQL, row locks are stored by storing the transaction ID of the locking transaction in the xmax system attribute of the table row. When a session checks if the row is locked, it will consult PostgreSQL's commit log for the status of the locking transaction. Only if that transaction is still open, the row is considered locked.

    So the question boils down to: when does the server realize that the client is gone?

    If the server tries to send a response to the client, it will notice right away that the client is gone. But if it is “idle in transaction”, it can take longer, because the server is waiting for the client to send the next statement and won't notice immediately that the line is dead. Eventually, TCP keepalive will detect that the connection is gone, but that could take more than two hours.

    You can tune the database parameters tcp_keepalives_idle, tcp_keepalives_interval and tcp_keepalives_count to make the server detect a dead connection sooner. While at it, you may also want to set client_connection_check_interval so that the server also notices a dead connection if it is currently busy processing a long-running statement.