Is it possible to automatically release a lock in PostgreSQL?

Question

I am testing the fault tolerance of an ActiveMQ system configured as JDBC Master/Slave. In this setup there is one postgres database and two brokers--one is the master broker, the other is a slave broker. The way this mechanism works is the master takes out an exclusive lock on a table in the db. The slave tries to do this also and waits until the lock becomes available. If the master dies, the lock should be released and the slave will take over. However, if the master loses network connectivity with the database, the lock is never released resulting in a deadlock scenario. What seems to be required here is a way to tell Postgres to automatically release the lock if not renewed within a specified period of time. The POSA 3 book of design patterns calls this the Leasing pattern. Is it possible to get Postgres to do this? If not, do other database vendors support it?

Answer 1

This is not a deadlock, this is lost connection problem.

A deadlock occurs when two transactions try to lock the resources previously locked by each other. PostgreSQL detects these situations.

In your case, master locks a resource, slave waits for master, and master waits for the user input which it never receives because the connection is lost.

Whenever PostgreSQL detects a lost connection, it rollbacks its transaction automatically.

To control connection loss detection, you can use the following PostgreSQL connection options:

tcp_keepalives_idle (integer)

On systems that support the TCP_KEEPIDLE socket option, specifies the number of seconds between sending keepalives on an otherwise idle connection. A value of zero uses the system default. If TCP_KEEPIDLE is not supported, this parameter must be zero. This parameter is ignored for connections made via a Unix-domain socket.

tcp_keepalives_interval (integer)

On systems that support the TCP_KEEPINTVL socket option, specifies how long, in seconds, to wait for a response to a keepalive before retransmitting. A value of zero uses the system default. If TCP_KEEPINTVL is not supported, this parameter must be zero. This parameter is ignored for connections made via a Unix-domain socket.

tcp_keepalives_count (integer)

On systems that support the TCP_KEEPCNT socket option, specifies how many keepalives may be lost before the connection is considered dead. A value of zero uses the system default. If TCP_KEEPCNT is not supported, this parameter must be zero. This parameter is ignored for connections made via a Unix-domain socket.