SELECT FOR UPDATE in a ref cursor from VB.NET application...

I have a .NET application that I want to use row level locking in Oracle.  There are about 300 users that can simultaneously access the application that I have built.  If each person tries to access a given issue number I want them to be denied access with raise_application_error("issue already being modified").

How do I create this?

The transaction would have to be inside of .NET I am guessing?  If it is in .NET how can I SELECT FOR UPDATE when the update is taking place in a separate method?

Any thoughts would be great.

Trial and error would be horrific in terms of time taken to build a prototype.

Please give me your thoughts.

THanks,

B
LVL 1
cyimxtckAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

kretzschmarCommented:
hi,

well, no .NET experience, but usual any transaktion-object is just a wrapper for the DB-transaktion, which is started with the db-session.

of course you can use the for update-clause. the lock on the record persists as long as the transaktion is not commited or rollbacked (or the session becomes invalid, is killed).

btw. it doesn't matter which method selects and which does the real update. you have only to care, that all is using the same connection (which was not closed and reopened between select and update).

hope this helps

meikl ;-)
0
cyimxtckAuthor Commented:
What if there are multiple sessions opened by the same user?  (i.e. 5 browser windows with the same login credentials)

Thanks,

B
0
kretzschmarCommented:
well in this case different connections are used, each connection do have its own session
(the user doesn't matter in this case)

meikl ;-)
0

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.