× Discuss on Template programming, jBASE programming, Enquiries, No-File enquiry, Enquiry routines, Version, Version routines, Menus, Abbriviations, Creating local reference fields, Fast path enquiries, Creating charts and graphs, Generating Reports, Deal slips, Straight through processing, Multi Company and Multi Book setup, Tabbed screens, Composite Screens, T24 API, etc...

Deadlock Detected Or Waiting Too Long on F.LOCKING during AA creation

  • MasterL
  • MasterL's Avatar Topic Author
  • Offline
  • Premium Member
  • Premium Member
More
2 weeks 3 days ago #23447 by MasterL
I am trying to create multiple AA lending records using OFS but I realized only one record was being processed at a time, even with multi-threading. This has made the process extremely slow to the point of processing a single record every 15 to 30 mins. 
On checking the TAFJ logs I saw the error below which I suspect is responsible for the slow processing.

DEADLOCK DETECTED OR WAITING TOO LONG  F_LOCKING*FBNK.ACCOUNT

Unfortunately the client does not have DM module installed so we are having to feed the OFS Messages manually using OFS.CALL.BULK.MANAGER.
OFS.POST.MESSAGE was taking as long and the service kept dying before completing the processing.

So my questions:
a) Is there a way to get around the deadlock error above? As you can see, this is not a locking on a specific account. Rather it's a deadlock on the LOCKING table itself, presumably because one record is locking the F_LOCKING*FBNK.ACCOUNT record while generating an account number and thereby blocking the rest of the agents from processing

b) Is there a better/quicker approach to loading the data? It's a reverse and replay operation so most of the loans are many years old and as such they take a lot longer to create.

Any help/assistance will be highly appreciated.

Env Details:
 - R17
 - TAFJ
 - Windows
- SQL Server

Please Log in or Create an account to join the conversation.

  • VK
  • VK's Avatar
  • Offline
  • Platinum Member
  • Platinum Member
  • TAFC|R19/R21
More
2 weeks 2 days ago #23448 by VK
Hi
F.LOCKING>FBNK.ACCOUNT is used when a new account is being created - to get its number. The only way to avoid that lock - set UNIQUE.NO in AUTO.ID.START>ACCOUNTNO. But this will affect all accounts being opened, not only AA-related ones so account numbers will be generated randomly (and this might not fit the established procedure).

I used to create multiple loans in AA using OFS (data migration 8 years ago) and didn't experience such problems.. but it was TAFC on DB2.

Please Log in or Create an account to join the conversation.

  • MasterL
  • MasterL's Avatar Topic Author
  • Offline
  • Premium Member
  • Premium Member
More
1 week 2 days ago - 1 week 2 days ago #23453 by MasterL
Thanks @VK for your suggestion.

I however wasn't able to apply it for the exact reasons you quoted. Even after I limited the base table to numbers (alphanumeric account numbers are not accepted), the generated account numbers would violate the checkdigit requirements set by the bank. In addition, there was no way to get around the INTERCO.parm settings (branch code and length restrictions) while utilizing the auto.id.start functionalty.

In the end though I did come up with a workaround, which is to pre-generate the account numbers and inject them in the OFS message strings.
I did this by creating a dummy account in VALIDATE mode using OFS.GLOBUS.MANAGE routine (OFS Bulk would also work). The data component of the OFS string was also set to null so either way no actual account was going to be created. I was only interested in the response which contains an account number. 

    Y.REC.ID = ""
    Y.OFS.FUN = "I"
    Y.OFS.PROCESS.MODE = "VALIDATE"
    Y.VERSION = "ACCOUNT,"
    Y.OFS.DATA = ""
    OFS.REC  = Y.VERSION
    OFS.REC := "/" : Y.OFS.FUN
    OFS.REC := "/" : Y.OFS.PROCESS.MODE
    OFS.REC := ",//" : ID.COMPANY : ","
    OFS.REC := Y.REC.ID : ","
    OFS.REC := Y.OFS.DATA
    Y.OFS.SRC = "GENERIC.OFS.PROCESS"
    OFS.RESP = OFS.REC
    CALL OFS.GLOBUS.MANAGER(Y.OFS.SRC, OFS.RESP)
    NEW.ACCT.NO = OFS.RESP['/',1,1]
Last edit: 1 week 2 days ago by MasterL. Reason: Update

Please Log in or Create an account to join the conversation.

Time to create page: 0.156 seconds