内容
- 事务原则
- SQL实现数据库事务
- JDBC实现事务管理
事务
概述
事务是以一种客观、一致的方式,方式和操作数据库中数据的程序单元
原则
- 原子性 A
- 一致性 C
- 隔离性 I
- 持久性 D
实例
数据库事务
隔离级别
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ(默认)
- SERIALIZABLE
实例
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事务
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(); // 此处运行打断点,观察效果
plusPS.close();
minusPS.close();
conn.close();
注意:for update 会将select语句加锁,如果没有加where条件或者条件没有索引会加表锁,因此这样使用select * from t_user where id = 1 for update加的行级锁。
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();