On TransactionIsolation（0）

本系列主要是研究不同数据库对于各种transaction isolation的处理区别

On TransactionIsolation（1）MySql

On TransactionIsolation（2）Oracle

On TransactionIsolation（3）Postgresql

On TransactionIsolation（4）MsSql

以及两个connection处于设置不同隔离级别的情况：On TransactionIsolation（5）different transaction

针对repeatable read的各种骚操作的专项研究：On TransactionIsolation（6）RepeatableRead

写了一段代码，通过两个connection来连接数据库，一个写，一个读。每次写线程写完都通知读线程来读取数据。

package com.qbit;

import java.sql.*;
import java.util.concurrent.Callable;
import java.util.concurrent.Executors;
import java.util.concurrent.atomic.AtomicBoolean;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

public class Main {
    private static final String[] ids={"valueA","valueB"};
    public static void main(String[] args) throws InterruptedException, SQLException {
        Lock lock=new ReentrantLock();
        initValue();
        lock.lock();
        boolean lockd=true;
        AtomicBoolean finish=new AtomicBoolean();
        Callable<?> read=new Callable<Object>() {
            @Override
            public Object call() throws Exception {
                Connection connection = connection();
                connection.setAutoCommit(false);
                while (!finish.get()) {
                    lock.lock();
                    PreparedStatement select = connection.prepareStatement("select * from transaction_table");
                    ResultSet resultSet = select.executeQuery();
                    while (resultSet.next()) {
                        System.out.println("\t" + resultSet.getString(1) + " | " + resultSet.getInt(2));
                    }
                    lock.unlock();
                    Thread.sleep(1000);
                }
                connection.close();
                return null;
            }
        };
        var pool=Executors.newCachedThreadPool();
        pool.submit(read);
        Connection connection=connection();
        connection.setAutoCommit(false);
        var update="update transaction_table set curr_value=? where id=?";
        var prepareStatement=connection.prepareStatement(update);
        for(String id:ids) {
            for (int i = 1; i < 5; i++) {
                if(lockd){
                    lockd=false;
                }else {
                    lock.lock();
                }
                prepareStatement.setInt(1, i);
                prepareStatement.setString(2, id);
                prepareStatement.executeUpdate();
                System.out.println(update.replace("id=?","id="+id).replace('?',(char)(i+'0')));
                lock.unlock();
                Thread.sleep(1000);
            }
            connection.commit();
        }
        connection.close();
        finish.set(true);
        pool.shutdown();
    }

    private static void initValue() throws SQLException {
        Connection connection=connection();
        connection.setAutoCommit(false);
        connection.createStatement().execute("drop table if exists transaction_table");
        connection.createStatement().execute("create table transaction_table(id varchar(15),curr_value int )");
        var insert= connection.prepareStatement("insert into transaction_table values(?,0)");
        for(String id:ids) {
            insert.setString(1, id);
            insert.executeUpdate();
        }
        connection.commit();
    }

    private static Connection connection() {
        try {
            Class.forName(com.mysql.cj.jdbc.Driver.class.getName());
            Connection out= DriverManager.getConnection("jdbc:mysql://172.23.47.6:3306/qbit","root","mima");
            out.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
            return out;
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }
}

将会测试各种数据库及各种隔离级别，从代码上来看，对同一个数据库的不同connection可以使用不同的隔离级别。