Q: Lock Detection in Pro*C / V7 (long message)

I have a Pro*C application I am developing, which will be making extensive use of Cursors with up to 64 or more cursors active at a time. This is working fine, however a issue has arisen on the finer points of lock detection which the New Zealand Oracle personnel are able to suggest a workaround but this will complicate the situation and affect performance. So I am seeking advice from much more knowledgable persons who may have had this issue before.

The situation is this:

I have two processes (Applications written with Pro*C), the first has a open cursor and is reading and updating records as it goes, thus creating Row Exclusive locks on each row altered, the transaction could be a long one, its length in terms of time maybe quite long also.

Now a second application wishes to read one of these rows and at some point in the current transaction it MAY decide that it needs to also update one of the already locked records [locked by app #1]. According to the situation as I understand it (and the local Oracle personnel also understand it) the second process will be made to WAIT until the first transaction either commits or rollsback, thus freeing the row needed from the Row Exclusive lock. I am unhappy about the waiting as the length of wait time is indeterminate, this therefore means that the second user would see that there App has 'frozen' and is not proceeding.

What I would like is to be able to instruct the Oracle database somehow, that I do not wish to wait for locks and that I will be quite happy to be advised by error code that the record is locked (This error is -54 in Pro*C), and then proceed to do something else (like skip this row change [for now] and come back to it later after I have done the other work needed).

The Oracle manuals are no help here and the local Oracle people say the ONLY way they know of for a Oracle app to detect a locked row is to declare a cursor for update which will detect the row lock when fetched and return error -54.
I do admit that this would work in this case however because I am using cursors without update deliberately [particular design requirements too lengthy etc to explain fully here] and my only option is to use a second cursor which uses the primary key to declare a cursor for update which will resolve at OPEN time to this row only, thus attempting to lock the row in question (and ONLY that row), which will detect the row lock held by the first application.
This of course may double my cursor requirements and will cause extra load to be placed on the system when executing these locks as all SQL updates will need to be run past through this method to verify that the update wouldn't freeze, a tiresome and wasteful notion.

Other Database products I have used including Informix have a extension to the ANSI standard(s) which allow a particular 'session' or instance of a application to request:

1. That the database not wait for locked records & return a error code straight away. In Informix that is the 'SET LOCK MODE TO NOT WAIT' statement
2. That if waiting is desired, you can set it to X seconds (say up to 60 seconds) and if the resource is still locked after that a error code will be returned. In Informix that is the 'SET LOCK MODE TO WAIT X' statement where X is the number of seconds up to some arbrtrary maximum set by DBA in the configuration files for that Instance.

Now the closest I seem to be able to see in the manuals is the LOCK TABLE statement which does have a NO WAIT OPTION but that is the only statement that has anything to do with lock control.

My question therefore is this:

Are there some methods I can use apart from the select for update to request that the Database inform me that records are locked so I can take other actions instead of having me enter a lock with no timeout.

For instance, if I set a C signal catcher routine which was then activated after X seconds of lock time, would this destabilise the Pro*C application? (i.e. Does Oracle handle signals properly!) Also how can I in this case request the Database engine to cancel the pending request which caused my timer to go off anyway? (Must be some OCI call of whatever to do that, unfortunately I didn't receive the OCI manual when I got Oracle [I got TWO copies of the Oracle Guide to the PreCompilers manuals instead], so I apologise if its in the OCI manual please summarise anyway in this case).

I do not mind using OCI if needed as this may be a easier solution than changing large blocks of code elsewhere.

You may like to email me direct & I will sumarise any replies and post them back so that others can share and enjoy.

Regards

Greg Nikoloff Received on Wed Jul 20 1994 - 00:44:09 CEST