目录
根据代码来看,isolation是针对单个连接而言的,如果对于读和写使用不同的隔离级别会是什么效果呢?
结论
先说结论,如下
write | |||||
read | READ_UNCOMMITTED | READ_COMMITTED | REPEATABLE_READ | SERIALIZABLE | |
READ_UNCOMMITTED | READ_UNCOMMITTED | ||||
READ_COMMITTED | READ_COMMITTED | ||||
REPEATABLE_READ | REPEATABLE_READ | ||||
SERIALIZABLE | SERIALIZABLE |
也就是只有read这个connection的isolation对自己生效了,而write对自己没有影响。
测试代码
测试代码如下,我是使用的MqSql
package com.qbit;
import java.sql.*;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
public class Transaction {
public static void main(String[] args) throws Exception {
Database database = new Mysql();
database.test();
}
}
class Mysql extends Database {
@Override
Connection connection() throws ClassNotFoundException, SQLException {
Class.forName(com.mysql.cj.jdbc.Driver.class.getName());
Connection out = DriverManager.getConnection("jdbc:mysql://172.23.47.6:3306/qbit", "root", "mima");
return out;
}
}
abstract class Database {
private static final String[] ids = {"valueA", "valueB"};
public static final String TRANSACTION_TABLE = "transaction_table_" + (System.currentTimeMillis() % 10000);
abstract Connection connection() throws Exception;
public void test() throws Exception {
Connection connection = connection();
connection.setAutoCommit(true);
connection.createStatement().execute("create table " + TRANSACTION_TABLE + "(id varchar(15),curr_value int )");
int[] isolations = new int[]{Connection.TRANSACTION_READ_UNCOMMITTED, Connection.TRANSACTION_READ_COMMITTED,
Connection.TRANSACTION_REPEATABLE_READ, Connection.TRANSACTION_SERIALIZABLE};
System.out.println("==========update==========");
for (int isolationRead : isolations) {
for (int isolationWrite : isolations) {
init(connection);
var readBefore = connection(isolationRead);
if (null == readBefore) {
continue;
}
var readAfter = connection(isolationRead);
var write = connection(isolationWrite);
printTransaction("---------- Isolation Read:", isolationRead);
printTransaction(" ---------- Isolation Write:", isolationWrite);
System.out.println(" ----------");
update(readBefore, write, readAfter);
for (Connection c : new Connection[]{write, readBefore, readAfter}) {
try {
c.close();
} catch (Exception e) {
System.err.println("close fail");
}
}
}
}
System.out.println();
System.out.println("==========insert==========");
for (int isolationRead : isolations) {
for (int isolationWrite : isolations) {
var readBefore = connection(isolationRead);
if (null == readBefore) {
continue;
}
var readAfter = connection(isolationRead);
var write = connection(isolationWrite);
printTransaction("Isolation Read:", isolationRead);
printTransaction("Isolation Write:", isolationWrite);
insert(readBefore, write, readAfter);
for (Connection c : new Connection[]{write, readBefore, readAfter}) {
try {
c.close();
} catch (Exception e) {
System.err.println("close fail");
}
}
}
}
connection.close();
}
private void init(Connection connection) throws SQLException {
connection.createStatement().execute("truncate " + TRANSACTION_TABLE);
var insert = connection.prepareStatement("insert into " + TRANSACTION_TABLE + " values(?,0)");
for (String id : ids) {
insert.setString(1, id);
insert.executeUpdate();
}
}
private void insert(Connection readBefore, Connection write, Connection readAfter) throws SQLException, InterruptedException {
print("[ReadBefore]init", readBefore, "1=1");
var insert = write.prepareStatement("insert " + TRANSACTION_TABLE + " values(66,?)");
for (var id : ids) {
id = id + '_';
System.out.println("insert " + TRANSACTION_TABLE + " values(66,'" + id + "')");
insert.setString(1, id);
insert.executeUpdate();
print(readBefore, write, readAfter, "1=1");
}
write.commit();
print(readBefore, write, readAfter, "1=1");
}
private void printTransaction(String info, int isolation) {
System.out.print(info);
System.out.print(toString(isolation));
}
private String toString(int isolation) {
switch (isolation) {
case Connection.TRANSACTION_READ_UNCOMMITTED:
return "READ_UNCOMMITTED";
case Connection.TRANSACTION_READ_COMMITTED:
return "READ_COMMITTED";
case Connection.TRANSACTION_REPEATABLE_READ:
return "REPEATABLE_READ";
case Connection.TRANSACTION_SERIALIZABLE:
return "SERIALIZABLE";
default:
return null;
}
}
ExecutorService executorService = Executors.newCachedThreadPool();
private void update(Connection readBefore, Connection write, Connection readAfter) throws SQLException, InterruptedException {
print("[ReadBefore]init", readBefore, "id='" + ids[0] + "'");
var update = write.prepareStatement("update " + TRANSACTION_TABLE + " set curr_value=? where id=?");
var id = ids[0];
for (int i = 1; i < 3; i++) {
System.out.println("update " + TRANSACTION_TABLE + " set curr_value=" + i + " where id='" + id + "'");
update.setInt(1, i);
update.setString(2, id);
update.executeUpdate();
print(readBefore, write, null , "id='"+id+"'");
}
write.commit();
System.out.println("After commit for " + id);
print(readBefore, write, null, "id='" + id + "'");
id = ids[1];
for (int i = 1; i < 3; i++) {
System.out.println("update " + TRANSACTION_TABLE + " set curr_value=" + i + " where id='" + id + "'");
update.setInt(1, i);
update.setString(2, id);
update.executeUpdate();
print(null, write, readAfter, "1=1");
}
write.commit();
System.out.println("After commit for " + id);
print(readBefore, write, readAfter, "1=1");
}
protected void print(Connection readBefore, Connection write, Connection readAfter, String where) throws InterruptedException {
executorService.submit(new Callable<Object>() {
@Override
public Object call() throws SQLException {
print("[write]", write, where);
return null;
}
});
Thread.sleep(400);
if(readBefore!=null) {
executorService.submit(new Callable<Object>() {
@Override
public Object call() throws Exception {
print("[readBefore]", readBefore, where);
return null;
}
});
Thread.sleep(400);
}
if (null == readAfter) {
return;
}
executorService.submit(new Callable<Object>() {
@Override
public Object call() throws Exception {
print("[readAfter]", readAfter, where);
return null;
}
});
Thread.sleep(400);
}
private void print(String info, Connection connection, String where) throws SQLException {
System.out.println("\t" + info + ":");
PreparedStatement select = connection.prepareStatement("select * from " + TRANSACTION_TABLE + " where " + where);
ResultSet resultSet = select.executeQuery();
while (resultSet.next()) {
int currValue = resultSet.getInt(2);
if (currValue == 1 && info.contains("read")) {
System.err.println("\t\t\t" + resultSet.getString(1) + " | " + currValue);
} else {
System.out.println("\t\t\t" + resultSet.getString(1) + " | " + currValue);
}
}
}
private Connection connection(int isolationWrite) throws Exception {
Connection connection = connection();
try {
connection.setTransactionIsolation(isolationWrite);
} catch (SQLException e) {
System.out.println("Unsupported " + toString(isolationWrite));
return null;
}
connection.setAutoCommit(false);
return connection;
}
}
read_uncommited
read_uncommited的打印结果如下(可以看到任何时候readBefore,write,readAfter得到的数据都是一致的)
[ReadBefore]init:
valueA | 0
update transaction_table_3423 set curr_value=1 where id='valueA'
[write]:
valueA | 1
[readBefore]:
valueA | 1
update transaction_table_3423 set curr_value=2 where id='valueA'
[write]:
valueA | 2
[readBefore]:
valueA | 2
After commit for valueA
[write]:
valueA | 2
[readBefore]:
valueA | 2
update transaction_table_3423 set curr_value=1 where id='valueB'
[write]:
valueA | 2
valueB | 1
[readAfter]:
valueA | 2
valueB | 1
update transaction_table_3423 set curr_value=2 where id='valueB'
[write]:
valueA | 2
valueB | 2
[readAfter]:
valueA | 2
valueB | 2
After commit for valueB
[write]:
valueA | 2
valueB | 2
[readBefore]:
valueA | 2
valueB | 2
[readAfter]:
valueA | 2
valueB | 2
read_commited
当read的隔离级别设为read_commited的时候,打印结果如下
[ReadBefore]init:
valueA | 0
update transaction_table_3423 set curr_value=1 where id='valueA'
[write]:
valueA | 1
[readBefore]:
valueA | 0
update transaction_table_3423 set curr_value=2 where id='valueA'
[write]:
valueA | 2
[readBefore]:
valueA | 0
After commit for valueA
[write]:
valueA | 2
[readBefore]:
valueA | 2
update transaction_table_3423 set curr_value=1 where id='valueB'
[write]:
valueA | 2
valueB | 1
[readAfter]:
valueA | 2
valueB | 0
update transaction_table_3423 set curr_value=2 where id='valueB'
[write]:
valueA | 2
valueB | 2
[readAfter]:
valueA | 2
valueB | 0
After commit for valueB
[write]:
valueA | 2
valueB | 2
[readBefore]:
valueA | 2
valueB | 2
[readAfter]:
valueA | 2
valueB | 2
repeatable_read
repeatable_read的结果如下,这是一个有些让人困惑的级别,有空我再单独研究一下。可以看到readBefore对于valueA和valueB的值读取出来永远是0,即使它是在write执行了commit后才去读取的valueB。另外需要说明Oracle是不支持这个隔离级别的
[ReadBefore]init:
valueA | 0
update transaction_table_3423 set curr_value=1 where id='valueA'
[write]:
valueA | 1
[readBefore]:
valueA | 0
update transaction_table_3423 set curr_value=2 where id='valueA'
[write]:
valueA | 2
[readBefore]:
valueA | 0
After commit for valueA
[write]:
valueA | 2
[readBefore]:
valueA | 0
update transaction_table_3423 set curr_value=1 where id='valueB'
[write]:
valueA | 2
valueB | 1
[readAfter]:
valueA | 2
valueB | 0
update transaction_table_3423 set curr_value=2 where id='valueB'
[write]:
valueA | 2
valueB | 2
[readAfter]:
valueA | 2
valueB | 0
After commit for valueB
[write]:
valueA | 2
valueB | 2
[readBefore]:
valueA | 0
valueB | 0
[readAfter]:
valueA | 2
valueB | 0
serializable
观察前面的输出可以知道当write设置隔离级别是serializable时是不会影响read的,当时当read设置serialable时会影响write
---------- Isolation Read:SERIALIZABLE ---------- Isolation Write:READ_UNCOMMITTED ----------
[ReadBefore]init:
valueA | 0
update transaction_table_3423 set curr_value=1 where id='valueA'
Exception in thread "main" com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1347)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025)
at com.qbit.Database.update(Transaction.java:141)
at com.qbit.Database.test(Transaction.java:53)
at com.qbit.Transaction.main(Transaction.java:12)