事务的原则的实现

内容

1. 事务原则
2. SQL实现数据库事务
3. 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();