asked on

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?

Regards
Eric

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="pageInput.do"
     * @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;
        
        log.info("calling viewInputOne");
                
 
        try{
 
            //user_activity
            try{
                logUserActivity(vioForm.httpPort,vioForm.DSName,(String)getSession().getAttribute("User"),(String)getSession().getAttribute("remoteAdd"),"View Input Of Correction (Detail)");
            }
            catch(Exception e){
                log.error(this,e);
                e.printStackTrace();
                getRequest().setAttribute("errorMessage",e.getMessage());
                return new Forward("ua_fail");
            }
            //user_activity
        
                    
            query.append("SELECT * " +
                          "FROM koreksi " +
                          "WHERE id = '" + vioForm.id + "' for update nowait");      
            
 
 
            conn = getDS(vioForm.httpPort,vioForm.DSName).getConnection();
            
            stmt = conn.prepareStatement(query.toString());
            rs = stmt.executeQuery();
            
            if(rs.next()){
                //Looping the resultset and store it on javabean manualedjb
                ManualEDJB manualedjb = new ManualEDJB();
                
                manualedjb.setCol1(rs.getString("COL1"));
                manualedjb.setCol2(rs.getString("COL2"));
                manualedjb.setCol3(rs.getString("COL3"));
  
 
                array.add(manualedjb);
                manualedjb = null;
            }
            
            rs.close();
            stmt.close();
 
 
            getRequest().setAttribute("array",array);
                    
        }
        catch(SQLException sqle){
            log.error(this,sqle);
            sqle.printStackTrace();            
            
            
            if(sqle.getErrorCode()==54){
                getRequest().setAttribute("errorMessage","The row is being updated by other user. Please try again later.");
                return new Forward("error_update",viForm);
            }
            else{
                getRequest().setAttribute("errorMessage",sqle.getMessage());
                return new Forward("error");
            }
        }           
        catch(Exception e){
            log.error(this,e);
            e.printStackTrace();
            getRequest().setAttribute("errorMessage",e.getMessage());
            return new Forward("error");
        }
        finally
        {
            try
            {
                if (rs!=null){
                    rs.close();
                }
                if (stmt!=null){
                    stmt.close();
                }
                if (conn!=null){
                    conn.close();
                }
                rs=null;
                stmt=null;
                conn=null;
                
                array=null;
                query=null;
            }
            catch(Exception e)
            {
                log.error(e);
                e.printStackTrace();
            }                                 
        }
        return new Forward("success");
    }
    public ViewInputOneForm vioForm;

Open in new window

humanonomics

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

In that case, the lock will be released anyways.

eric81

ASKER

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.

schwertner

What kind is the server: dedicated or shared.
Does the second statement is SELECT ...FOR UPDATE ?
Or pure SELECT?

DrSQL - Scott Anderson

Eric,
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!

eric81

ASKER

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.

Regards

schwertner

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.

eric81

ASKER

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.

Regards

schwertner

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.

eric81

ASKER

Yap. I have suspects on connection pooling on BEA sides.

But don't know how to make it right.

Regards

schwertner

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

DrSQL - Scott Anderson

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!

eric81

ASKER

Dr SQL,

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.

Regards

SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

eric81

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial