Lock Table Overflow

Y

ymjamal

New Member
Hi,
I'm not very good in Transaction controlling.
I have a temp-table with 1.2 millions records that I need to add to a database.
I have a -L parameter set quite high 150,000.
This is in progress 10.1 in unix box.
This is the piece of code:
FOR EACH TT-ob_rate
WHERE TT-ob_rate.fri = YES
BREAK BY TT-ob_rate.dest-area
BY TT-ob_rate.orig-loc
BY TT-ob_rate.mode
BY TT-ob_rate.unit-type
BY TT-ob_rate.width
BY TT-ob_rate.length
BY TT-ob_rate.del-carrier
BY TT-ob_rate.hdr-flag DESCENDING:
IF FIRST-OF(TT-ob_rate.del-carrier) THEN DO:
CREATE fri_hdr.
{gen/x/ourrecid.i fri_hdr} /* assign a random unique number to ourrecid */
ASSIGN fri_hdr.dest-area = TT-ob_rate.dest-area
fri_hdr.orig-loc = TT-ob_rate.orig-loc
fri_hdr.mode = TT-ob_rate.mode
fri_hdr.unit-type = TT-ob_rate.unit-type
fri_hdr.width = TT-ob_rate.width
fri_hdr.length = TT-ob_rate.length
fri_hdr.rnd-rate = TT-ob_rate.rnd-rate
fri_hdr.NA-flag = TT-ob_rate.NA-flg
fri_hdr.currency = TT-ob_rate.currency
fri_hdr.del-carrier = TT-ob_rate.del-carrier
fri_hdr.route-option = TT-ob_rate.route-option
fri_hdr.distance = TT-ob_rate.distance
fri_hdr.onbord = TT-ob_rate.onbord.
END.
CREATE fri_dtl.
ASSIGN fri_dtl.ourrecid = fri_hdr.ourrecid
fri_dtl.thick = TT-ob_rate.thick
fri_dtl.act-thick = TT-ob_rate.act-thick
fri_dtl.rnd-rate = TT-ob_rate.rnd-rate
fri_dtl.NA-flag = TT-ob_rate.NA-flg
fri_dtl.onbord = TT-ob_rate.onbord.
IF TT-ob_rate.Onbord = NO THEN
DELETE TT-ob_rate.
END.

How can I modify this to overcome the lock table overflow.
Any hep will be much appreciated.
 
Your code would be much easier to read if it were enclosed within CODE tags. There's a button for that in the toolbar of the reply box.
 
ymjamal said:
Hi,
I'm not very good in Transaction controlling.
I have a temp-table with 1.2 millions records that I need to add to a database.
I have a -L parameter set quite high 150,000.
This is in progress 10.1 in unix box.
This is the piece of code:
Code: 
  FOR EACH TT-ob_rate
      WHERE TT-ob_rate.fri = YES
  BREAK BY TT-ob_rate.dest-area
        BY TT-ob_rate.orig-loc
        BY TT-ob_rate.mode
        BY TT-ob_rate.unit-type
        BY TT-ob_rate.width
        BY TT-ob_rate.length
        BY TT-ob_rate.del-carrier
        BY TT-ob_rate.hdr-flag DESCENDING:
      IF FIRST-OF(TT-ob_rate.del-carrier) THEN DO TRANSACTION:
        CREATE fri_hdr.
        {gen/x/ourrecid.i fri_hdr}
        ASSIGN fri_hdr.dest-area      = TT-ob_rate.dest-area
                fri_hdr.orig-loc      = TT-ob_rate.orig-loc
                fri_hdr.mode          = TT-ob_rate.mode
                fri_hdr.unit-type      = TT-ob_rate.unit-type
                fri_hdr.width          = TT-ob_rate.width
                fri_hdr.length        = TT-ob_rate.length
                fri_hdr.rnd-rate      = TT-ob_rate.rnd-rate
                fri_hdr.NA-flag        = TT-ob_rate.NA-flg
                fri_hdr.currency      = TT-ob_rate.currency
                fri_hdr.del-carrier    = TT-ob_rate.del-carrier
                fri_hdr.route-option  = TT-ob_rate.route-option
                fri_hdr.distance      = TT-ob_rate.distance
                fri_hdr.onbord        = TT-ob_rate.onbord.
      END.
      DO TRANSACTION:
        CREATE fri_dtl.
        ASSIGN fri_dtl.ourrecid      = fri_hdr.ourrecid
                fri_dtl.thick          = TT-ob_rate.thick
                fri_dtl.act-thick      = TT-ob_rate.act-thick
                fri_dtl.rnd-rate      = TT-ob_rate.rnd-rate
                fri_dtl.NA-flag        = TT-ob_rate.NA-flg
                fri_dtl.onbord        = TT-ob_rate.onbord.
        IF TT-ob_rate.Onbord = NO THEN
            DELETE TT-ob_rate.
      END.
  END.

