import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


/**
 * @author maxim
 */
public class DatabaseDeadlockTest implements Runnable {

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    private String url = "jdbc:mysql://localhost:3306/deadlock";
    private String user = "root";
    private String password = "";

    protected Connection connection;
    private Thread thread;
    private int index;

    public DatabaseDeadlockTest(int i) throws SQLException {
        index = i;
        connection = DriverManager.getConnection(url, user, password);
        thread = new Thread(this, "[Worker " + index + "]");
        thread.start();
    }

    public void run() {
        // wait all threads to start in the same time 
        synchronized (this) {
            try {
                wait(500);
            } catch (InterruptedException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

        try {
            for (int i = 1; i < 300; i++) {
                try {
                    connection.setAutoCommit(false);
                    Statement stmt = connection.createStatement();

                    // select from first table
                    stmt.execute("SELECT * FROM test WHERE id IN (" + index
                            + ")");

                    // delete from ref table 
                    stmt.executeUpdate("DELETE FROM test1 WHERE test_id_ref = "
                            + index);
                    
                    for (int j = 1; j < 30; j++) {
                        stmt.executeUpdate("INSERT INTO test1 (value, test_id_ref) VALUES ("
                                + (int) (Math.random() * 10) + ", " + index
                                + ")");
                    }

                    stmt.close();
                    connection.commit();
                } catch (SQLException e) {
                    e.printStackTrace();
                    try {
                        ResultSet rs = connection.createStatement().executeQuery("SHOW ENGINE INNODB STATUS");

                        if (rs.next()) {
                            log(rs.getString(1));
                        }
                    } catch (SQLException e1) {
                        e1.printStackTrace();
                    }
                    System.exit(-1);
                }
            }
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            log("done");
        }
        
    }

    void log(String log) {
        System.out.println(thread.getName() + ": " + log);
    }

    /**
     * @param args
     */
    public static void main(String[] args) {
        try {
            DatabaseDeadlockTest ddt1 = new DatabaseDeadlockTest(1);
            DatabaseDeadlockTest ddt2 = new DatabaseDeadlockTest(2);
            DatabaseDeadlockTest ddt3 = new DatabaseDeadlockTest(3);
            DatabaseDeadlockTest ddt4 = new DatabaseDeadlockTest(4);
            DatabaseDeadlockTest ddt5 = new DatabaseDeadlockTest(5);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}