Table locked and session was invalidated

I have a simple question, I developed a java application, which runs on Oracle application server and Oracle database. This application depends on multiple JSPs on which multiple users can access a single JSP in the same time, some problems occurs due to the excessive usage of this application and I have some questions about them:

1. Is there any danger or disadvantages in connecting to the database using the jdbc directly from the JSP ?!!

2. An error occurs almost once a week (most probably at the point of time when an excessive usage happens). This error stops the users from submitting or accessing the some JSPs. I ran the following query on the DB to find any locked items:

SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status,l.session_id, l.oracle_username, l.locked_mode
FROM dba_objects o, gv$locked_object l
WHERE o.object_id = l.object_id;

I found that the table I use to select from in the halted JSPs is a result of this query.

In order to solve this problem temporarily, i use the sql developer to add a record to this table and commit, i find that the problem is solved and the halted jsps involved are running.

These are the exceptions that appears in the log files of the application server right after I press commit:

a. java.lang.IllegalStateException: Session was invalidated (all the time)
b. java.sql.SQLException: Closed Connection (some times)

I do not know exactly where to start debugging, since i use synchronization in all of my code. Also I made sure that each user accessing the same JSP has a separate connection.

Thanks in advance

Hello,

The session was invalidated message sounds like someone tried to post off the request after their session had expired and therefore that jsessionid had been invalidated and hence you have the illegalstateexception.

The SQLException can be somewhat trickier, is that exception thrown as you call a query to the database? This could mean that the database connection had been closed before the query was made.

// Json

Hey Json,

First of all thanks for your reply.

* Regarding the session invalidation: what do you mean by "post off the request", i would like to make my case more obvious, for example i have a JSP page displayed for the user, when he presses the "Search" button, for example, another JSP is displayed in concatenation of the previous one. Does this mean that if the user has first JSP called for a long time (for example 2 hours) and then he presses the "Search" button in order for the second JSP to be displayed, his session can be invalidated?!! I would like to say also that after that all other users cannot display the results too.

* Regarding the SQL exception: The system i developed has interactions with the database all the time, therefore the answer to your question "is that exception thrown as you call a query to the database?" is yes. I would like to know what exactly can close a database connection when it should be open?!! Also what about this locked table?!! all oracle people said that locking a table should not happen at all in an oracle database. Also i would like to add that multiple users can access my JSP at the same time, can this be a threading problem, although my code is totally synchronized?!!

Thanks again for your previous reply.

Regards.

How do you handle your SQL stuff, are you doing it all in the JSP's or are you using servlets or maybe even some elaborate database connection pool?

What application server are you using?

I'm not too familiar with oracle databases myself, I'm more of a MySQL user.

// Json

hey JSON,

I really appreciate your help.

I am using Oracle Application server and Oracle Database. Everything is done in JSPs and connections are done directly from the JSPs. Can this be the problem, should I connect to the database using servlets or what?! If so, what is the difference between having the connection direclty from the JSPs or from servlets on the server?!!

Thanks again

Sometimes when I have database connections using Apache Tomcat I tend to put them as context resources. I believe then that the connection pool is taken care of by Tomcat and I can just shoot off requests to it as I please. Tomcat also has a way of verifying that a connection exists before actually doing the real query. I'm sure there must be some way of letting WebLogic handle this stuff as well.

Have a look at something like Configuring WebLogic JDBC Resources

Out of curiosity, how many simultaneous connections can you have before it all stops working?

// Json

I don't know exactly how many simultaneous connections happens before it stops, as i can't actually track users actions.

My estimation is maybe 10 connections, but this is just a completely non-accurate estimate. But I would like to point out that my system implementation is integrated to an Enterprise Content Management System, running on two Oracle application servers and two (Load balanced) Oracle Databases. My implementation is like a dot in a sea on the main system.

I am also interested in knowing what actions by users can lock a table in the database, because i think this is my main issue.

Thanks alot for help JSON, i really appreciate it