Answered Is using Temp tables and locking bad coding standards

progdev1

Member
Hi all,
Quick question, is using lock statements (no-lock, exclusive-lock) when doing searches on temp tables considered bad coding standards? See example below. Now before anyone says anything note the below is a simple example for illustrative purposes only.
Code:
DEFINE TEMP-TABLE tt-customerReport NO-UNDO
    LIKE customer 
    FIELD importantCustomer as logical.

FOR EACH customer NO-LOCK:
    CREATE tt-customer.
    BUFFER-COPY customer to tt-customerReport
END.
   
FOR EACH tt-customerReport [B]EXCLUSIVE-LOCK [/B]:
     FOR EACH order WHERE order.customer-no = tt-customerReport 
                                       AND  order.order-value    > 1000000
                              NO-LOCK:
             ASSIGN tt-customer.importantCustomer = TRUE.  
    END. 
END.

I know using locking with temp-tables is unnecessary, but I prefer to have them for two reasons.
1) It helps anyone re-reading the code at a later date determine quickly if a block or procedure is reading or updating a temp table.
2) I was told as a young developer that omitting no-lock and exclusive-lock when doing temp-table updates was a bad habit to get into. That it would lead to you forget them when it came to actual database tables. Whether right or wrong, I guess it is something that has stayed with me since then.

Where I work now a lot of the developers are completely against using locks with temp-tables. They say:
a) It looks terrible.
b) Using locks with temp-tables the below creates unnecessary locking entries in the system tables and uses unnecessary system resources which if they are omitted are not used.
Is b) above correct.
Are there any guidelines on use of locking and temp tables? As I'm beginning to wonder if I am in a minority of 1 here.
 

TomBascom

Curmudgeon
I have a split personality on the topic. I used to be strongly in the "act like it is a real table" camp.

These days I lean towards the -- it's a temp-table, locking makes no sense, side of the divide.
 

Cringer

ProgressTalk.com Moderator
Staff member
It's very much a matter of opinion, but consider this code:
Code:
define temp-table tt-customer no-undo 
  field customerkey as integer. 

def var lv-i as int no-undo.

do lv-i = 1 to 10:
  create tt-Customer. 
  assign 
    tt-Customer.CustomerKey = lv-i. 
end. 

for each tt-customer no-lock :
  assign tt-customer.customerkey = tt-customer.customerkey + 1.
end.

The temp table has no lock and yet the code still works without error (11.2.1). In that case the no-lock is actually misleading. Whereas if it was a DB table you'd get a nice big runtime error. For that reason I leave the lock statement off in my code.
 

RealHeavyDude

Well-Known Member
There are a few practices I follow when I use temp-tables which have never let me down:

  1. Never give a temp-table the same name like a database table. Looking at a piece of code it should be exactly clear whether the code is working on a temp-table or a database table. Plus, compiling the code with or without a database connections might yield different results.
  2. Always use defined buffers and strong scope them when your are creating, updating or deleting records in the temp-table. Otherwise you might get tempted to use the infamous release statement do to the lack of correct scoping and wind up with code that does not exactly do what you want it to do. I always do that with database tables too and it always served me well too.
  3. Make sure the temp-table is indexed in a meaningful way. Even if you don't intend it to hold many records, some fellow fiddling with your code might prove you wrong.
  4. Although locks don't matter and specifying one does not hur immediately - with temp-tables I never specifiy it whereas with database tables I always do specify it. With temp-tables for me it is a matter of producing consistent code that should be self-explaining containing no stuff intended to potentially mislead others. I don't want code for which I am held responsible contain superfluous stuff. With database tables it is an absolute necessity.
  5. When you are on a reasonably recent release of OpenEdge use classes to wrap access to temp-tables.
Heavy Regards, RealHeavyDude.
 

progdev1

Member
Thanks folks. The input is on this interesting and very helpful. It would appear to me that maybe in the past using locks with temp tables used to be the done thing and now it is not. I wasn't aware the code below was now valid, good to know. This alone would make me think that yeah its time to ditch the whole locks with temp-tables.
for each tt-customer no-lock :
assign tt-customer.customerkey = tt-customer.customerkey + 1.
end.
Thanks for your help.
 

tamhas

ProgressTalk.com Sponsor
There is something to be said for not writing code that doesn't mean what it appears to mean. Since Cringer's example shows that the presence of the lock keywords doesn't impact the behavior and therefore those keywords are deceptive.
 

davidvilla

Member
I have always used locks when accessing temp-tables - just to be consistent with the rest of the queries in the program involving real database tables. Though I know it is not necessary, I prefer to do it as a habit, so that I will not forget to use one when dealing with real tables.
However, what is the impact of using NO-LOCK in a CAN-FIND statement?
 

ForEachInvoiceDelete

Active Member
I never use locking statements with temp-tables, but always define the lock for DB tables.

Its became more of a habit than solid reasoning. I have never came across an issue that was solved by using lock statements on temp-table queries, so i have never bothered.

The only part of the code that i would class as bad standards is the order loop.

Edit: Didnt realise this was a zombie post.

David. Absolutely nothing.

Progress KB - Does the CAN-FIND function lock records if NO-LOCK is not specified?
 

GregTomkins

Active Member
I found these notes about CAN-FIND from one of our internal knowledgebase entries, you might find them interesting. Or not.

I was trying to disprove the 'CAN-FIND NO-LOCK does absolutely nothing' claim, but I failed ;)

--

You can put LOCK qualifiers on a CAN-FIND.

1. Unlike a regular FIND, the default is NO-LOCK.

2. Unlike a regular FIND, you can’t say EXCLUSIVE-LOCK, thus sidestepping questions about transaction scoping.

3. Like a regular FIND, if you say SHARE-LOCK and the record is in use, it will WAIT for the lock unless you specify NO-WAIT.

4. If you get an answer of FALSE from CAN-FIND, it could mean that the record doesn’t exist OR it could mean that it couldn’t lock it.

5. If you say (eg) ‘CAN-FIND(FIRST x SHARE-LOCK). PAUSE.’, no lock will actually exist, eg. you will be able to get a lock from another session.

6. Because of #5, a true result of CAN-FIND … SHARE-LOCK means that you still might not be able to get a lock. Which seems to negate the value of using CAN-FIND … LOCK to make sure that a record will be updatable, which along with #2 seems to make it kind of pointless.
 

zerovian

New Member
You need to read Martin's book on clean code. One of the topic's he discusses is "cognitive load". The basic concept is "if it isn't serving an purpose, then don't include it". This applies to useless comments, comments on "getters" where the only possible value is explained by the function name, overly complicated code, unused methods and so-on.

Don't create extra work for your brain by including the "noise" that is lock keywords that don't do anything. Let your brain work on more important stuff than filtering out the useless lock phrases.
 
Top