事务_事务的保存点_事务的隔离级别
事务------------------------------------------------------------------------------------------
事务的保存点(SavePoint)------------------------------------------------------------------------------------------
当只想撤销事务中的部分操作时可使用SavePoint
隔离级别
|
脏读
|
不可重复读
|
幻读
|
读未提交(Read uncommitted)
|
V
|
V
|
V
|
读已提交(Read committed)
|
x
|
V
|
V
|
可重复读(Repeatable read)
|
x
|
x
|
V
|
可串行化(Serializable )
|
x
|
x
|
x
|
数据库:
CREATE DATABASE jdbc;
USE jdbc;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`birthday` date DEFAULT NULL,
`money` float unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=gbk;
insert into user(name,birthday,money)values('zhangsan','1987-10-01',400);
insert into user(name,birthday,money)values('lisi','1988-09-14',35);
insert into user(name,birthday,money)values('wangwu','1947-07-09',670);
TxTest.java
package com.dwt1220;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
public class TxTest {
public static void main(String[] args) throws Exception {
test();
}
static void test() throws SQLException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
Savepoint savepoint = null;
try {
conn = JdbcUtils.getConnection();
/************打开事务*****************/
conn.setAutoCommit(false);
/************设置事务的隔离级别
* TRANSACTION_NONE--没有隔离级别
* TRANSACTION_READ_UNCOMMITTED--读未提交
* TRANSACTION_READ_COMMITTED--读已提交
* TRANSACTION_REPEATABLE_READ--可重复读
* TRANSACTION_SERIALIZABLE--可串行化
* *****************/
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
st = conn.createStatement();
String sql = "update user set money=money-10 where id=1";
st.executeUpdate(sql);
/************设置保存点*****************/
savepoint = conn.setSavepoint();
sql = "update user set money=money-10 where id=3";
st.executeUpdate(sql);
sql = "select money from user where id=2";
st = conn.prepareStatement(sql);
rs = st.executeQuery(sql);
float money = 0.0f;
if (rs.next()) {
money = rs.getFloat("money");
}
if (money > 300) {
throw new RuntimeException("已超过最大值!");
}
sql = "update user set money=money+10 where id=2";
st.executeUpdate(sql);
/************提交事务*****************/
conn.commit();
} catch (RuntimeException e) {
if (conn != null && savepoint!=null ) {
/************回滚到保存点*****************/
conn.rollback(savepoint);
conn.commit();//提交
throw e;
}
} catch (SQLException e) {
if (conn != null) {
conn.rollback();
throw e;
}
} finally {
JdbcUtils.free(rs, st, conn);
}
}
}