CODEWITHSUNDEEP
postgresqljdbclockingpostgresql-9.0
Crystal Paladin
Crystal Paladin

Reputation: 589

postgresql9.0 locked and unresponsive if update operation performed on the row where update operation is interrupted

Scenario:

  1. table has a row with id=1
  2. update command initiated with JDBC on row id=1 but before it gets committed, the java program that made the JDBC connection got terminated
  3. Now another JDBC connection tries to update the same row id=1

Observation:

When monitoring the database, the locks are noticeable and further update operation on the same row, causes the db server to freeze up and must be restarted for normal operation...
If there are less update queries and less number of sessions, the locks gets released within a minute... but in real world apps, the update gets executed frequently and the server freezes up

So my Question is:

Is there a way(configuration,tools,...) to force,close and drop the locks in Postgres server for any dead db connections from client

Code:

package postgrestest;

import java.sql.*;

public class IncompleteTransvanilla implements Runnable {
    private int seq = 0;
    private Connection connection = null;
    private boolean insertoperation = true;

    public static void main(String[] args) throws Exception{
        boolean flag = true;
        if(args.length>0) {flag = false;}
        for(int i=0;i<10;i++) {
            new Thread(new IncompleteTransvanilla(i,flag)).start();
            //Thread.sleep(1000*(int)(Math.random()*10));
        }
        Thread.sleep(10000);
    }

    public IncompleteTransvanilla(int i, boolean flag) {
        try {
            connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/testdb", "postgres", "postgres");
            connection.setAutoCommit(false);
        } catch (Exception e) {
            System.out.println("error");
        }
        seq=i;
        insertoperation = flag;
    }

    public void run() {
        try {
            PreparedStatement statement;
            if(insertoperation == true) {
                System.out.println("Inserting");
                statement = connection.prepareStatement("Insert into uacc(user_id,username,password,email) values(DEFAULT,?,'samplepass','samplemail')");
            }
            else {
                System.out.println("Updating");
                statement = connection.prepareStatement("update uacc set username=? where user_id=1");
            }
            int maxval = 100;
            for(int i=0;i<maxval;i++) {
                statement.setString(1, "sampleuser"+((seq*maxval)+i));
                statement.execute();
            }
            Thread.sleep(10000);
            connection.commit();
        } catch (Exception e) {
            System.out.println("Connection failure.");
        }
    }

}

database monitoring dash screenshot for reference:

Database Monitoring - pashviewer postgres9.0 locks

Edit: any suggestion/configuration in server that can be used to auto drop such locks or config to reduce the time it it waits for the connection and drops the locks?

or... will upgrading postgres9.0 to say 9.5 fix these kinda issues?

Upvotes: 0

Views: 434

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246268

To protect against clients that suddenly die in the middle of work, you can use the following PostgreSQL parameters:

  • tcp_keepalives_idle: Set it to a low value like 60, so that the server probes the client after a minute of inactivity and can kill the session if the client is dead.

  • idle_in_transaction_session_timeout: Set this to and even lower value, because no transaction should remain idle for a long time. This will kill sessions that are idle while holding locks. Be careful that you don't disrupt normal activity on your database!

Upgrading from 9.0 should be your first priority, since 9.0 is dangerously old and unsupported. Update to v11 rather than to 9.5 though.

Upvotes: 1

Related Questions