Has anyone ever heard of JDBC (using a JDBC-ODBC driver) caching regular ResultSet results?
Here's my example...I have a DAO that gets called constantly by a servlet! Therefore, the servlet creates an instance of the DAO, the DAO acquires a database connection from the J2EE and retains it, prepares a PreparedStatement and then uses it when asked to return information. The PreparedStatement takes one parameter (e.g., SELECT * FROM mytbl WHERE id = ?). The chain of events are:
1. The servlet calls the DAO and requests data for id=3. 2. The DAO sets the parameter to 3 and executes the query. 1 record is returned and the result set is returned. 3. ANOTHER APPLICATION DELETES THE RECORD ASSOCIATED WITH ID=3 BEHIND THE SCENES. 4. The servlet calls the DAO and requests data for id=3. 5. The DAO sets the parameter to 3 and executes the query. 1 record is returned and the result set is returned.
At step 5, I am not expecting the result set to return 1 record. I am expecting 0 records.
Are steps 2 and 4 done in the same transaction or in two different ones? If it's in the same transaction you might get what you're seeing (could depend on the isolation level of the transaction).
Joined: Jan 08, 2002
They are done in two separate transactions.
I've done more investigation and think the following is happening...
The other application is a stateful session EJB running in a J2EE container. I think the J2EE container is optimizing performance by caching database state and not necessarily physically committing the changes made within the EJB. I see what I expect if anyone at anytime within the EJB queries/updates the database. My assumption is, even if the J2EE container is caching stuff and has not yet truly committed the changes, this is okay because for anyone running within the scope of the J2EE container, it is maintaining database integrity. The servlet, however, is running in a servlet container and its database access may not be going through the J2EE environment to get to the database, resulting in it not seeing the changes made by the "other" application.
I have temporarily solved my problem by modifying the EJB to call the servlet to perform the database update when it is needed. Unfortunately, I cannot escape the requirement to enter the EJB...the EJB update to the database happens in conjunction with other things happening in the EJB. I also cannot escape the requirement for the servlet...redirecting these calls to an EJB would render performance in this situation unacceptable because of the number and frequency of calls to the servlet.
It's not pretty but it works. Time permitting, I'd love to find a better solution for this. Any ideas?
First of all you should not return a result as it is a connected result set. After executing the query, you should populate the data into a value object and close the result set, statement and the connection as every connection object has a timeout associated with it.
If you don't want stale data, you should go for Serializable isolation level. After getting the connection object change the isolation level and then go for fetching data. This can be done in Session bean also but one thing you should also understand, it will decrease the performance.
Serializable should be used if data integrity is on high priority then perfromance.