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