- 标签:会话级临时表、全局临时表、幻读
- 释义:Oracle 全局临时表又叫GTT
★ 结论
✔ 专用服务器模式:不同应用会话只能访问自己的数据
✔ 共享服务器模式:不同应用会话只能访问自己的数据
✔ 数据库驻留连接池模式:不同应用会话可能访问到前一个应用会话的数据(当他们连接到同一个数据库会话时),直到应用连接池释放数据库会话(但不像应用连接池,数据库驻留连接池产生的数据库会话由Oracle维护,应用层无法控制其释放)
✔ 数据库连接池方式(HikariCP等):不同应用会话可能访问到前一个应用会话的数据(当他们连接到同一个数据库会话时),直到应用连接池释放数据库会话
★ 实验环境
※ 数据库:19c
※ 会话模式└专用服务器模式└共享服务器模式└数据库驻留连接池模式└数据库连接池方式(HikariCP)
★ 实验结果
✔ 专用服务器模式:gtt表的count(*)数值始终为“1”
✔ 共享服务器模式:gtt表的count(*)数值始终为“1”
✔ 数据库驻留连接池模式:gtt表的count(*)数值会在新会话重复访问时累加,并不总是“1”,由于数据库会话的释放由Oracle管理,因此应用下次执行时可能从上次的值开始累加
✔ 数据库连接池方式(HikariCP):gtt表的count(*)数值会在新会话重复访问时累加,并不总是“1”,直到应用连接池释放数据库会话后下次执行重新从“1”计数
★ 配置数据库(需要重启数据库)
✔ 配置共享服务器模式
alter system set shared_server_sessions=1 scope=spfile;
alter system set max_shared_servers=1 scope=spfile;
alter system set shared_servers=1 scope=spfile;
alter system set max_dispatchers=1 scope=spfile;
alter system set dispatchers='(PROTOCOL=TCP)(DISPATCHERS=1)' scope=spfile;✔ 配置数据库驻留连接池模式
execute dbms_connection_pool.start_pool();
execute dbms_connection_pool.alter_param('','minsize','1');
execute dbms_connection_pool.alter_param('','maxsize','1');✔ 创建会话级全局临时表
drop table zzt.gtt;
create global temporary table zzt.gtt(id number) on commit preserve rows;
★ Java案例
✔ 专用服务器模式
package PAC_001;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import oracle.jdbc.pool.OracleDataSource;
public class CLA_oracle {public static void main(String args[]) throws SQLException {OracleDataSource ods = null;Connection conn = null;Statement stmt = null;ResultSet rset = null;ods = new OracleDataSource();ods.setURL("jdbc:oracle:thin:@1.1.1.9:1521/prodpdb1:dedicated");ods.setUser("zzt");ods.setPassword("zzt");for (int i = 0; i <= 5; i++) {conn = ods.getConnection();try {stmt = conn.createStatement();int rset1 = stmt.executeUpdate("insert into zzt.gtt values(1)");int rset2 = stmt.executeUpdate("commit"); // 默认自动提交,可写可不写rset = stmt.executeQuery("SELECT count(*) FROM zzt.gtt");while (rset.next())System.out.println(i + " : " + rset.getString(1));System.out.println("#########");}finally {if (rset != null)rset.close();if (stmt != null)stmt.close();if (conn != null)conn.close();}}}
}✔ 共享服务器模式
package PAC_001;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import oracle.jdbc.pool.OracleDataSource;
public class CLA_oracle {public static void main(String args[]) throws SQLException {OracleDataSource ods = null;Connection conn = null;Statement stmt = null;ResultSet rset = null;ods = new OracleDataSource();ods.setURL("jdbc:oracle:thin:@1.1.1.9:1521/prodpdb1:shared");ods.setUser("zzt");ods.setPassword("zzt");for (int i = 0; i <= 5; i++) {conn = ods.getConnection();try {stmt = conn.createStatement();int rset1 = stmt.executeUpdate("insert into zzt.gtt values(1)");int rset2 = stmt.executeUpdate("commit"); // 默认自动提交,可写可不写rset = stmt.executeQuery("SELECT count(*) FROM zzt.gtt");while (rset.next())System.out.println(i + " : " + rset.getString(1));System.out.println("#########");}finally {if (rset != null)rset.close();if (stmt != null)stmt.close();if (conn != null)conn.close();}}}
}✔ 数据库驻留连接池模式
package PAC_001;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import oracle.jdbc.pool.OracleDataSource;
public class CLA_oracle {public static void main(String args[]) throws SQLException {OracleDataSource ods = null;Connection conn = null;Statement stmt = null;ResultSet rset = null;ods = new OracleDataSource();ods.setURL("jdbc:oracle:thin:@1.1.1.9:1521/prodpdb1:pooled");ods.setUser("zzt");ods.setPassword("zzt");for (int i = 0; i <= 5; i++) {conn = ods.getConnection();try {stmt = conn.createStatement();int rset1 = stmt.executeUpdate("insert into zzt.gtt values(1)");int rset2 = stmt.executeUpdate("commit"); // 默认自动提交,可写可不写rset = stmt.executeQuery("SELECT count(*) FROM zzt.gtt");while (rset.next())System.out.println(i + " : " + rset.getString(1));System.out.println("#########");}finally {if (rset != null)rset.close();if (stmt != null)stmt.close();if (conn != null)conn.close();}}}
}✔ 应用连接池方式(HikariCP)
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.Statement;
public class C_O {// main函数public static void main(String[] args) {executeQuery();}public static void executeQuery() {HikariConfig hikariConfig = new HikariConfig();hikariConfig.setPoolName("HikariCP 连接池");hikariConfig.setDataSourceClassName("oracle.jdbc.pool.OracleDataSource");hikariConfig.addDataSourceProperty("user", "zzt");hikariConfig.addDataSourceProperty("password", "zzt");hikariConfig.addDataSourceProperty("url", "jdbc:oracle:thin:@1.1.1.9:1521/prodpdb1:dedicated");hikariConfig.setMaximumPoolSize(1); // 设置数据库层的session数hikariConfig.setAutoCommit(false); // 默认自动提交为开启状态hikariConfig.setConnectionTimeout(5000);HikariDataSource hikariDataSource = new HikariDataSource(hikariConfig);Connection connection = null;for (int i = 0; i < 5; i++) {System.out.println("#############");try {connection = hikariDataSource.getConnection();Statement st1 = connection.createStatement();st1.executeUpdate("insert into zzt.gtt values(1)");Statement st2 = connection.createStatement();st2.executeUpdate("commit");Statement st = connection.createStatement();ResultSet rs = st.executeQuery("select count(*) cn from zzt.gtt");while (rs.next()) {System.out.println("【count is:】" + rs.getString("cn"));}} catch (SQLException e) {e.printStackTrace();} finally {if (connection != null) {try {connection.close(); // 断开的是Java和hikari应用池的连接,而非HikariCP和数据库的连接} catch (SQLException e) {e.printStackTrace();}}}}hikariDataSource.close(); // 断开HikariCP和数据库之间的连接System.out.println("【ALL END】");}
}
※ 如果您觉得文章写的还不错, 别忘了在文末给作者点个赞哦 ~