gistfile1.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.concurrent.TimeUnit;

public class TT {

	private static final String url = "jdbc:postgresql://localhost/postgres?user=postgres&password=q";
	
	private static final String SQL_SETUP_CONTEXT = 
		"DROP TABLE IF EXISTS accounts;"+"\n"+
		"CREATE TABLE accounts(id int, amount int);"+"\n"+
		"INSERT INTO accounts VALUES(1,100);"+"\n"+
		"INSERT INTO accounts VALUES(2,100)";
	
	private static final String SQL_AMOUNT_DEDUCTING_FUNCTION = 
		"CREATE OR REPLACE FUNCTION test (id1 int, id2 int) RETURNS boolean AS $$"+"\n"+
		"BEGIN"+"\n"+
		//"\tUPDATE accounts SET amount=amount WHERE id IN (id1,id2);"+"\n"+
		//"\tUPDATE accounts SET amount=amount WHERE id=id2;"+"\n"+
		"\tIF (SELECT SUM(amount) FROM accounts WHERE id IN (id1,id2)) >= 0 THEN"+"\n"+
		"\t\tUPDATE accounts SET amount=amount-200 WHERE id=id1;"+"\n"+
		"\t\tRETURN true;"+"\n"+
		"\tEND IF;"+"\n"+
		"\tRETURN false;"+"\n"+
		"END;"+"\n"+
		"$$ LANGUAGE plpgsql;";
	
	private static final String SQL_UPDATE_AMOUNTS = 
		"UPDATE accounts SET amount = 100";
	
	private static final String SQL_GET_AMOUNTS = 
		"SELECT amount FROM accounts ORDER BY id";
	
	private static final String SQL_DEDUCT_AMOUNT = 
		"START TRANSACTION ISOLATION LEVEL SERIALIZABLE;"+"\n"+
		"SELECT * FROM test(?,?);"+"\n"+
		"END TRANSACTION;";
	
	
	public static void main(String[] args) {
		try {
			Class.forName("org.postgresql.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return;
		}
		
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(url);
		} catch (SQLException e) {
			e.printStackTrace();
			return;
		}
		Statement stmt = null;
		try{
			stmt = conn.createStatement();
		}
		catch(SQLException e){
			e.printStackTrace();
			try {
				conn.close();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			return;
		}
		
		try {
			stmt.execute(SQL_SETUP_CONTEXT);
			stmt.execute(SQL_AMOUNT_DEDUCTING_FUNCTION);
			
			Callable<Boolean> deductMoneyFromAccnt1 = new Callable<Boolean>() {
				@Override
				public Boolean call() {
					return deductMoneyFromAccount(1,2);
				}
			};
			Callable<Boolean> deductMoneyFromAccnt2 = new Callable<Boolean>() {
				@Override
				public Boolean call() {
					return deductMoneyFromAccount(2,1);
				}
			};
			ExecutorService e = Executors.newFixedThreadPool(2);
			
			int cnt=0;
			while(cnt++ < 10){
				stmt.executeUpdate(SQL_UPDATE_AMOUNTS);
				
				Future<Boolean> futureAccnt1 = e.submit(deductMoneyFromAccnt1);
				Future<Boolean> futureAccnt2 = e.submit(deductMoneyFromAccnt2);
			
				try {
					futureAccnt1.get();
					futureAccnt2.get();
				} catch (Exception e1) {
					e1.printStackTrace();
				}
			
				ResultSet rs = stmt.executeQuery(SQL_GET_AMOUNTS);
				rs.next(); int amount1 = rs.getInt(1);
				rs.next(); int amount2 = rs.getInt(1);
				System.out.println("Amounts=["+amount1+","+amount2+"]\n");
				rs.close();
			}
			
			e.shutdown();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			try {
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private static Boolean deductMoneyFromAccount(int id1, int id2){
		/*try {
			TimeUnit.MILLISECONDS.sleep((id1 - 1) * 5);
		} catch (InterruptedException e1) {
			e1.printStackTrace();
		}*/
		Connection        conn  = null;
		PreparedStatement pstmt = null;
		try {
			conn = DriverManager.getConnection(url);
			pstmt = conn.prepareStatement(SQL_DEDUCT_AMOUNT);
			pstmt.setInt(1, id1);
			pstmt.setInt(2, id2);
			boolean r = pstmt.execute();
			return r;
		} catch (SQLException e) {
			System.out.println("Deducting from account="+id1+" gave "+e.getMessage());
		}
		finally{
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
}