Solved

Transaction session on connection pool

Posted on 2008-06-23
14
1,238 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
  • 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 47

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
 
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 47

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

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 47

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 47

Assisted Solution

by:schwertner
schwertner earned 50 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
firstswap challenge 20 65
Maintaining Oracle Managed Accounts 2 29
Oracle and DateTime math 6 25
egit plugin on eclipse 8 31
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
The viewer will learn how to implement Singleton Design Pattern in Java.

911 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now