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

最新推荐文章于 2022-07-22 11:50:27 发布

nice_hui_

最新推荐文章于 2022-07-22 11:50:27 发布

阅读量142

点赞数 1

分类专栏： MySQL

本文链接：https://blog.csdn.net/nice_hui_/article/details/96777118

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

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();
    }
}