mysql默认隔离级别多事务修改查询结果粗略验证

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBC {
    public static void main(String[] args) throws ClassNotFoundException, SQLException, InterruptedException {
        Class.forName("com.mysql.jdbc.Driver");
        new Thread(new Runnable() {
            @Override
            public void run() {
                Connection connention = null;
                ResultSet resultSet = null;
                Statement statement = null;
                try {
                    connention = DriverManager.getConnection("jdbc:mysql://*/*", "*", "*");
                    String session = "set session transaction isolation level REPEATABLE READ"; //REPEATABLE-READ
                    statement = connention.createStatement();
                    statement.executeUpdate(session);
                    connention.setAutoCommit(false);
                    String sql = "SELECT * FROM pls_city for update"; //准备sql语句
                    statement = connention.createStatement();
//执行
                    resultSet = statement.executeQuery(sql);
                    while (resultSet.next()) {
                        System.out.println(resultSet.getString(1) + "," + resultSet.getString(2) + "," + resultSet.getInt(3) + "," + resultSet.getString(4));
                    }
                    System.out.println("线程1查询后开始睡眠8s");
                    Thread.sleep(1000*8);

                    System.out.println("线程1睡了8s后再查");
                    String sql1 = "SELECT * FROM pls_city where 1 = 1"; //准备sql语句
                    Statement statement1 = connention.createStatement();
                    ResultSet resultSet1 = statement1.executeQuery(sql1);
                    while (resultSet1.next()) {
                        System.out.println(resultSet1.getString(1) + "," + resultSet1.getString(2) + "," + resultSet1.getInt(3) + "," + resultSet1.getString(4));
                    }

                    connention.commit();
                    System.out.println("线程1提交");
                } catch (Exception e) {
                    e.printStackTrace();
                }finally {
                    try {
                        resultSet.close();
                        statement.close();
                        connention.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }

                }
            }
        },"线程1").start();

        Thread.sleep(1000);
        new Thread(new Runnable() {
            @Override
            public void run() {
                Connection connention = null;
                Statement statement = null;
                try {
                    connention = DriverManager.getConnection("jdbc:mysql://*/*", "*", "*");
                    connention.setAutoCommit(false);
                    System.out.println("线程2启动");
                    String sql = "UPDATE pls_city SET CITY_CAR_CODE = 2"; //准备sql语句
                    statement = connention.createStatement();
                    statement.executeUpdate(sql);
                    System.out.println("更新完成");
                    connention.commit();
                    System.out.println("线程2提交");
                } catch (Exception e) {
                    e.printStackTrace();
                }finally {
                    try {
                        statement.close();
                        connention.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }

                }
            }
        },"线程2").start();
    }
}
import java.sql.*;

//1线程先启动未提交,2线程后启动提交,2中修改在1中查询不可见,符合默认隔离级别;1保持未提交,在2修改且提交后再改然后查询,则为最新结果(包含2的修改)。
public class TransactionIsolationLevel {
    public static void main(String[] args) throws ClassNotFoundException, SQLException, InterruptedException {
        Class.forName("com.mysql.jdbc.Driver");
        new Thread(new Runnable() {
            @Override
            public void run() {
                Connection connention = null;
                ResultSet resultSet = null;
                Statement statement = null;
                try {
                    connention = DriverManager.getConnection("jdbc:mysql://***/**", "**", "**");

                    connention.setAutoCommit(false);
                    String sql = "SELECT * FROM pls_city"; //准备sql语句
                    statement = connention.createStatement();
//执行
                    resultSet = statement.executeQuery(sql);
                    while (resultSet.next()) {
                        System.out.println(resultSet.getString(1) + "," + resultSet.getString(2) + "," + resultSet.getInt(3) + "," + resultSet.getString(4));
                    }
                    System.out.println("线程1查询后开始睡眠8s");
                    Thread.sleep(1000*8);

                    String sql1 = "UPDATE pls_city SET CITY_CAR_CODE = CITY_CAR_CODE+1"; //准备sql语句
                    statement = connention.createStatement();
                    statement.executeUpdate(sql1);
                    resultSet = statement.executeQuery(sql);

                    while (resultSet.next()) {
                        System.out.println(resultSet.getString(1) + "," + resultSet.getString(2) + "," + resultSet.getInt(3) + "," + resultSet.getString(4));
                    }

                    connention.commit();
                    System.out.println("线程1提交");
                } catch (Exception e) {
                    e.printStackTrace();
                }finally {
                    try {
                        resultSet.close();
                        statement.close();
                        connention.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }

                }
            }
        },"线程1").start();

        Thread.sleep(1000);
        new Thread(new Runnable() {
            @Override
            public void run() {
                Connection connention = null;
                Statement statement = null;
                try {
                    connention = DriverManager.getConnection("jdbc:mysql://**/**", "**", "**");
                    connention.setAutoCommit(false);
                    System.out.println("线程2启动");
                    String sql = "UPDATE pls_city SET CITY_CAR_CODE = CITY_CAR_CODE+1"; //准备sql语句
                    statement = connention.createStatement();
                    statement.executeUpdate(sql);
                    System.out.println("更新完成");
                    connention.commit();
                    System.out.println("线程2提交");
                } catch (Exception e) {
                    e.printStackTrace();
                }finally {
                    try {
                        statement.close();
                        connention.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }

                }
            }
        },"线程2").start();
    }
}

 

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值