[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Transaction session on connection pool

Posted on 2008-06-23
14
Medium Priority
?
1,255 Views
Last Modified: 2013-12-10
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

0
Comment
Question by:eric81
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 7

Expert Comment

by:humanonomics
ID: 21853101
Well, what if the query executed by user 1 is over ?

In that case, the lock will be released anyways.
0
 

Author Comment

by:eric81
ID: 21853422
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.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 21853541
What kind is the server: dedicated or shared.
Does the second statement is SELECT ...FOR UPDATE ?
Or pure SELECT?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 22

Expert Comment

by:DrSQL
ID: 21855638
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!
0
 

Author Comment

by:eric81
ID: 21862727
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
0
 
LVL 48

Expert Comment

by:schwertner
ID: 21863677
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.
0
 

Author Comment

by:eric81
ID: 21871310
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
0
 
LVL 48

Expert Comment

by:schwertner
ID: 21872364
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.

0
 

Author Comment

by:eric81
ID: 21872374
Yap. I have suspects on connection pooling on BEA sides.

But don't know how to make it right.


Regards
0
 
LVL 48

Expert Comment

by:schwertner
ID: 21872385
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
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 21873519
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!
0
 

Author Comment

by:eric81
ID: 21880989
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
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 200 total points
ID: 21881434
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
0
 

Accepted Solution

by:
eric81 earned 0 total points
ID: 21913024
I still can't figure it out. Really frustrating...

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


Regards
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Upgrading Tomcat – There are a couple of methods to upgrade Tomcat is to use The Apache Installer is to download and unzip and run the services.bat remove|install Tomcat6 Because of the App that we are working with, we can only use Tomcat 6.…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question