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://*.*.*.*:*/dbName";
	public static String user = "***";
	public static String password = "***";
	public static String driver = "org.postgresql.Driver";
	public static CountDownLatch cdl;
	public static int testThreadNum = 50;
	public static int executeTimesPerThread = 100;
	public static AtomicInteger exceptionCounter = new AtomicInteger(0);

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

		Class.forName(driver);
		Connection con = DriverManager.getConnection(url, user, password);

		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("drop index if exists xIndex");
		stat.execute("drop index if exists yIndex");
		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()) {
			System.out.println("less than 0£º" + rs.getInt(1));
		}
		con.close();
	}

	@Override
	public void run() {
		try {
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(url, user, 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 + "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 + "where id=" + pk);
					}
					stat.execute("commit");

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

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

}