Solved

Transaction session on connection pool

Posted on 2008-06-23
14
1,237 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

743 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

9 Experts available now in Live!

Get 1:1 Help Now