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

/**
 * @author Maxim
 */
public class MysqlDeadlockTest implements Runnable {

    /* 
     * CREATE TABLE test.deadlock_test (
     *      id BIGINT  NOT NULL AUTO_INCREMENT,
     *      value BIGINT NOT NULL,
     *      PRIMARY KEY(id)
     *      ) TYPE = InnoDB; 
     * 
     */

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

    private String url = "jdbc:mysql://localhost:3306/test?zeroDateTimeBehavior=convertToNull&useServerPrepStmts=false";
    private String user = "";
    private String password = "";
    private Thread thread;

    //connection for each worker thread
    protected Connection connection;
    //unique values for each worker thread
    protected int start, end;

    public MysqlDeadlockTest(int start, int end) throws SQLException {
        connection = DriverManager.getConnection(url, user, password);
        this.start = start;
        this.end = end;
        thread = new Thread(this);
        thread.start();
    }

    public void startTransaction() throws SQLException {
        System.out.println(this + " Before start transaction");
        connection.setAutoCommit(false);
    }

    public void endTransaction() throws SQLException {
        connection.commit();
        System.out.println(this + " After end transaction");
    }

    public void run() {
        try {
            startTransaction();
            try {
                for (int i = start; i < end; i++) {
                    Statement stmt = connection.createStatement();
                    try {
                        String insert = "insert into deadlock_test (value) "
                                + "values (" + i + ");";
                        stmt.executeUpdate(insert);

                        String delete = "delete from deadlock_test where value = "
                                + i;
                        stmt.executeUpdate(delete);

                    } finally {
                        stmt.close();
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();

            } finally {
                endTransaction();
            }

        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            try {
                connection.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
    }

    /**
     * main
     */
    public static void main(String[] args) {
        try {
            new MysqlDeadlockTest(0, 100);
            new MysqlDeadlockTest(101, 200);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}