On TransactionIsolation（6）RepeatableRead

书接上回，提到了repeatable read到底是怎样的情况，从上次的例子看出来有些即使没有没读取的数据，在repeatable read的情况也会读取历史数据，这样做有个好处就是如果先前读的数据和后来读的数据有着某种联系，那么repeatable read可以保证多次读取的数据是内在一致的。

对于同一个表里，多行数据会读取历史信息（无论是否被读取过），那么不同表的数据呢？更进一步，如果DDL语句会体现出来么？测试代码如下

package com.qbit;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.LinkedHashMap;
import java.util.Map;

public class RepeatableRead {
    static abstract class Database {
        private static final String TABLE_NAME = "repeatable_read_" + (System.currentTimeMillis() % 10000);

        public void test() throws Exception {
            Connection connection = connection();
            String pkTable = TABLE_NAME + "_pk";
            String oldTalbe=TABLE_NAME+"_old";
            for (String table : new String[]{TABLE_NAME, pkTable,oldTalbe}) {
                connection.createStatement().execute("create table " + table + "(id varchar(15),curr_value int )");
                connection.createStatement().execute("insert into " + table + " values('delete',0)");
                connection.createStatement().execute("insert into " + table + " values('update',0)");
                connection.createStatement().execute("insert into " + table + " values('delete&insert',0)");
            }
            connection.createStatement().execute(pk(pkTable));
            Map<String ,Connection> connections=new LinkedHashMap<>();
            Connection write = connection();
            connections.put("WRITE",write);
            Connection newConnection=connection();
            connections.put("NEW",newConnection);
            Connection pkConnection = connection();
            connections.put("PRIMARY",pkConnection);
            Connection noPkConnection = connection();
            connections.put("NO_PK",noPkConnection);
            for (Connection c : connections.values()) {
                c.setAutoCommit(false);
                c.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
            }
            for(String table:new String[]{pkTable,oldTalbe}) {
                pkConnection.createStatement().executeQuery("select * from " + table + " where id='delete'");
            }
            noPkConnection.createStatement().executeQuery("select * from " + TABLE_NAME + " where id='delete'");
            for (String table : new String[]{TABLE_NAME, pkTable,oldTalbe}) {
                write.createStatement().execute("delete from " + table + " where id='delete'");
                write.createStatement().execute("delete from " + table + " where id='delete&insert'");
                write.createStatement().execute("insert into " + table + " values('delete&insert',1)");
                write.createStatement().execute("update " + table + " set curr_value=1 where id='update'");
            }
            var newTable =  TABLE_NAME+"_new";
            write.createStatement().execute("create table " + newTable + "(id varchar(15),curr_value int )");
            write.createStatement().execute("insert into "+newTable+" values('new table',-1)");
//            write.createStatement().execute("alter table "+oldTalbe+" drop column curr_value");
//            write.createStatement().execute("alter table " +oldTalbe+" add new_column int null");
            write.commit();

            try {
                for (String table : new String[]{TABLE_NAME, pkTable, newTable,oldTalbe}) {
                    System.out.println("==========" + table + "===========");
                    for (var entry : connections.entrySet()) {
                        System.out.print("------------" + entry.getKey() + "----------");
                        var c = entry.getValue();
                        try {
                            var rs = c.createStatement().executeQuery("select * from " + table);
                            int columnCount = rs.getMetaData().getColumnCount();
                            System.out.println(columnCount);
                            for(int i=1;i<=columnCount;i++){
                                System.out.print("\t|"+rs.getMetaData().getColumnName(i));
                            }
                            System.out.println('|');
                            while (rs.next()) {
                                System.out.println("\t" + rs.getString(1) + '|' + rs.getInt(2));
                            }
                        }catch (Exception ee){
                            ee.printStackTrace();
                            Thread.sleep(1000);
                        }
                    }
                }
            }finally {
                for(var c:connections.values()){
                    c.close();
                }
            }
        }

        protected abstract String pk(String tableName);

        protected abstract Connection connection() throws Exception;
    }

    static class Mysql extends Database {

        @Override
        protected String pk(String tableName) {
            return "alter table " + tableName + " add constraint transaction_table_pk primary key (id)";
        }

        @Override
        protected Connection connection() throws Exception {
            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;
        }
    }

    public static void main(String[] args) throws Exception {
        new Mysql().test();
    }
}

打印结果如下所示

==========repeatable_read_889===========
------------WRITE----------2
	|id	|curr_value|
	update|1
	delete&insert|1
------------NEW----------2
	|id	|curr_value|
	update|1
	delete&insert|1
------------PRIMARY----------2
	|id	|curr_value|
	delete|0
	update|0
	delete&insert|0
------------NO_PK----------2
	|id	|curr_value|
	delete|0
	update|0
	delete&insert|0
==========repeatable_read_889_pk===========
------------WRITE----------2
	|id	|curr_value|
	delete&insert|1
	update|1
------------NEW----------2
	|id	|curr_value|
	delete&insert|1
	update|1
------------PRIMARY----------2
	|id	|curr_value|
	delete|0
	delete&insert|0
	update|0
------------NO_PK----------2
	|id	|curr_value|
	delete|0
	delete&insert|0
	update|0
==========repeatable_read_889_new===========
------------WRITE----------2
	|id	|curr_value|
	new table|-1
------------NEW----------2
	|id	|curr_value|
	new table|-1
------------PRIMARY----------java.sql.SQLException: Table definition has changed, please retry transaction
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	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.StatementImpl.executeQuery(StatementImpl.java:1200)
	at com.qbit.RepeatableRead$Database.test(RepeatableRead.java:60)
	at com.qbit.RepeatableRead.main(RepeatableRead.java:104)
------------NO_PK----------java.sql.SQLException: Table definition has changed, please retry transaction
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	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.StatementImpl.executeQuery(StatementImpl.java:1200)
	at com.qbit.RepeatableRead$Database.test(RepeatableRead.java:60)
	at com.qbit.RepeatableRead.main(RepeatableRead.java:104)
==========repeatable_read_889_old===========
------------WRITE----------2
	|id	|curr_value|
	update|1
	delete&insert|1
------------NEW----------2
	|id	|curr_value|
	update|1
	delete&insert|1
------------PRIMARY----------2
	|id	|curr_value|
	delete|0
	update|0
	delete&insert|0
------------NO_PK----------2
	|id	|curr_value|
	delete|0
	update|0
	delete&insert|0

首先，被读取过的数据，再次读取时值不会改变，即使有别的session执行了commit

其次不但被读取过的数据不会该表，被读取的表里其他数据也不会改变

再进一步，不但被读取过的表，还包括那些未被读取过的表，其数据也不会改变。这样可以把整个数据库当成一个状态，而一旦进行读取，后续读取的数据都是该时间下的状态，也就是时间停留在了第一次读取的时候。

对于DML语句如此，对于DDL语句呢？

打印中的两个报错，对于newTable无法读取说明DDL也是读取之前的那个历史时间的状态。

但是对于oldTable的处理有些让人困惑。如果把上面注释的地方放开，则程序会卡在那里。如果去数据库查询，发现DDL语句已经生效。但是java方法的调用却无法返回，正是诡异。

总的来说，MqSql的表现和MVCC的模型很一致，即使DDL也是。偶有的情况就是对已有表的修改（无论新增字段还是删除字段）可能存在于潜在的冲突导致java服务没能正常返回。