Transaction session on connection pool

What I have done:
1. I developed EAR and deployed it on BEA WLS.
2. I made conn pool and corresponding Data Source.

What I am doing:
1. I am using select ... for update nowait statement for row locking

The issue:
1. user-1 as operator login to app and select 1 record (select ... for update nowait) which supposed to lock particular selected row.
2. user-2 as supervisor login to app and select the same record (select ... for update nowait) via different menu of course.
3. And the error is not showing. I am expecting resource busy error due to row locking

My investigation:
1. When user-1 locks particular row. I could see the lock session still holding the row.
2. When user-2 do whatever it does esp on committing a transaction, the locking that belongs to user-1 get lost.
3. When user-2 is not committing any transaction but gets logout from app, the locking that belongs to user-1 get lost too.

My suspect:
1. Kind of shared transaction session or something.

How to solve this issue?
Is there something to do with conn pool settings on BEA WLS?

This is what user-1 (operator) does when selecting 1 row before update
     * @jpf:action
     * @jpf:forward name="success" path="pageSuccess.jsp"
     * @jpf:forward name="error" path="../error.jsp"
     * @jpf:forward name="error_update" path=""
     * @jpf:forward name="ua_fail" path="../login_failed.jsp" 
    protected Forward viewInputOne(ViewInputOneForm vioForm)
        ArrayList array = new ArrayList();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        StringBuffer query = new StringBuffer();
        this.vioForm = vioForm;
       "calling viewInputOne");
                logUserActivity(vioForm.httpPort,vioForm.DSName,(String)getSession().getAttribute("User"),(String)getSession().getAttribute("remoteAdd"),"View Input Of Correction (Detail)");
            catch(Exception e){
                return new Forward("ua_fail");
            query.append("SELECT * " +
                          "FROM koreksi " +
                          "WHERE id = '" + + "' for update nowait");      
            conn = getDS(vioForm.httpPort,vioForm.DSName).getConnection();
            stmt = conn.prepareStatement(query.toString());
            rs = stmt.executeQuery();
                //Looping the resultset and store it on javabean manualedjb
                ManualEDJB manualedjb = new ManualEDJB();
                manualedjb = null;
        catch(SQLException sqle){
                getRequest().setAttribute("errorMessage","The row is being updated by other user. Please try again later.");
                return new Forward("error_update",viForm);
                return new Forward("error");
        catch(Exception e){
            return new Forward("error");
                if (rs!=null){
                if (stmt!=null){
                if (conn!=null){
            catch(Exception e)
        return new Forward("success");
    public ViewInputOneForm vioForm;

Open in new window

Who is Participating?

Improve company productivity with a Business Account.Sign Up

eric81Connect With a Mentor Author Commented:
I still can't figure it out. Really frustrating...

But my own alternative solution (creating independent table to hold locks) works well so far.

Well, what if the query executed by user 1 is over ?

In that case, the lock will be released anyways.
eric81Author Commented:
Nope. The lock still there when the query for user-1 is over.

Until another user login and committing something or another user login and gets logout.
The lock for user-1 is lost.

I don't know. This is should not happening right? Seems like the same session is for every users.
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

What kind is the server: dedicated or shared.
Does the second statement is SELECT ...FOR UPDATE ?
Or pure SELECT?
    I'm assuming there's more to the code than you've supplied here.  Otherwise, I'm not sure why you're concerned that user-1 is selecting the rows and putting them in the java bean when user-2 tries to select the same rows.  Long-term locking of rows causes problems.  But, if the idea is that you want to avoid collisions and overwrites by users who are updating the same, original copies, then you have a few options:

1) As schwertner alluded to, make your servers dedicated.  You can even add a "start transaction" command.

2) Migrate your database transactional code to a pl/sql stored procedure or anonymous block so that an entire transaction can be perfromed in a single call (I suspect this won't work for your application).

3) Change the application logic to only try to acquire the locks when the changes are being made to the database.  Then, evaluate (in pl/sql, in the SAME block of code, and as part of the SAME transaction) determine whether there have been changes made to the rows (means keeping the original and the changed data).  You could even add the check to your update and then see if the rows processed = the number of rows you expect.  If the data is changed, warn the user about those rows or roll back the whole thing - or ask them if they want to refresh the values.

Good luck!
eric81Author Commented:
Yup my server is a dedicated. But I'm interesting on adding "start transaction" command. What is it? How to implement this?

Let me try to elaborate more on this issue. So you all can help me better.

Operator point of view:
1. Operator does a select on whole table. (usual select)
2. Then operator chose 1 unique row that he/she wants to edit (select ... for update)

Supervisor point of view:
1. Supervisor does a select on a whole table (usual select)
2. Then supervisor chose 1 unique row that he/she wants to approve/reject (select ... for update)

The case is:
1. When operator reached action no 2 (row is locked) and supervisor reached action no 1 (usual select) as described above, the lock on particular row is lost.
2. Yes, there is a commit on supervisor side which is logging to user_activity table. (thus lock is released)
3. But we are not expecting this behavior, right?

I suspect it's a kind of shared connection or something.

My expectation:
1. When operator reached action no 2 (row is locked) and supervisor reached action no 1 (usual select) as described above, the lock is still persist.
2. When operator reached action no 2 (row is locked) and supervisor reached action no 2 (select ... for update) as described above, it should and must return sql exception code with error code 54 (resource busy) due to lock behalf on operator side.

My currently alternative solution:
1. I created another connection pool and its data source
2. Whatever operator does will be using this new connection pool
3. Whereas supervisor still using old connection pool
4. This one fixed the problem but is not viable enough to make another connection pool and its data source on production environment.

I will stress that you use CONNECTION POOLING by BEA.

Connection to Oracle created by connection pools difers
from normal connections.

Connection Pooling OFTEN is used in Web environments to create connection, exucute SQl and release the resource. This is so because WEB pages works in this manner.

I guess that connection pooling consider the transaction only as SELECT statement
and after it is executed  the transaction is quited and the row (rows) are not locked.

So the qiestion you ask seems not to be an Oracle question.
Possibly it is BEA question.
eric81Author Commented:
Yup I used BEA connection pooling. But I'm using Oracle jdbc thin driver anyway.

In fact the lock remains there until another transaction (from another user) doing committing.
So it's a kind of shared session id (in DB) because connection pooling is assigning the new transaction to the current session id.

I have heard from my colleagues that connection pooling shares
a particulat session between many Aplication server users.
They used Apache.

But I haven't been involved in WEB development,
so I have not investigated how connection pooling works.

I will say again that your question is oriented more to
BEA functionality and not to Oracle.
Possibly you have to investigate the BEA documentation.

eric81Author Commented:
Yap. I have suspects on connection pooling on BEA sides.

But don't know how to make it right.

I see two ways.
1. To investigate the BEA documentation. Your problem is
a major problem and such a respectable product like BEA
has to manage it.
2. To ask the guys at the Application Server thread of
Experts Exchange
3. To googling
Or, rather than hold the lock while your user makes changes (and maybe walks away from their terminal or leaves it uncommittes over night), try only acquiring the locks as part of the same operation (using an anonymous pl/sql block).  As I mentioned above, this would mean evaluating the changed block to see if someone changed them out from under the current user and adding logic to handle it.  but holding locks for long periods, especially indeterminate periods is not a good idea.

Good luck!
eric81Author Commented:

You are right your solution no.2 put it in pl/sql block will not work. It is not working.

I'm still expecting something like connection pooling setting to configure. So it works without changing the logic or the codes.

Currently I fixed this issue by creating a new table to "hold" all the rows that are in locked.

schwertnerConnect With a Mentor Commented:
Alternativelly you can add a new column to the table and put there a value that will show
if it is locked or not.

Buut I am interested about connection pooling and how it works.
The reason is also that Oracle 11g embeded connection pooling.
So it is very easy to use this functionality
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.