JDBC事务
- jdbc默认情况自动提交,:在jdbc中执行一条DML就执行一条
- 将事务改为手动提交
- 在事务执行的过程中任何一步出现一场都要回滚
开启事务
//关闭自动提交,开启事务
conn.setAutoCommit(false);//事务之和DML语句有关
提交事务,只有在提交事务的时候才运行
conn.commit();//提交事务
回滚事务
conn.rollback();
package jdbc_test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class JDBC_test {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/lll?serverTimezone =UTC";
String user = "root";
String password = "123123";
conn = DriverManager.getConnection(url,user,password);
//关闭自动提交,开启事务
conn.setAutoCommit(false);//事务之和DML语句有关
String sql = "delete from t_user where id = ?";
ps = conn.prepareStatement(sql);
ps.setInt(1, 2);
int count = ps.executeUpdate();
System.out.println(count);
conn.commit();//提交事务
} catch (Exception e) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}//回滚事务
}
finally {
if(ps!=null) {
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
行级锁(悲观锁)
多线程访问同一张表的相同记录,其他线程排队
- java使用synchronized
- 数据库设置隔离级别为 :串行化seriallizable
- SQL使用行级锁 :for update
锁,同一张表中,相同数据起作用
只有在一个一个程序运行完毕以后另外一个程序才能去访问数据
可减小数据库的冲突
如下表1表2:在表1的程序中先对t_user表中的数据进行访问,并且进行了行级锁处理 for update ,所以只有在表1处理完毕以后表2才可以对表中的数据进行修改
、表1
public class JDBC_test01 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps= null;
ResultSet rs = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/lll?serverTimezone =UTC";
String user = "root";
String password = "123123";
conn = DriverManager.getConnection(url,user,password);
conn.setAutoCommit(false);
String sql = "select * from t_user where username = ? for updata";//锁数据
ps = conn.prepareStatement(sql);
ps.setString(1, "manager");
rs = ps.executeQuery();
while(rs.next()) {
String username = rs.getString("username");
System.out.println(username);
}
//提交事务
conn.commit();
} catch (Exception e) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}finally {
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(ps!=null) {
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
表2
package jdbc_test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JDBC_test02 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps= null;
ResultSet rs = null;
int count = 0;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/lll?serverTimezone =UTC";
String user = "root";
String password = "123123";
conn = DriverManager.getConnection(url,user,password);
conn.setAutoCommit(false);
String sql = "update t_user set username = ? where id = ?";
ps = conn.prepareStatement(sql);
ps.setString(1, "String");
ps.setInt(2, 3);
count = ps.executeUpdate();
System.out.println(count);
//提交事务
conn.commit();
} catch (Exception e) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}finally {
if(ps != null) {
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}