import com.mysql.jdbc.MySQLConnection;
import java.sql.*;
import java.util.concurrent.TimeUnit;

/**
 * MySQL 默认可重复读RR.
 * 两个线程T1，T2 分别执行一个业务单元.
 * T1中先 UPDATE test1 set field1='AA' where id=1, 然后 select field1 from test1 where id=1就应该返回 AA.
 * T2同理, set BB 后，select出的也应该是 BB .
 * <p>
 * 但这是建立在两个业务单元在不同事务中执行时，如果多线程同时操作统一connection，（多线程不是必要条件，只是容易理解而已）,
 * MySQL的协议决定了两个业务单元SQL会穿插到同一个事务中，而不会爆出错误.
 * start transaction; sql11; sql12; commit;
 * start transaction; sql21; sql22; commit;
 * =>
 * start transaction; start transaction; sql11; sql21; sql12; sql22; commit; commit;
 * <p>
 * 下面例子演示了 “两个线程操作同一个connection，导致RR失效” 这一不符合直觉的例子.
 *
 * 编译:javac -cp ~/.m2/repository/mysql/mysql-connector-java/5.1.41/mysql-connector-java-5.1.41.jar  JDBCMultiThreadDemo.java
 *
 * 执行(*nix):
 * 相同连接
 java -cp /Users/ljh/.m2/repository/mysql/mysql-connector-java/5.1.41/mysql-connector-java-5.1.41.jar:/Users/ljh/.m2/repository/org/slf4j/slf4j-api/1.7.21/slf4j-api-1.7.21.jar:/Users/ljh/.m2/repository/ch/qos/logback/logback-core/1.1.7/logback-core-1.1.7.jar:/Users/ljh/.m2/repository/ch/qos/logback/logback-classic/1.1.7/logback-classic-1.1.7.jar:.  -DuseSameConnectionSimultaneously=true JDBCMultiThreadDemo
 不同连接(作为对比)
 java -cp /Users/ljh/.m2/repository/mysql/mysql-connector-java/5.1.41/mysql-connector-java-5.1.41.jar:/Users/ljh/.m2/repository/org/slf4j/slf4j-api/1.7.21/slf4j-api-1.7.21.jar:/Users/ljh/.m2/repository/ch/qos/logback/logback-core/1.1.7/logback-core-1.1.7.jar:/Users/ljh/.m2/repository/ch/qos/logback/logback-classic/1.1.7/logback-classic-1.1.7.jar:.  -DuseSameConnectionSimultaneously=false JDBCMultiThreadDemo
 *
 * Window下path.separator 是 ;
 * Linux下是 :
 *
 *
 */
public class JDBCMultiThreadDemo {
    public static void main(String[] args) throws SQLException, InterruptedException {

        String host = "HOST:3306";
        String database = "x";
        String user = "x";
        String password = "x";
        /*
        CREATE TABLE `test1` (
          `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
          `field1` varchar(255) NOT NULL,
          PRIMARY KEY (`id`),
        ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;
        INSERT INTO test1(id,field1) values (1,"default");
         */

        String url = String.format("jdbc:mysql://%s/%s?user=%s&password=%s", host, database, user, password) +
                "&useUnicode=true&characterEncoding=utf8&useSSL=false&sessionVariables=sql_mode='NO_ENGINE_SUBSTITUTION'&jdbcCompliantTruncation=false&zeroDateTimeBehavior=round" +
//                "&logger=com.mysql.jdbc.log.StandardLogger&profileSQL=true" ; /*打印StackTrace(bug) + sql*/
                "&logger=com.mysql.jdbc.log.Slf4JLogger&profileSQL=true"; /*只打印 sql*/

        boolean useSameConnectionSimultaneously = Boolean.getBoolean("useSameConnectionSimultaneously");
        Connection t1Connection = DriverManager.getConnection(url);
        Connection t2Connection = useSameConnectionSimultaneously
                ? t1Connection
                : DriverManager.getConnection(url);

        t1Connection.setAutoCommit(false);
        t2Connection.setAutoCommit(false);

        System.out.println("useSameConnectionSimultaneously:" + useSameConnectionSimultaneously);
        System.out.println("T1 ConnectionId:" + ((MySQLConnection) t1Connection).getId());
        System.out.println("T2 ConnectionId:" + ((MySQLConnection) t2Connection).getId());

        Thread t1 = new Thread(new MyTaskUnit("T1", t1Connection, true));
        t1.setName("T1");
        t1.start();

        Thread t2 = new Thread(new MyTaskUnit("T2", t2Connection, false));
        t1.setName("T2");
        t2.start();

        t1.join();
        t2.join();

        if (t1Connection != null) {
            t1Connection.close(); //close()会发送 rollback 被MySQL Server
        }

        if (t2Connection != null) {
            t2Connection.close(); //close()会发送 rollback 被MySQL Server
        }

        Connection connection = DriverManager.getConnection(url);
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT field1 FROM test1 WHERE id=1;/*query*/");
        while (resultSet.next()) {
            String field1R = resultSet.getString(1);
            System.out.println(String.format("最后结果:%s", field1R));
        }

        if (connection != null) {
            connection.close();
        }

    }

    public static class MyTaskUnit implements Runnable {

        private Connection connection;
        private String tName;
        private String expectResult;
        private boolean busy;

        MyTaskUnit(String t, Connection connection, boolean busy) {
            this.connection = connection;
            this.tName = t;
            this.expectResult = "BY_" + tName + "_" + (java.time.LocalTime.now());
            this.busy = busy;
        }

        @Override
        public void run() {

            Statement t1Statement = null;
            try {
                t1Statement = connection.createStatement();
                t1Statement.execute(String.format("UPDATE test1 SET field1='%s'  where id=1; /*%s*/ ", expectResult, tName));

                if (busy) {
                    TimeUnit.SECONDS.sleep(2);
                    //假装2s繁忙业务, 给另外 同时操作这个Connection的线程 机会来穿插其他语句
                    //造成本来两个 业务单元/事务 被搞入同一个事务
                }

                //打印结果
                Statement statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery("SELECT field1 FROM test1 WHERE id=1;/*query*/");
                while (resultSet.next()) {
                    String field1R = resultSet.getString(1);
                    String desc = expectResult.equalsIgnoreCase(field1R)
                            ? "【GOOD——看到本业务写入的值】"
                            : "【BAD——看到别业务写入的值, connection错误用法导致，'看上去'不符合MySQL RR】";
                    System.out.println(String.format("%s:->期望:%s,得到:%s,%s", tName, expectResult, field1R, desc));
                }
                connection.commit();

            } catch (SQLException | InterruptedException e) {
                e.printStackTrace();
            }
        }
    }
}