How can I modify this to overcome the lock table overflow.
Any hep will be much appreciated.
Click to expand...
 
You're aware that your code changed between the first post and the second? There were no TRANSACTION qualifiers on your DO blocks originally. Which is the code you're actually running?
 
Code: 
FOR EACH TT-ob_rate
    WHERE TT-ob_rate.fri = YES
    BREAK
      BY TT-ob_rate.dest-area
      BY TT-ob_rate.orig-loc
      BY TT-ob_rate.mode
      BY TT-ob_rate.unit-type
      BY TT-ob_rate.width
      BY TT-ob_rate.length
      BY TT-ob_rate.del-carrier
      BY TT-ob_rate.hdr-flag
    DESCENDING:

  do transaction:

    IF FIRST-OF(TT-ob_rate.del-carrier) THEN DO:
      CREATE fri_hdr.
      {gen/x/ourrecid.i fri_hdr} /* assign a random unique number to ourrecid */
      ASSIGN
        fri_hdr.dest-area = TT-ob_rate.dest-area
        fri_hdr.orig-loc = TT-ob_rate.orig-loc
        fri_hdr.mode = TT-ob_rate.mode
        fri_hdr.unit-type = TT-ob_rate.unit-type
        fri_hdr.width = TT-ob_rate.width
        fri_hdr.length = TT-ob_rate.length
        fri_hdr.rnd-rate = TT-ob_rate.rnd-rate
        fri_hdr.NA-flag = TT-ob_rate.NA-flg
        fri_hdr.currency = TT-ob_rate.currency
        fri_hdr.del-carrier = TT-ob_rate.del-carrier
        fri_hdr.route-option = TT-ob_rate.route-option
        fri_hdr.distance = TT-ob_rate.distance
        fri_hdr.onbord = TT-ob_rate.onbord
      .
    END.

    CREATE fri_dtl.
    ASSIGN
      fri_dtl.ourrecid = fri_hdr.ourrecid
      fri_dtl.thick = TT-ob_rate.thick
      fri_dtl.act-thick = TT-ob_rate.act-thick
      fri_dtl.rnd-rate = TT-ob_rate.rnd-rate
      fri_dtl.NA-flag = TT-ob_rate.NA-flg
      fri_dtl.onbord = TT-ob_rate.onbord
    .

    IF TT-ob_rate.Onbord = NO THEN DELETE TT-ob_rate.

  end.

END.

If adding the "do transaction" block results in a warning about a transaction already being active then the problem causing too many locks lies outside this block of code.
 
ymjamal said:
Thanks!
Click to expand...

Sorry!
The original posting without the transaction is the one that I had problems with.
The second is what I'm planning to try.
I wonder if that will help. When I comply I get warning messages of TRANSACTION within TRANSACTION.
Unfortunately the building of the temp tabel takes over 10 hours.
Would the "TRANSACTION BLOCKS" with a "RELEASE" after the end of the block help?
TIA
 
