[jira] [Created] (DBCP-563) query return previous result when previous request cause MySQLTimeoutException by one connection

[jode (Jira)]

jode created DBCP-563:
-------------------------

             Summary: query return previous result when previous request cause 
MySQLTimeoutException by one connection
                 Key: DBCP-563
                 URL: https://issues.apache.org/jira/browse/DBCP-563
             Project: Commons DBCP
          Issue Type: Improvement
    Affects Versions: 2.1.1
            Reporter: jode


when run JdbcLocalDemo, you can get wrong result。query a record，then return b 
record。

{code:java}
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.ThreadLocalRandom;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicInteger;

import static java.sql.ResultSet.CLOSE_CURSORS_AT_COMMIT;
import static java.sql.ResultSet.CONCUR_READ_ONLY;
import static java.sql.ResultSet.TYPE_FORWARD_ONLY;

public class JdbcLocalDemo {
    private static final AtomicInteger count = new AtomicInteger(0);

    static BasicDataSource basicDataSource = null;

    static {
        try {
            basicDataSource = 
BasicDataSourceFactory.createDataSource(PropertiesUtils.loadFromClassPath("application-local.properties"));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static void main(String[] args) throws Exception {
        SqlRunnable r1 = new SqlRunnable("a");
        SqlRunnable r2 = new SqlRunnable("b");

        ExecutorService executorService = Executors.newFixedThreadPool(5);

        for(int i = 1; i <= 2000; ++i) {
            executorService.submit(r1);
            executorService.submit(r2);
        }

        executorService.shutdown();

        executorService.awaitTermination(1, TimeUnit.MINUTES);
    }

    private static class SqlRunnable implements Runnable {
        private final String value;

        private SqlRunnable(String value) {
            this.value = value;
        }

        @Override
        public void run() {
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            Connection connection = null;
            System.out.println(count.incrementAndGet());
            try {
                connection = basicDataSource.getConnection();
                preparedStatement = connection.prepareStatement("select \"" + 
value + "\" as v, sleep(?);", TYPE_FORWARD_ONLY, CONCUR_READ_ONLY, 
CLOSE_CURSORS_AT_COMMIT);
                preparedStatement.setQueryTimeout(1);
                preparedStatement.setFetchSize(0);
                preparedStatement.setMaxRows(0);
//                preparedStatement.setString(1, value);
                
preparedStatement.setDouble(1,ThreadLocalRandom.current().nextInt(2000) / 
1000.0);
                resultSet = preparedStatement.executeQuery();

                while (resultSet.next()) {
                    String v = resultSet.getString("v");

                    if (!value.equals(v)) {
                        System.err.println("wrong result 
!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=: " + value);
                        System.exit(-1);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (resultSet != null) {
                    try {
                        resultSet.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (preparedStatement != null) {
                    try {
                        preparedStatement.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }
}
{code}

{code:java}
// application-local.properties
defaultQueryTimeout=1000
driverClassName=com.mysql.jdbc.Driver
maxTotal=10
maxIdle=10
minIdle=5
initialSize=5
testOnBorrow=false
username=root
password=12345678
url=jdbc:mysql://127.0.0.1:3306/db_paymra_router?characterEncoding=UTF8&socketTimeout=1005&allowMulti
{code}




--
This message was sent by Atlassian Jira
(v8.3.4#803005)