select for update oracle transaction in VB.NET - where do you specify the transaction.begin and .end?

Posted on 2008-11-13
Last Modified: 2013-12-07
I have an Oracle PL/SQL procedure that selects data out for a front end and the users can modify this data and resubmit the information.  In the course of 300+ folks simultaneously accessing the information I need a way to "lock the record" so that it cannot be modified in more than one place at the same time.

Looking to the SELECT FOR UPDATE clause in Oracle it seems like a really good thing here.  The problem is you have a couple routines and the transaction isn't visible to each routine...




on error transaction.rollback

How on earth can I get the same transaction to .begin/.end/.rollback/.commit in the same method when one fires from a button click event of selecting data and the other event is a button click for a save of data?

Or how can I make the transaction visible to both methods?

Once .NET is through with a connection, it is returned to the connection pool.  Once this happens Oracle "lets go" of the transaction and lets other folks edit the same issue number (data).

Or is there a better solution that I am missing?

Please help gurus!



Question by:cyimxtck
    LVL 18

    Expert Comment

    The OracleConnection object has BeginTransaction, CommitTransaction, and RollbackTransaction methods.

    Author Comment


    I understand that part perfectly but the issue I am having is that there are two different methods:

    select data

    update data

    The beginTransaction must start in the selectData method and endTransaction, commit and rollback would end in the updateData method.

    The two methods cannot see each others transactions.  how can you keep the database connection open from the select until the update data happens?

    See what I mean?


    LVL 18

    Expert Comment

    Sorry, I didn't catch that this is a web site.  You aren't going to be able to (lets say, it would be wise) to start it during one method, and then end it in a different one.  You are going to have to implement locking on a higher level than the database server.

    Author Comment

    You mean implement locking based on the application creating a lock on its own outside of Oracle?

    I don't know and of course that is why I am asking this question but why does Oracle give you select for update capability if you cannot span methods?  What scenario would that prove useful?
    LVL 18

    Accepted Solution

    "You mean implement locking based on the application creating a lock on its own outside of Oracle?"

    Yes.  You could do this various ways, including a flag in the table indicating that it has been locked (and preferably a locked time, so that it can be unlocked if it is held for too long).

    SELECT FOR UPDATE is for cursors and batch processing, it indicates that Oracle should hold the lock until data is committed.  With a website, you cannot/should not hold a connection open across page requests.

    I say "cannot/should not" because it is probably possible, but you would have to jump though hoops (like storing the connection in the Application/Session cache), and performance would suffer. Also, what would happen if someone selected the data, and then closed the browser?  The connection wouldn't close until the worker process recycled.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

    737 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

    20 Experts available now in Live!

    Get 1:1 Help Now