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 Test2 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 con = DriverManager.getConnection(url, userName, password);

		while (!hasBug) {
			Statement stat = con.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(10,6), primary key(id));");
			stat.execute("create table yTable (id integer, secCol integer, col decimal(10,6), primary key(id));");
			stat.execute("create index xIndex on xTable (secCol)");
			stat.execute("create index yIndex on yTable (secCol)");
			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 Test2()).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();
		}
		con.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 transaction");
				stat.execute("set transaction ISOLATION LEVEL SERIALIZABLE");

				ResultSet rs;
				try {
					if (Math.random() > 0.5) {// read x, write y
						rs = stat.executeQuery("select col from xTable where id=" + pk);
						rs.next();
						BigDecimal x = new BigDecimal(rs.getDouble(1));
						BigDecimal p = x.multiply(new BigDecimal(0.9));
						stat.execute("update yTable set col = col- col*0.9-" + p.toPlainString() + "where id=" + pk);
					} else {// read y, write x
						rs = stat.executeQuery("select col from yTable where id=" + pk);
						rs.next();
						BigDecimal y = new BigDecimal(rs.getDouble(1));
						BigDecimal p = y.multiply(new BigDecimal(0.9));
						stat.execute("update xTable set col = col- col*0.9-" + p.toPlainString() + "where id=" + pk);
					}
					stat.execute("commit");

				} catch (org.postgresql.util.PSQLException e) {
					exceptionCounter.incrementAndGet();
					stat.execute("rollback");
				}
			}
			conn.close();
			cdl.countDown();

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

}