Thanks
TomBascom said:
Code: 
FOR EACH TT-ob_rate
    WHERE TT-ob_rate.fri = YES
    BREAK
      BY TT-ob_rate.dest-area
      BY TT-ob_rate.orig-loc
      BY TT-ob_rate.mode
      BY TT-ob_rate.unit-type
      BY TT-ob_rate.width
      BY TT-ob_rate.length
      BY TT-ob_rate.del-carrier
      BY TT-ob_rate.hdr-flag
    DESCENDING:
 
  do transaction:
 
    IF FIRST-OF(TT-ob_rate.del-carrier) THEN DO:
      CREATE fri_hdr.
      {gen/x/ourrecid.i fri_hdr} /* assign a random unique number to ourrecid */
      ASSIGN
        fri_hdr.dest-area = TT-ob_rate.dest-area
        fri_hdr.orig-loc = TT-ob_rate.orig-loc
        fri_hdr.mode = TT-ob_rate.mode
        fri_hdr.unit-type = TT-ob_rate.unit-type
        fri_hdr.width = TT-ob_rate.width
        fri_hdr.length = TT-ob_rate.length
        fri_hdr.rnd-rate = TT-ob_rate.rnd-rate
        fri_hdr.NA-flag = TT-ob_rate.NA-flg
        fri_hdr.currency = TT-ob_rate.currency
        fri_hdr.del-carrier = TT-ob_rate.del-carrier
        fri_hdr.route-option = TT-ob_rate.route-option
        fri_hdr.distance = TT-ob_rate.distance
        fri_hdr.onbord = TT-ob_rate.onbord
      .
    END.
 
    CREATE fri_dtl.
    ASSIGN
      fri_dtl.ourrecid = fri_hdr.ourrecid
      fri_dtl.thick = TT-ob_rate.thick
      fri_dtl.act-thick = TT-ob_rate.act-thick
      fri_dtl.rnd-rate = TT-ob_rate.rnd-rate
      fri_dtl.NA-flag = TT-ob_rate.NA-flg
      fri_dtl.onbord = TT-ob_rate.onbord
    .
 
    IF TT-ob_rate.Onbord = NO THEN DELETE TT-ob_rate.
 
  end.
 
END.

If adding the "do transaction" block results in a warning about a transaction already being active then the problem causing too many locks lies outside this block of code.
Click to expand...

Tom,
I do get the warning message. I wonder if releasing the record after the subtransaction block would help?
TIA
 
No. Never use RELEASE. It does not do what you expect it to do. Mostly RELEASE just covers up bad transaction scoping. If you find yourself tempted to use RELEASE you need to stop what you're doing, go for a walk, clear your head, find someone to talk to etc and then come back and rethink your approach. If you still want to use RELEASE you should turn in your programmer badge.

So now the question becomes what are you doing in the rest of your code? Compile with XREF and look at where your transaction blocks are and what records are involved.

In general, transactions should be coded something like this:

Code: 
define buffer updCustomer for customer.

...

do FOR updCustomer TRANSACTION:
  find updCustomer exclusive-lock where custNum = 12.
  updCustomer.name = "fred".
end.

It is useful to create a buffer for updates, creates and deletes. This way you can easily use "strong scope" (do FOR updCustomer TRANSACTION) to control record and transaction scope without sacrificing the ability to willy-nilly use free references for no-lock reads. (One could argue that free references are a bad thing too -- but on the scale of things to deal with they are a much lower priority than poorly scoped transactions.)
 
TomBascom said:
No. Never use RELEASE. It does not do what you expect it to do. Mostly RELEASE just covers up bad transaction scoping. If you find yourself tempted to use RELEASE you need to stop what you're doing, go for a walk, clear your head, find someone to talk to etc and then come back and rethink your approach. If you still want to use RELEASE you should turn in your programmer badge.

So now the question becomes what are you doing in the rest of your code? Compile with XREF and look at where your transaction blocks are and what records are involved.

In general, transactions should be coded something like this:

