On TransactionIsolation（2）Oracle

最新推荐文章于 2022-04-28 08:53:41 发布

首席IT民工

最新推荐文章于 2022-04-28 08:53:41 发布

阅读量327

点赞数

分类专栏： DataBase 文章标签： oracle Transaction Isolation read commited

本文链接：https://blog.csdn.net/kamputer/article/details/109293766

版权

DataBase 专栏收录该内容

42 篇文章 1 订阅

订阅专栏

Oracle的连接需要将connection改为如下情况

private static Connection connection() {
        try {
            Class.forName(oracle.jdbc.OracleDriver.class.getName());
            Connection out= DriverManager.getConnection("jdbc:oracle:thin:@172.23.47.6:1521:ORCLCDB","sys as sysdba","Oradoc_db1");
            out.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            return out;
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }

另外需要注意的是由于语法不一样，initValue也需要更改

private static void initValue() throws SQLException {
        Connection connection=connection();
        connection.setAutoCommit(false);
        connection.createStatement().execute("drop table 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();
    }

oracle是不支持drop table if exists语法的。下面说各种隔离级别，TRANSACTION_READ_UNCOMMITTED和TRANSACTION_REPEATABLE_READ这两个级别oracle是不支持的，如果设置这两个隔离级别会报以下错误

Exception in thread "main" java.lang.RuntimeException: java.sql.SQLException: 仅 READ_COMMITTED 和 SERIALIZABLE 是有效的事务处理级
	at com.qbit.Main.connection(Main.java:85)
	at com.qbit.Main.initValue(Main.java:64)
	at com.qbit.Main.main(Main.java:14)
Caused by: java.sql.SQLException: 仅 READ_COMMITTED 和 SERIALIZABLE 是有效的事务处理级
	at oracle.jdbc.driver.PhysicalConnection.setTransactionIsolation(PhysicalConnection.java:2515)
	at com.qbit.Main.connection(Main.java:82)
	... 2 more

至于TRANSACTION_READ_COMMITTED的结果和https://blog.csdn.net/kamputer/article/details/109292372是一样的。

另外由于我使用的是sys用户，不支持TRANSACTION_SERIALIZABLE，会报以下错误

Exception in thread "main" java.lang.RuntimeException: java.sql.SQLException: ORA-08178: 为用户 INTERNAL 指定的 SERIALIZABLE 子句非法

	at com.qbit.Main.connection(Main.java:85)
	at com.qbit.Main.initValue(Main.java:64)
	at com.qbit.Main.main(Main.java:14)
Caused by: java.sql.SQLException: ORA-08178: 为用户 INTERNAL 指定的 SERIALIZABLE 子句非法

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:213)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:37)
	at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:896)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1119)
	at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1737)
	at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1692)
	at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:300)
	at oracle.jdbc.driver.PhysicalConnection.setTransactionIsolation(PhysicalConnection.java:2507)
	at com.qbit.Main.connection(Main.java:82)
	... 2 more
Caused by: Error : 8178, Position : 0, Sql = ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE, OriginalSql = ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE, Error Msg = ORA-08178: 为用户 INTERNAL 指定的 SERIALIZABLE 子句非法

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)
	... 16 more