content

1. Transaction principle
2. Implementing Database Transaction with SQL
3. JDBC Implements Transaction Management

affair

Summary

Transaction is a program unit that operates data in the database in an objective and consistent manner.

Principle

• Atomicity A
• Consistency C
• Isolated I
• Persistent D

Example

Database transaction

Isolation level

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ (default)
• SERIALIZABLE

Example

start transaction;
update t_user set amount = amount + 100 where id = 1
update t_user set amount = amount - 100 where id = 2
commit;

start transaction;
selelct *  from t_user
selelct *  from t_user where id = 1
commit;

JDBC transaction

Connection conn = getConnection();
conn.setAutoCommitI(true);
String plusSQL = "update t_user set amount = amount + 100 where id = ?";
PreparedStatement plusPS = conn.preparedStatement(plusSQL);
String minusSQL = "update t_user set amount = amount - 100 where id =  ?";
PreparedStatement minusPS = conn.preparedStatement(minusSQL);

plusPS.setString(1,"1");
plusPS.executeUpdate();  
minusPS.setString(1,"2");
minusPS.executeUpdate();
conn.commit(); // Run the interruption point here and observe the effect
plusPS.close();
minusPS.close();
conn.close();

Note: For update, select statements are locked, and table locks are added if there is no where condition or condition without index, so row-level locks added by select * from t_user where id = 1 for update are used in this way.

Connection conn = getConnection();
conn.setAutoCommitI(true);
String query = "select * from  t_user where id = 1  for update";
PreparedStatement ps
= conn.preparedStatement(query);
ResultSet rs = ps.executeQuery();
Long amount = 0L;
while(rs.next()){
   amount = rs.getLong(1);
}

String minusSQL = "update t_user set amount = ? where id =  ?";
PreparedStatement minusPS = conn.preparedStatement(minusSQL);
minusPS.setLong(1,amount - 100)
minusPS.setString(2,"1");
minusPS.executeUpdate();  

conn.commit();
ps.close();
minusPS.close();
conn.close();