Advice on avoiding locking problems in precompiler apps

From: Brian M. Biggs <bbiggs_at_cincom.com>
Date: 1996/11/18
Message-ID: <3290FEB3.61B2@cincom.com>#1/1

All,

I sent this message out a few months ago, and never got a reply back (or if I did, it was only posted in the nesgroup and not sent to me via email, and I missed it!). I thought I'd put it out here again, to see if I can get an opinion on it. Any help is appreciated. Please reply via email in addition to this newsgroup.

By the way, we are using Pro*COBOL if it matters, but I wouldn't think it makes much of a difference.

I was wondering if anyone has some general advice on how to manage locking in precompiler applications.

Do you have to do all of your own lock management in precompiler languages? For example, if I want to delete a set of rows from a table, I would typically use the DELETE command and delete the rows. However, what if someone else has one of those rows locked due to an update taking place? Oracle will wait until that row is freed up before it will delete the rows, which will cause the application to "hang" until the DELETE is complete.

Does this mean that the best way to handle this possibility is to first do a SELECT...FOR UPDATE NOWAIT, and THEN do the DELETE? This would allow us to take some action if the row could not be immediately deleted. However, it seems that this method could become cumbersome and involves a lot of rework for EVERY situation.

Is there a better, faster, stronger way to accomplish what I've described above? Is there a way to have Oracle take care of more of the locking itself? I have only used SQL*Forms 3.0, and it appeared to take care of this type of locking itself. Does it basically do a SELECT...FOR UPDATE NOWAIT and then actually do the update if no error is returned? Does this mean that we have to duplicate this type of functionality inf a precompiler application?

Also, should we even be worrying about this type of locking in an OLTP application?

Thanks,
Brian

-- 
Brian M. Biggs				mailto:bbiggs_at_cincom.com
Cincom Systems, Inc.			voice: (513) 677-7661
http://www.cincom.com/

Received on Mon Nov 18 1996 - 00:00:00 CST