As per my project requirements, which is using Weblogic Application Server, it will co-exists with Pro4 Legacy System. But both of them will use the same Oracle database.
Pro4 application locks a record using "SELECT...FOR UPDATE" statement and keep it locked until the end of transaction (at the end of screen). In the meanwhite, if web application tries to access the same record using JDBC API, it waits indefinetely until record is released (by commit/rollback). This is an undesirable situation where application server should show an error to user that record is locked by legacy system user, not that JSP page will wait forever.
What is the alternative approach or solution to this problem?
If u set the isolation level in the weblogic app to read uncommitted, then it will take the previous version of the data and show it on the screen. As long as ur app is ok with stale data - and does not use it to re-update the database, it should work. If not, it sure is a problem. The default isolation is read committed and it waits till the record is committed.
well u could use oracle's select.. for update nowait in your jdbc code, then catch for SQLException and examine if its this error 'ORA-00054: resource busy and acquire with NOWAIT specified', if it is you know the resource is already locked, so just post your friendly user message.
i will rather use setQueryTimeout method of Statement class to timeout Update Queries rather than invoking SELECT statement every time before any update.