lock Oracle db objects, but only a lock for others?

My question concerns locks.

I am a developer new to a VB6/Oracle 9i client-server application and somewhat new to Oracle although I have developed against SQL Server and Access quite a bit. One to several (3,4,...,10?) clients located on separate machines use this Oracle database - no more. No other application or process out of our control accesses this DB.

The practice has been to explicitly lock particular rows in particular tables under certain circumstances - while some input is pending, for instance. Other clients trying to perform similar operations on the same data may try to lock the same rows, only to be turned away. This 'turning away" is acceptable - it just means that other clients should not interfere with what is going on with those data and the data locking those records implies.

The problem arises in managing the code and the locks. In the course of some user input process at the client GUI, many sub routines might need to access the data that will have been locked when the input process was initiated. The locks are released and reinstated repeatedly as each bit of code does its thing. The code is not well modularized, so often code drops a lock, anticipating a need for data access by subsequent code somehwere else, or reinstates a lock, anticipating that it should in case the other spaghetti code forgot to, or whatever - who knows? There are occasionally module level or global booleans, but IMHO, this just adds to the quagmire.

Can the client set a lock on Oracle db objects that is only a lock for others?

Wouldn't it be just as effective if each client set a flag in some table or other that, in effect said, "do not disturb - I am working here", then cleared it when done?

Something like this would help make this "lock-unlock-lock-unlock-(hope no one jumps in!)-lock?-lock?-unlock?-..." nightmare go away.

Help, please!

Bob Kilmer Received on Sun Apr 20 2003 - 10:35:41 CDT