Table locked and session was invalidated

[tarek.mostafa]

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

[ProgramThis]

You should read this thread (http://www.codeguru.com/forum/showthread.php?t=352053) from these very forums. You should always check if there is a thread similar to yours before posting.

That being said, it is never a good idea to connect directly to your database from the JSP, especially if you are doing it client side via JavaScript. If you are having problems with concurrent users, you DEFINITELY do not want to do this. A database connection must be created for each user. Whereas if you manage your database connections in a servlet you can create a database pool and share those connections instead of creating a new one for each user.

Based off of your error, I would say that the users session is timing out, not allowing them to connect to the database. I would check to see how long it took them to input the data and check that against your session timeout setting.

[tarek.mostafa]

hey ProgramThis,

First of all, thanks for your reply, i found the thread link you posted completely useful.

But what actions by users can lock a table in the database?!! everything i read explained that oracle databases is designed in order not to lock any tables.

Thanks again for your previous reply.

[ProgramThis]

hey ProgramThis,
First of all, thanks for your reply, i found the thread link you posted completely useful.

You're welcome :)


hey ProgramThis,
But what actions by users can lock a table in the database?!! everything i read explained that oracle databases is designed in order not to lock any tables.
You are confusing locking the table at the database with locking the connection at the Web server. While it is true that Oracle can perform concurrent atomic transactions over numerous requests for read and write, your application may not be.

Locking of the database was not really the point that I was trying to make though. What I was trying to point out was that for each client that connects to your Web site, you will have to create a new database connection. Now, say you are getting a thousand hits per minute. That is a thousand new database connections opened (either on the client in script, or on your server in the JSP). How well do you think your server will handle a 1000 instantaneous connections?

The point of a Database connection pool is to alleviate this problem. Your database connections are already created and shared among processes / clients. This way you don't have 1000 different connections at the same time. This will greatly improve the performance on your system.

[tarek.mostafa]

Thanks alot ProgramThis,

Your comment is totally considered and i really appreciate your help.

As soon as i apply it and solve the problem, i'll mark this thread as resolved.

Thanks again.