select for update oracle transaction in VB.NET - where do you specify the transaction.begin and .end?
Posted on 2008-11-13
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!