ConcurrencyDerbyExample.java

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.util.Random;

public class Example {

	public static void main(String[] args) {
		Connection c = createNewConnection();
		setUp(c);
		
		try {
			Thread t1 = createThread(1);
			Thread t2 = createThread(2);
			t1.join();
			t2.join();
		} catch (InterruptedException e) {
			e.printStackTrace();
		}
		
		PreparedStatement ps;
		ResultSet rs;
		try {
			ps = c.prepareStatement("SELECT * FROM TESTA");
			rs = ps.executeQuery();
			if (rs.next()) {
				System.out.println("A:" + rs.getInt(2));
			}
			
			ps = c.prepareStatement("SELECT * FROM TESTB");
			rs = ps.executeQuery();
			if (rs.next()) {
				System.out.println("B:" + rs.getInt(2));
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		

	}

	private static Thread createThread(int i) {
		final Connection c = createNewConnection();
		Thread t = new Thread() {
			public void run() {
				try {
					for (int i = 0; i < 500; i++) {
						c.setAutoCommit(false);
						Savepoint s = c.setSavepoint("Thread" + i);
						
						try {
							PreparedStatement ps = c.prepareStatement("SELECT * FROM TESTA", ResultSet.CONCUR_UPDATABLE, ResultSet.TYPE_FORWARD_ONLY);
							ResultSet rs = ps.executeQuery();
							if (rs.next()) {
								rs.updateInt(2, rs.getInt(2)-1);
								rs.updateRow();
							}
							
							try {
								Thread.sleep(new Random().nextInt(50));
							} catch (InterruptedException e) {
								e.printStackTrace();
							}
							
							PreparedStatement ps2 = c.prepareStatement("SELECT * FROM TESTB", ResultSet.CONCUR_UPDATABLE, ResultSet.TYPE_FORWARD_ONLY);
							ResultSet rs2 = ps2.executeQuery();
							if (rs2.next()) {
								rs2.updateInt(2, rs2.getInt(2)+1);
								rs2.updateRow();
							}
							c.commit();
						} catch (SQLException e) {
							e.printStackTrace();
							System.out.println("Rolling back");
							c.rollback(s);
						}
						c.setAutoCommit(true);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		};
		t.run();
		return t;
	}

	private static void setUp(Connection c) {
		try {
			PreparedStatement p = c.prepareStatement(
					"CREATE TABLE TESTA (ID INTEGER PRIMARY KEY NOT NULL, a INTEGER NOT NULL)");
			p.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {
			PreparedStatement p = c.prepareStatement(
					"CREATE TABLE TESTB (ID INTEGER PRIMARY KEY NOT NULL, b INTEGER NOT NULL)");
			p.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		try {
			PreparedStatement p = c.prepareStatement("INSERT INTO TESTA VALUES (1, 10000)");
			p.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {
			PreparedStatement p = c.prepareStatement("INSERT INTO TESTB VALUES (1, 0)");
			p.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private static Connection createNewConnection() {
		try {
			Runtime.getRuntime().exec("rm -r tmpdb");
		} catch (IOException e1) {
			e1.printStackTrace();
		}
		try {
			return DriverManager.getConnection("jdbc:derby:tmpdb;create=true", "T", "");
			// TODO:
			// connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
}