Jdbc connection pg database timeout

  springboot

abnormal

When the database is hung, it starts to report an error (The connection attempt failed)

org.postgresql.util.PSQLException: 尝试连线已失败。
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:233) ~[postgresql-9.3-1102-jdbc4.jar:na]
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64) ~[postgresql-9.3-1102-jdbc4.jar:na]
    at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:144) ~[postgresql-9.3-1102-jdbc4.jar:na]
    at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:29) ~[postgresql-9.3-1102-jdbc4.jar:na]
    at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:21) ~[postgresql-9.3-1102-jdbc4.jar:na]
    at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:31) ~[postgresql-9.3-1102-jdbc4.jar:na]
    at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24) ~[postgresql-9.3-1102-jdbc4.jar:na]
    at org.postgresql.Driver.makeConnection(Driver.java:410) ~[postgresql-9.3-1102-jdbc4.jar:na]
    at org.postgresql.Driver.connect(Driver.java:280) ~[postgresql-9.3-1102-jdbc4.jar:na]
    at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:310) ~[tomcat-jdbc-8.5.6.jar:na]
    at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:203) ~[tomcat-jdbc-8.5.6.jar:na]
    at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:718) [tomcat-jdbc-8.5.6.jar:na]
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:650) [tomcat-jdbc-8.5.6.jar:na]
    at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:468) [tomcat-jdbc-8.5.6.jar:na]
    at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:143) [tomcat-jdbc-8.5.6.jar:na]
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:118) [tomcat-jdbc-8.5.6.jar:na]
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:107) [tomcat-jdbc-8.5.6.jar:na]
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:131) [tomcat-jdbc-8.5.6.jar:na]
    at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess.obtainConnection(JdbcEnvironmentInitiator.java:180) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:68) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:88) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:254) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:228) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:207) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:51) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:94) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:237) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:207) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.handleTypes(MetadataBuildingProcess.java:352) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:111) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:847) [hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:874) [hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:60) [spring-orm-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:353) [spring-orm-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:373) [spring-orm-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:362) [spring-orm-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1648) [spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1585) [spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:554) [spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483) [spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) [spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) [spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) [spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) [spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1081) [spring-context-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:856) [spring-context-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:542) [spring-context-4.3.5.RELEASE.jar:4.3.5.RELEASE]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:761) [spring-boot-1.4.3.RELEASE.jar:1.4.3.RELEASE]
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:371) [spring-boot-1.4.3.RELEASE.jar:1.4.3.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:315) [spring-boot-1.4.3.RELEASE.jar:1.4.3.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1186) [spring-boot-1.4.3.RELEASE.jar:1.4.3.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1175) [spring-boot-1.4.3.RELEASE.jar:1.4.3.RELEASE]
    at com.xixicat.DatasourcePgTimeoutDemoApplication.main(DatasourcePgTimeoutDemoApplication.java:10) [classes/:na]
Caused by: java.net.SocketException: Operation timed out
    at java.net.SocketInputStream.socketRead0(Native Method) ~[na:1.8.0_71]
    at java.net.SocketInputStream.socketRead(SocketInputStream.java:116) ~[na:1.8.0_71]
    at java.net.SocketInputStream.read(SocketInputStream.java:170) ~[na:1.8.0_71]
    at java.net.SocketInputStream.read(SocketInputStream.java:141) ~[na:1.8.0_71]
    at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:143) ~[postgresql-9.3-1102-jdbc4.jar:na]
    at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:112) ~[postgresql-9.3-1102-jdbc4.jar:na]
    at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:71) ~[postgresql-9.3-1102-jdbc4.jar:na]
    at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:282) ~[postgresql-9.3-1102-jdbc4.jar:na]
    at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:386) ~[postgresql-9.3-1102-jdbc4.jar:na]
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:181) ~[postgresql-9.3-1102-jdbc4.jar:na]
    ... 54 common frames omitted

SocketInputStream#socketRead0

/Library/Java/JavaVirtualMachines/jdk1.8.0_71.jdk/Contents/Home/src.zip! /java/net/SocketInputStream.java

/**
     * Reads into an array of bytes at the specified offset using
     * the received socket primitive.
     * @param fd the FileDescriptor
     * @param b the buffer into which the data is read
     * @param off the start offset of the data
     * @param len the maximum number of bytes read
     * @param timeout the read timeout in ms
     * @return the actual number of bytes read, -1 is
     *          returned when the end of the stream is reached.
     * @exception IOException If an I/O error has occurred.
     */
    private native int socketRead0(FileDescriptor fd,
                                   byte b[], int off, int len,
                                   int timeout)
        throws IOException;

