import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.atomic.AtomicInteger;

public class Test1 implements Runnable {

	// Database connection infomation
	public static String url = "jdbc:postgresql://10.11.6.118:5432/postgres";
	public static String userName = "zsy";
	public static String password = "zsy";
	public static String driver = "org.postgresql.Driver";

	public static CountDownLatch cdl;
	public static int testThreadNum = 50;
	public static int executeTimesPerThread = 200;
	public static AtomicInteger exceptionCounter = new AtomicInteger(0);

	public static boolean hasBug = false;

	public static void main(String[] args) throws ClassNotFoundException, SQLException, InterruptedException {

		Class.forName(driver);
		Connection conn = DriverManager.getConnection(url, userName, password);

		while (!hasBug) {
			Statement stat = conn.createStatement();
			stat.execute("drop table if exists xtable");
			stat.execute("drop table if exists ytable");
			stat.execute("create table xTable (id integer, secCol integer, col decimal(30,26), primary key(id));");
			stat.execute("create table yTable (id integer, secCol integer, col decimal(30,26), primary key(id));");
			for (int i = 1; i <= 50; ++i) {
				stat.execute("insert into xTable values (" + i + "," + i + "," + "1.0)");
				stat.execute("insert into yTable values (" + i + "," + i + "," + "1.0)");
			}

			cdl = new CountDownLatch(testThreadNum);
			for (int i = 0; i < testThreadNum; ++i) {
				new Thread(new Test1()).start();
			}
			cdl.await();

			ResultSet rs = stat.executeQuery(
					"select xtable.id from xtable join ytable using (id) where xtable.col + ytable.col < 0");
			while (rs.next()) {
				hasBug = true;
				System.out.println("less than 0 (xtable.id)£º" + rs.getInt(1));
			}
			System.out.println("Number of exceptions: " + exceptionCounter.intValue());
			stat.close();
		}
		conn.close();
	}

	@Override
	public void run() {
		try {
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(url, userName, password);

			conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
			Statement stat = conn.createStatement();
			for (int i = 0; i < executeTimesPerThread; ++i) {
				int pk = (int) (1 + Math.random() * 50);
				stat.execute("begin");
				stat.execute("set transaction ISOLATION LEVEL SERIALIZABLE");
				ResultSet rs;
				try {
					rs = stat.executeQuery("select col from xtable where id=" + pk);
					rs.next();
					BigDecimal x = new BigDecimal(rs.getDouble(1));

					rs = stat.executeQuery("select col from ytable where id=" + pk);
					rs.next();
					BigDecimal y = new BigDecimal(rs.getDouble(1));

					BigDecimal p = x.add(y).multiply(new BigDecimal(0.9));
					if (Math.random() > 0.5) {
						stat.execute("update xtable set col = col-" + p.toPlainString() + "where id =" + pk);
					} else {
						stat.execute("update ytable set col = col-" + p.toPlainString() + "where id =" + pk);
					}
					stat.execute("commit");
				} catch (com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException
						| org.postgresql.util.PSQLException e) {
					exceptionCounter.incrementAndGet();
					stat.execute("rollback");
				}
			}
			conn.close();
			cdl.countDown();

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

}