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

Posted on 2008-11-13
Medium Priority
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
  • 3
  • 2
LVL 18

Expert Comment

ID: 22954249
The OracleConnection object has BeginTransaction, CommitTransaction, and RollbackTransaction methods.

Author Comment

ID: 22954328

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

ID: 22954385
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

ID: 22954450
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

ChetOS82 earned 2000 total points
ID: 22954542
"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 ASP.net worker process recycled.

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

840 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