soTimeout

If socket timeout is not set, it is 0, which is theoretically unlimited blocking waiting, but it is actually determined by java native according to the implementation of the operating system.

To summarize, timeout for blocking sockets is implemented by calling poll on Linux (and select on Windows) to determine that data is available before calling recv. However, at least on Linux, both methods can spuriously indicate that data is available when it is not, leading to recv blocking indefinitely.

Properly set timeout for pg

  • Background information

<!-- postgresql jdbc -->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>9.3-1102-jdbc4</version>
        </dependency>
  • ConnectionFactoryImpl

/Users/xixicat/.m2/repository/org/postgresql/postgresql/9.3-1102-jdbc4/postgresql-9.3-1102-jdbc4-sources.jar! /org/postgresql/core/v3/ConnectionFactoryImpl.java

public ProtocolConnection openConnectionImpl(HostSpec[] hostSpecs, String user, String database, Properties info, Logger logger) throws SQLException {
        // Extract interesting values from the info properties:
        //  - the SSL setting
        boolean requireSSL;
        boolean trySSL;
        String sslmode = info.getProperty("sslmode");
        if (sslmode==null)
        { //Fall back to the ssl property
          requireSSL = trySSL  = (info.getProperty("ssl") != null);
        } else {
          if ("disable".equals(sslmode))
          {
            requireSSL = trySSL = false;
          }
          //allow and prefer are not handled yet
          /*else if ("allow".equals(sslmode) || "prefer".equals(sslmode))
          {  
            //XXX Allow and prefer are treated the same way
            requireSSL = false;
            trySSL = true;
          }*/
          else if ("require".equals(sslmode) || "verify-ca".equals(sslmode) || "verify-full".equals(sslmode))
          {
            requireSSL = trySSL = true;
          } else {
            throw new PSQLException (GT.tr("Invalid sslmode value: {0}", sslmode), PSQLState.CONNECTION_UNABLE_TO_CONNECT);
          }
        }

        //  - the TCP keep alive setting
        boolean requireTCPKeepAlive = (Boolean.valueOf(info.getProperty("tcpKeepAlive")).booleanValue());

        // NOTE: To simplify this code, it is assumed that if we are
        // using the V3 protocol, then the database is at least 7.4.  That
        // eliminates the need to check database versions and maintain
        // backward-compatible code here.
        //
        // Change by Chris Smith <cdsmith@twu.net>

        for (int whichHost = 0; whichHost < hostSpecs.length; ++whichHost) {
            HostSpec hostSpec = hostSpecs[whichHost];
            
        if (logger.logDebug())
            logger.debug("Trying to establish a protocol version 3 connection to " + hostSpec);

        //
        // Establish a connection.
        //

        int connectTimeout = 0;
        String connectTimeoutProperty = info.getProperty("connectTimeout", "0");
        try {
            connectTimeout = Integer.parseInt(connectTimeoutProperty) * 1000;
        } catch (NumberFormatException nfe) {
            logger.info("Couldn't parse connectTimeout value:" + connectTimeoutProperty);
        }

        PGStream newStream = null;
        try
        {
            newStream = new PGStream(hostSpec, connectTimeout);

            // Construct and send an ssl startup packet if requested.
            if (trySSL)
                newStream = enableSSL(newStream, requireSSL, info, logger, connectTimeout);
            
            // Set the socket timeout if the "socketTimeout" property has been set.
            String socketTimeoutProperty = info.getProperty("socketTimeout", "0");
            try {
                int socketTimeout = Integer.parseInt(socketTimeoutProperty);
                if (socketTimeout > 0) {
                    newStream.getSocket().setSoTimeout(socketTimeout*1000);
                }
            } catch (NumberFormatException nfe) {
                logger.info("Couldn't parse socketTimeout value:" + socketTimeoutProperty);
            }

            // Enable TCP keep-alive probe if required.
            newStream.getSocket().setKeepAlive(requireTCPKeepAlive);

            // Try to set SO_SNDBUF and SO_RECVBUF socket options, if requested.
            // If receiveBufferSize and send_buffer_size are set to a value greater
            // than 0, adjust. -1 means use the system default, 0 is ignored since not
            // supported.

            // Set SO_RECVBUF read buffer size
            String receiveBufferSizeProperty = info.getProperty("receiveBufferSize", "-1");
            try {
                int receiveBufferSize = Integer.parseInt(receiveBufferSizeProperty);
                if (receiveBufferSize > -1) {
                    // value of 0 not a valid buffer size value
                    if (receiveBufferSize > 0) {
                        newStream.getSocket().setReceiveBufferSize(receiveBufferSize);
                    } else {
                        logger.info("Ignore invalid value for receiveBufferSize: " + receiveBufferSize);
                    }
                }
            } catch (NumberFormatException nfe) {
                logger.info("Couldn't parse receiveBufferSize value: " + receiveBufferSizeProperty);
            }

            // Set SO_SNDBUF write buffer size 
            String sendBufferSizeProperty = info.getProperty("sendBufferSize", "-1");
            try {
                int sendBufferSize = Integer.parseInt(sendBufferSizeProperty);
                if (sendBufferSize > -1) {
                    if (sendBufferSize > 0) {
                        newStream.getSocket().setSendBufferSize(sendBufferSize);
                    } else {
                        logger.info("Ignore invalid value for sendBufferSize: " + sendBufferSize);
                    }
                }
            } catch (NumberFormatException nfe) {
                logger.info("Couldn't parse sendBufferSize value: " + sendBufferSizeProperty);
            }

            logger.info("Receive Buffer Size is " + newStream.getSocket().getReceiveBufferSize());
            logger.info("Send Buffer Size is " + newStream.getSocket().getSendBufferSize());

            // Construct and send a startup packet.
            String[][] params = {
                                    { "user", user },
                                    { "database", database },
                                    { "client_encoding", "UTF8" },
                                    { "DateStyle", "ISO" },
                                    { "extra_float_digits", "2" },
                                    { "TimeZone",  createPostgresTimeZone() },                                    
                                };

            sendStartupPacket(newStream, params, logger);

            // Do authentication (until AuthenticationOk).
            doAuthentication(newStream, hostSpec.getHost(), user, info, logger);

            // Do final startup.
            ProtocolConnectionImpl protoConnection = new ProtocolConnectionImpl(newStream, user, database, info, logger, connectTimeout);
            readStartupMessages(newStream, protoConnection, logger);

            runInitialQueries(protoConnection, info, logger);

            // And we're done.
            return protoConnection;
        }
        catch (UnsupportedProtocolException upe)
        {
            // Swallow this and return null so ConnectionFactory tries the next protocol.
            if (logger.logDebug())
                logger.debug("Protocol not supported, abandoning connection.");
            try
            {
                newStream.close();
            }
            catch (IOException e)
            {
            }
            return null;
        }
        catch (ConnectException cex)
        {
            // Added by Peter Mount <peter@retep.org.uk>
            // ConnectException is thrown when the connection cannot be made.
            // we trap this an return a more meaningful message for the end user
            if (whichHost + 1 < hostSpecs.length) {
                // still more addresses to try
                continue;
            }
            throw new PSQLException (GT.tr("Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections."), PSQLState.CONNECTION_UNABLE_TO_CONNECT, cex);
        }
        catch (IOException ioe)
        {
            if (newStream != null)
            {
                try
                {
                    newStream.close();
                }
                catch (IOException e)
                {
                }
            }
            if (whichHost + 1 < hostSpecs.length) {
                // still more addresses to try
                continue;
            }
            throw new PSQLException (GT.tr("The connection attempt failed."), PSQLState.CONNECTION_UNABLE_TO_CONNECT, ioe);
        }
        catch (SQLException se)
        {
            if (newStream != null)
            {
                try
                {
                    newStream.close();
                }
                catch (IOException e)
                {
                }
            }
            if (whichHost + 1 < hostSpecs.length) {
                // still more addresses to try
                continue;
            }
            throw se;
        }
        }
        throw new PSQLException (GT.tr("The connection url is invalid."), PSQLState.CONNECTION_UNABLE_TO_CONNECT);
    }

Two timeout, connectTimeout and socketTimeout are read here. after reading, they are *1000 respectively, indicating that the setting time is in seconds.

  • connectTimeout

public PGStream(HostSpec hostSpec, int timeout) throws IOException
    {
        this.hostSpec = hostSpec;

        Socket socket = new Socket();
        socket.connect(new InetSocketAddress(hostSpec.getHost(), hostSpec.getPort()), timeout);
        changeSocket(socket);
        setEncoding(Encoding.getJVMEncoding("US-ASCII"));

        _int2buf = new byte[2];
        _int4buf = new byte[4];
    }
  • socketTimeout

if (socketTimeout > 0) {
    newStream.getSocket().setSoTimeout(socketTimeout*1000);
}

Set on connection url

spring:
  jpa:
    show-sql: true
  datasource:
    url: jdbc:postgresql://192.168.99.100:5432/test?socketTimeout=1&connectTimeout=1
    driverClassName: org.postgresql.Driver
    username: postgres
    password: xxxxxx
    validation-query: SELECT 1
    test-while-idle: true
    test-on-borrow: true

doc