| JDBC and Hung Update Operations [message #295823] |
Wed, 23 January 2008 10:57  |
orbob
Messages: 1
Registered: January 2008
|
Junior Member |
|
|
Hi,
I am learning JDBC interface in Java with Oracle database and have noticed a strange problem when I perform simple update (i.e. insert) operation (using bind variable of PreparedStatement).
I am currently running Oracle database version 9.2.0.1.0 on Solaris 10 with my client program running on JVM Sun 1.5 with JDBC ojdbc5_g.jar. The database instance runs on Solaris OS and the JDBC thin driver is run on either Windows or Linux in an internal LAN network (using TCP). I can connect and perform queries from my JDBC code just fine; I can also perform the usual operation from SQL*Plus on my database hosting machine as well as remotely on the machines that I run the JDBC code using SQL*Plus client.
Now, if the JDBC code attempts to perform a modification operation AND any a user connected to the database through SQL*Plus also performs some sort of update operation on the "same" table that the JDBC code is trying to modify, then the JDBC code hangs. The two operations are being performed at various times. Just to give you a scenario in which the code does or does not become responsive:
If I login via SQL*Plus and connect to the database but perform solely "select" statements on my designated table, and run the JDBC updating code, everything works fine. The insert is done accordingly even in the subsequent updates via JDBC as long as the SQL*Plus user performs only "select" queries.
As soon as I perform a modification operation (i.e. delete the inserted row by the JDBC code) in SQL*Plus, any subsequent attempts to, let's say insert a row, by the JDBC code would result in an operation to hang (just to note, I can acquire the connection in this scenario but the actual execution of operation from JDBC hangs, no response) -- even if I wait half an hour and run my JDBC program, the operation hangs.
The strange thing is, to remedy this, a user that has performed some sort of modification operation on the table via SQL*Plus has to "disconnect" from the database. As soon as the SQL*Plus user disconnects, the JDBC operation goes through and program successfully performs the update
By the way, I am also utilizing DataSource in my code (without JNDI) to establish connections but I've tried the DriverManager in my code with the same result. One last remark is that the isolation level has been set to READ COMMITTED if that has anything to do with it (but the thing is neither of updating operations from JDBC or SQL*Plus are being done at the same time -- as I've mentioned, I even waited a few minutes between each run). Perhaps, the SQL*Plus holds a luck on the rows of the table for some reason and once the user disconnects, the JDBC program goes through *shrug*
Any hint would be appreciated.
|
|
|
|
| Re: JDBC and Hung Update Operations [message #296218 is a reply to message #295823] |
Fri, 25 January 2008 02:18  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
This sounds like you use exclusive table locks during your jdbc-sessions.
What happens when the jdbc-session hangs and the sqlplus user commits?
Query v$lock during a jdbc-hang to see what lock the jdbc-session is trying to acquire.
See this page for some explanation of the v$lock view
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
