本系列主要是研究不同数据库对于各种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可以使用不同的隔离级别。