Transaction

DML: INSERT, UPDATE, DELETE
DDL: CREATE TABLE, ALTER TABLE
DCL: GRANT

Transaction starts

• when the first DML runs.

Transaction ends:

• run commit() or rollback
• when run a DDL, will trigger commit()
• when run a DCL, will trigger commit()
• when database disconnect
• when run a DML, but the DML failed. Then it triggers rollback

ACID

• Atomicity: all succeed or all die
• Consistency: if error, must rollback to exactly original
• Isolation: other transactions will only see either before/after the change, cannot see in the middle
• Durability: if succeed, it will affect permanently

Isolation 4 levels(from low to high, but efficiency is from high to low)

1. Read Uncommitted
2. Read Committed(commonly used)
3. Repeatable Read
4. Serializable(lock table)

Example of Transaction

@Test
public void test(){
 List<String> SQLs = new ArrayList<String>();
 List<Object[]> parameters = new ArrayList<Object[]>();
 String sql1 = "INSERT INTO `javaweb`.`users` (`username`, `password`, `email`) VALUES (?,?,?);";
 Object[] parameters1 = new Object[]{"abcd1", "abc", "abc"};
 String sql2 = "INSERT INTO `javaweb`.`users` (`username`, `password`, `email`) VALUES (?,?,?);";
 Object[] parameters2 = new Object[]{"abcd2", "abc", "abc"};
 SQLs.add(sql1);
 SQLs.add(sql2);
 parameters.add(parameters1);
 parameters.add(parameters2);
 executeQuery(SQLs, parameters);
}
public void executeQuery(List<String> SQLs,List<Object[]> parameters){
 try {
  Class.forName("com.mysql.jdbc.Driver");
 } catch (ClassNotFoundException e) {
  return;
 }
 try(Connection conn = DriverManager.getConnection("jdbc:mysql://192.168.56.101:3306/javaweb","root","root");){
  conn.setAutoCommit(false);
  for(int i=0;i<SQLs.size();i++){
   try (
    PreparedStatement ps = createPreparedStatement(conn,SQLs.get(i),parameters.get(i));
   ) {
    int resultCount = ps.executeUpdate();
    System.out.println("Update succeed, updated result count:"+resultCount);
   } catch (SQLException e) {
    e.printStackTrace();
    conn.rollback();
    return;
   }
   try {
    Thread.sleep(6000);
   } catch (InterruptedException e) {
    e.printStackTrace();
   }
  }
  conn.commit();
 } catch (SQLException e) {
  
 }
 return;
}