PostgreSQL not releasing locks from table

Question

We are migrating our application data from Oracle to PostgreSQL.

Environment Details:
Java 1.8
PostgreSQL 9.5 Enterprise Edition (XA DataSource)
Hibernate 4.3
WildFly 9.0.2

We are using latest PostgreSQL driver(postgresql-9.4.1212.jdbc42.jar) available on their website(https://jdbc.postgresql.org/download.html)

Edit: Also tried edb-jdbc17.jar driver which comes with postgres enterprise db. Still same result.

We have also set max_prepared_connections to 100 in postgresql.conf file.

The method given below is taking an object and using hibernate starting transaction and then committing the transaction. Method is not throwing any error or exception. But in database, the object is not getting saved and application is acquiring locks on the table which is resulting in deadlock. The same code works perfectly with Oracle.

public void createObject(Object obj) throws CSTransactionException {
    Session s = null;
    Transaction t = null;
    try {

        try {
            obj = performEncrytionDecryption(obj, true);
        } catch (EncryptionException e) {
            throw new CSObjectNotFoundException(e);
        }


        try{
            obj = ObjectUpdater.trimObjectsStringFieldValues(obj);
        }catch(Exception e){
            throw new CSObjectNotFoundException(e);
        }



        s = HibernateSessionFactoryHelper.getAuditSession(sf);
        t = s.beginTransaction();
        s.save(obj);
        t.commit();
        s.flush();
        auditLog.info("Creating the " + obj.getClass().getName().substring(obj.getClass().getName().lastIndexOf(".")+1) + " Object ");          
    } 
catch (PropertyValueException pve)
    {
        try {
            t.rollback();
        } catch (Exception ex3) {
            if (log.isDebugEnabled())
                log.debug("Authorization|||createObject|Failure|Error in Rolling Back Transaction|" + ex3.getMessage());
        }
        if (log.isDebugEnabled())
            log
                    .debug("Authorization|||createObject|Failure|Error in Rolling Back Transaction|" + pve.getMessage());
        throw new CSTransactionException(
                "An error occured in creating the " + StringUtilities.getClassName(obj.getClass().getName()) + ".\n" + " A null value was passed for a required attribute " + pve.getMessage().substring(pve.getMessage().indexOf(":")), pve);
    }
    catch (ConstraintViolationException cve)
    {
        try {
            t.rollback();
        } catch (Exception ex3) {
            if (log.isDebugEnabled())
                log.debug("Authorization|||createObject|Failure|Error in Rolling Back Transaction|" + ex3.getMessage());
        }
        if (log.isDebugEnabled())
            log
                    .debug("Authorization|||createObject|Failure|Error in Rolling Back Transaction|" + cve.getMessage());
        throw new CSTransactionException(
                "An error occured in creating the " + StringUtilities.getClassName(obj.getClass().getName()) + ".\n" + " Duplicate entry was found in the database for the entered data" , cve);
    }       
    catch (Exception ex) {
        log.error(ex);
        try {
            t.rollback();
        } catch (Exception ex3) {
            if (log.isDebugEnabled())
                log
                        .debug("Authorization|||createObject|Failure|Error in Rolling Back Transaction|"
                                + ex3.getMessage());
        }
        if (log.isDebugEnabled())
            log
                    .debug("Authorization|||createObject|Failure|Error in creating the "
                            + obj.getClass().getName()
                            + "|"
                            + ex.getMessage());
        throw new CSTransactionException(
                "An error occured in creating the "
                        + StringUtilities.getClassName(obj.getClass()
                                .getName()) + "\n" + ex.getMessage(), ex);
    } finally {
        try {

            s.close();
        } catch (Exception ex2) {
            if (log.isDebugEnabled())
                log
                        .debug("Authorization|||createObject|Failure|Error in Closing Session |"
                                + ex2.getMessage());
        }
    }
    if (log.isDebugEnabled())
        log
                .debug("Authorization|||createObject|Success|Successful in creating the "
                        + obj.getClass().getName() + "|");
}

Locks information from database:

I am not a Hibernate expert, I know some about Postgres - Postgres holds lock to the end of transaction. Your code doesn't end a transactions. This is pretty critical issue. — Pavel Stehule, Feb 14 '17 at 19:51
look to postgres - log all statements or waiting statements - maybe hibernate doesn't do it. the table pg_stat_activity or log_min_duration_statement option can help to you. — Pavel Stehule, Feb 15 '17 at 03:08

score 1 · Answer 1 · answered Feb 14 '17 at 17:14

1

You have to close the session after the commit (ideally in the finally block):

s = HibernateSessionFactoryHelper.getAuditSession(sf);
t = s.beginTransaction();
    try {
            s.save(obj);
            session.flush();
            session.clear();  
            t.commit(); 
            auditLog.info("Creating the " + obj.getClass().getName().substring(obj.getClass().getName().lastIndexOf(".")+1) + " Object ");          
        }        
    }catch (Exception e) {
        t.rollBack();
    }finally{
        s.close();
    }

answered Feb 14 '17 at 17:14

Maciej Kowalski

25,605
12
54
63

Thanks for the reply Maciej. I am closing session in finally block. I just pasted the complete method definition. – Atul Feb 14 '17 at 17:46
@Leozeo Try to make `session.flush()` before `t.commit()` as declared in Maciej example – rvit34 Feb 14 '17 at 18:22
Tried flushing session before transaction commit but it didn't work. – Atul Feb 14 '17 at 19:45

PostgreSQL not releasing locks from table

1 Answers1