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

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!



Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The OracleConnection object has BeginTransaction, CommitTransaction, and RollbackTransaction methods.
cyimxtckCEOAuthor Commented:

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?


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.
cyimxtckCEOAuthor Commented:
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?
"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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.