Our question was what happens to the rows in a global temporary table when populated from a session obtained from a connection pool. Are they still there for the next application user to borrow the same session from the pool?<\/p>\n
The rows are retained across connection pool reserved sessions if the table is configured as “on commit preserve rows”. <\/p>\n
In other words, if user A checks connection 4 out of the pool, adds rows to a global temporary table, checks the connection back in, and user B then checks connection 4 out of the pool, the rows will be visible to user B by default. The only way to get around this is either configure the table as “on commit delete rows” (the default), or otherwise remove the rows, such truncate the table at the start of user B’s session.<\/p>\n
The page below will prove this. Repeated requests will show an increasing number of rows in the pool, even though the connection was “closed” when it was returned to the pool. Perhaps other connection pool implementations allow you to physically close the connection.<\/p>\n
\r\n<%@ page import=\"java.sql.*\"%>\r\n<%@ page import=\"javax.sql.*\"%>\r\n<%@ page import=\"javax.naming.*\"%>\r\n<%@ page import=\"weblogic.logging.*\"%>\r\n\r\n<%\r\ntry {\r\n\u00a0 InitialContext ic = new InitialContext();\r\n\u00a0 DataSource ds = (DataSource)ic.lookup(\"jdbc\/test\");\r\n\u00a0 Connection conn = ds.getConnection();\r\n\u00a0 Statement stm = conn.createStatement();\r\n\u00a0 ResultSet rst = stm.executeQuery(\"select * from t2\");\r\n\u00a0 while (rst.next()) {\r\n\u00a0\u00a0\u00a0 out.println(\"before t2 has this row \" + rst.getString(1) + \"
\");\r\n\u00a0 }\r\n\u00a0 stm.execute(\"insert into t2 values(1)\");\r\n\u00a0 conn.close();\r\n\u00a0 conn = ds.getConnection();\r\n\u00a0 stm = conn.createStatement();\r\n\u00a0 rst = stm.executeQuery(\"select * from t2\");\r\n\u00a0 while (rst.next()) {\r\n\u00a0\u00a0\u00a0 out.println(\"before commit t2 has this row \" + rst.getString(1) + \"
\");\r\n\u00a0 }\r\n\u00a0 conn.commit();\r\n\u00a0 rst = stm.executeQuery(\"select * from t2\");\r\n\u00a0 while (rst.next()) {\r\n\u00a0\u00a0\u00a0 out.println(\"after commit t2 has this row \" + rst.getString(1) + \"
\");\r\n\u00a0 }\r\n\u00a0 conn.close();\r\n}\r\ncatch (Exception e) {\r\n\u00a0 out.println(e.getMessage());\r\n}\r\n%>\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"Our question was what happens to the rows in a global temporary table when populated from a session obtained from a connection pool. Are they still there for the next application user to borrow the same session from the pool?…<\/p>\n