Records the issue where Session information is not updated when the ADO. NET connection pool is used to connect to Oracle, ado. netoracle

Records the issue where Session information is not updated when the ADO. NET connection pool is used to connect to Oracle, ado. netoracle

In a recent project, due to the large amount of data queried on the interface and the large number of associated tables, some data needs to be saved temporarily for later queries, so I thought of using temporary oracle tables to achieve this requirement. As we all know, there are two types of temporary tables in oracle: temporary tables at the transaction level and temporary tables at the session level. Here I use temporary tables at the session level. At that time, I thought everything was good after the function was implemented. I didn't expect to buy a pitfall here.

Emergence of pitfalls:Later, when I added debugging logs to the system, I accidentally found that the data in the temporary table was not as defined in the oracle temporary table, the data in the temporary table is automatically cleared after the session ends ". First, check the log records for the first query. The data volume for the first query is 10017 rows, and the temporary table is used in the place circled in red box: the log records for the second query, the data size of the second query is less than that of the first query, 15 rows: the results of the two queries are as follows: the oracle session-level temporary table used does not empty the data in the temporary table after the session ends, as defined by the temporary table? Obviously, this is not the reason. The most likely reason is that the two queries before and after are the same Session, so the temporary table data is not cleared. With this idea, the next step is to find out why the two queries before and after are the same Session. Investigate the causes of pitfalls:First, the system environment: 1. use ADO. NET is enabled by default, and the Connection pool configuration uses the default configuration; 2. the driver connecting to the oracle database is: 3. Each query creates a Connection, then, Close () and Dispose () are called after the query (); How to find the pitfalls:1. After the Connection pool is enabled, the Connection used for the first and second queries is the same Connection; 2. After the query is complete, the Connection calls Close (), Dispose () the Session is not actually closed after the method; Verification process:First, let's take a look at the oracle documentation's explanation of the Connection Pool: With connection pooling enabled (the default), the Open and Close methods of the OracleConnection object implicitly use the connection pooling service. in the preceding code, the Open call uses the connection pooling service, which is responsible for returning a connection to the application. connection pools are created by the connection pooling service using the ConnectionString as a signature to uniquely identify a pool. if no pool with the exact attribute values in the ConnectionString exists, the connection pooling service creates a new connection pool. if a pool already exists with the requested signature, a connection is returned to the application from thatpool. when a connection pool is created, the connection-pooling service initially creates the number of connections defined by the Min Pool Size attribute of the ConnectionString. this number of connections is always maintained by the connection pooling service for the connection pool. at any given time, these connections are available in the pool or used by the application. the Incr Pool Size attribute of the ConnectionString defines the number of new connections to be created by the connection pooling service when more connections are needed in the connection pool. when the application closes a connection, the connection pooling service determines whether the connection lifetime has exceeded the Connection Lifetime attribute; if so, the connection pooling service closes the connection; otherwise, the connection goes back to the connection pool. the connection pooling service only enforces the Connection Lifetime when a connection is going backto the connection pool. the Max Pool Size attribute of the ConnectionString sets the maximum number of connections for a connection pool. if a new connection is requested, no connections are available, and Max Pool Size has been reached, then the connection pooling service waits for the time defined by Connection Timeout. if the Connection Timeout has been reached and there are still no connectionsavailable in the pool, the connection pooling service raises an exception indicating that the pooled connection request has timed-out. the connection pooling service closes connections when they are not used; connections are closed every three minutes. the Decr Pool Size attribute of the ConnectionString provides connection pooling service for the maximum number of connections that can be closed in one run. when The Connection calls the Open () method, The following is the official oracle documentation Description: The connection is obtained from the pool if connection pooling is enabled. otherwise, a new connection is established. it is possible that the pool does not contain any unused connections when Open()Method is invoked. In this case, when a new connection is established. Connection calls the Close () method, the following is the official oracle documentation Description: Rolls back any pending transactions.
Places the connection to the connection pool if connection pooling is enabled. even if connection pooling is enabled, the connection can be closed if it exceeds the connection lifetime specified in the connection string. if connection pooling is disabled, the connection is closed.
Closes the connection to the database.

The connection can be reopened usingOpen().

From the description of the Open and Close Methods of Connection Pool and Connection in the official oracle documents and the combination of the current system environment (the query request has only one client), it is not difficult to obtain such a scenario: during the first query, a Connection Pool creates a Connection and returns it to the Connection object. After the query, it returns it to the Connection Pool. During the second query, because the time interval between the first query and the default Connection LifeTime is less than the Connection time, the Connection Pool returns the Connection used for the first query to the Connection object for query. The results are the same for the first and second queries. Again, according to the description of the Close () method, the Connection does not clear the Connection Session information and other data when calling the Close method (only after the Connection is actually removed from the Connection pool, the Session does not exist ). Currently, there are two solutions (the first one is finally decided): 1. truncate the temporary table before each use of the temporary table; 2. Do not use the Connection Pool; the first post, I read the blog "Why should we stick to writing a blog" published by the blogger's farmer code for my life today. I will record this problem by the way and ask the elders in the garden if there are any other solutions, or, the problem is solved in the wrong direction. Please kindly advise ~~~