Code: 
define buffer updCustomer for customer.
 
...
 
do FOR updCustomer TRANSACTION:
  find updCustomer exclusive-lock where custNum = 12.
  updCustomer.name = "fred".
end.

It is useful to create a buffer for updates, creates and deletes. This way you can easily use "strong scope" (do FOR updCustomer TRANSACTION) to control record and transaction scope without sacrificing the ability to willy-nilly use free references for no-lock reads. (One could argue that free references are a bad thing too -- but on the scale of things to deal with they are a much lower priority than poorly scoped transactions.)
Click to expand...
Thanks for the valuable advise of never using RELEASE.
This is a process that runs once a week and re-creates the freight costs for a freight rates inquire screens.
It provides freight from 10 origins to thousands of destination for different modes and products. All this ends into 1.2 million records.
These 2 tables (fri_hdr and fri_dtl) are then used for inquiry during the week.
After building the temp-table TT-ob_rate we delete all records in fri_hdr and fri_dtl (created in the previous week) and load the new data.

If I load the data into 2 NO-UNDO temp-tables simmilar to fri_hdr and fri_dtl with a count at 100, and then load from these temp-tables into the data base in chunks of 100 records, will this overcome my problem?
TIA
 
That depends. If there is a large transaction whose scope encompasses those sub-transactions (and it appears that there is such a trx now) then, no, it will not help.

Thanks for thanking me regarding RELEASE -- but the bigger point is the "DO FOR buffer TRANSACTION" syntax. Understanding that will take you a long ways. Ignoring it will just lead to continued problems.
 
I can only second what Tom said. Not using defined buffers and strong buffer scope for updating the database is a recipe for blowing the lock table and some other bad stuff like dead locks. Using defined buffer and strong buffer scope is - IMHO of course - very good practice making so much easier to have your scopes right.

IMHO, the concept of buffer and transaction scope is easy to master and you can even double check by compiling with XREF.

I recommend you to read the corresponding sections in the ABL handbook that is part of the product documentation. Namely the sections "Procedure Blocks and Data Access", "Record Buffers and Buffer Scope", and "Managing your Transactions".

Heavy Regards, RealHeavyDude.

P.S. - I am on a mission regarding buffer and transaction scope as I see so much bad coding in the ABL in that respect and they are basic concepts that you should understand in order to produce efficient ABL programs.
 
RealHeavyDude said:
I can only second what Tom said. Not using defined buffers and strong buffer scope for updating the database is a recipe for blowing the lock table and some other bad stuff like dead locks. Using defined buffer and strong buffer scope is - IMHO of course - very good practice making so much easier to have your scopes right.

IMHO, the concept of buffer and transaction scope is easy to master and you can even double check by compiling with XREF.

I recommend you to read the corresponding sections in the ABL handbook that is part of the product documentation. Namely the sections "Procedure Blocks and Data Access", "Record Buffers and Buffer Scope", and "Managing your Transactions".

Heavy Regards, RealHeavyDude.

P.S. - I am on a mission regarding buffer and transaction scope as I see so much bad coding in the ABL in that respect and they are basic concepts that you should understand in order to produce efficient ABL programs.
Click to expand...
I have to brush up my knowledge of transaction scope.
In this specific process there was no question dead locks, because these 2 table will be used during the week only for inquiry (read only). They need to be recreated every week end because the freight (fuel surcharge) changes.
I ended up loading 2 temp-tables similar of data base tables with a group number at 10,000 records. then loaded from these temp tables in chunks of 10,000. I could not think of another solution.
 
Last edited:
Note that in operations like this, you may think that you need the whole thing in one transaction scope since you want the whole thing current. But, given your description, it sounds as if, were the update to fail, merely rerunning the update would get the desired results. With a bit of fanciness, you could even restart it in the middle. If correct, then you shouldn't need a large lock table at all, but actually a very small one. Each record update could be its own transaction.

COMPILE XREF is your friend.
 
You must log in or register to reply here.
Back
Top