Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved Retrieval in FOR Loop Locks Up At Random

  1. How-to
0
Votes
Undo
  1. Olan Knight
    Olan Knight
  2. PowerBuilder
  3. Thursday, 30 July 2020 17:00 PM UTC

UPDATE:   31-JUL-2020

   I want to thank Chris, John, and Mike for their suggestions!

   Using most of Jon's suggestions, I added a YIELD() and a GarbageCollect() into the FOR loop - and the next run did not get locked up. After making a few more changes, I'll try the run again and if it does not get locked up I will mark this thread as RESOLVED.

Olan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PB2019R2
Windows 10, 64 bit platform
Oracle 12C
32 bit code, running both from the IDE and as an EXE


Summary:
I have a retrieval inside a FOR loop, and at random times it just locks up; i.e. the RETRIEVE call is made but it never completes.

Q: What could be causing the lock and how can I fix it?


Details:
The code flows like this:

Datastore   lds_aur

sqlca.autocommit = TRUE

FOR ll_aur = 1 to ll_max

   ll_rc = lds_aur.Reset
   sqlca.of_disconnect ()                 // Added as a way to maybe resolve the locks: to no avail
   sqlca.of_connect()                      //  "

   lds_aur.dataobject = "whatever"
   lds_aur.SetTransObject (sqlca)

   ll_count = lds_aur.Retrieve(ls_parm)    // This is the call that locks up and never completes

  <do work...>
 
   ll_rc = lds_aur.Update (TRUE, TRUE)
  
   COMMIT;

NEXT
sqlca.autocommit = FALSE

The DBA has checked the database: there are no other users, no locks, no blocking session, and no queries in process.


Setting AutoCommit to TRUE helped significantly; the locks started occuring every few hundred rows rather than every few dozen rows.

How to use the PFC Caching Service (pfc_n_cst_dwca...
StyleRaised border makes picture bigger
Responses (3)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 30 July 2020 19:14 PM UTC
  2. PowerBuilder
  3. # 1

Hi Olan;

 Unfortunately, Oracle is known for its over locking behavior plus you cannot control the locking mechanism via the SQLCA.Lock = "xx" setting like in other DBMS. Oracle does not support a "Read Uncommitted" mode either so accumulation of locks is exponential.

  What the random lock issue could be is that the DBMS is running out of lock table memory space. If this happens, Oracle may enter a "wait" state for available lock storage. If never satisfied, the App may seem to just freeze.

   To avert the above situation, your Oracle DBA team may need to adjust the Oracle Locking settings in your DB.

HTH

Regards ... Chris

Comment
Olan Knight
  1. Olan Knight
  2. Friday, 31 July 2020 15:27 PM UTC
That's great information! Thanks, Chris!
  1. Helpful
There are no comments made yet.
mike S
mike S Accepted Answer Pending Moderation
  1. Thursday, 30 July 2020 18:41 PM UTC
  2. PowerBuilder
  3. # 2

oracle should not have autocommit true (docs seem to indicate oracle just ignores that setting, i think it caused problems in old versions)

 

move the connection, dataobject setting, create statement, etc OUT of the loop.  check for errors too .

your loop should just be:

for

  ll_count = lds_aur.Retrieve(ls_parm)    // This is the call that locks up and never completes

  <do work...>
 
   ll_rc = lds_aur.Update (TRUE, TRUE)
   if ll_rc =  - 1 then

      rollback;

   else
     COMMIT;

   end if

  lds_aur.reset()

next

 

Comment
Olan Knight
  1. Olan Knight
  2. Friday, 31 July 2020 15:28 PM UTC
That's exactly how I had it initially.

Moving the connection code into the loop was an attempt to force Oracle to NOT lock up by intiating another connection. By itself, that attempt failed. :(
  1. Helpful
mike S
  1. mike S
  2. Friday, 31 July 2020 15:59 PM UTC
gotcha.

...



In PB2017R2 there was a bug that caused datawindows with large amounts of data to HANG the system when a reset or destroy of the datawindow occurred. This had to do with a PB accessibility dll.

adding the following to the pb.ini file in the application's directory fixed the problem:



[Data Window]

ACCESSIBILITY=0



This bug is supposed to be fixed in 2019R2. I believe i even tested it in 19R2, but i don't recall for sure.

  1. Helpful
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Thursday, 30 July 2020 17:40 PM UTC
  2. PowerBuilder
  3. # 3

Hi, Olan -

Have you tried:

1. Create/Destroy the DataStore each time through the loop? Maybe also include a GarbageCollect() call after the Destroy?

2. Issuing a Yield() after the Commit?

I'm not claiming these will resolve the issue you are experiencing... They're just a couple of ideas I would try if I were in your situation.

Good Luck! -John

Comment
Olan Knight
  1. Olan Knight
  2. Friday, 31 July 2020 15:31 PM UTC
John -

I added the YIELD() to the top of the loop, and I added the GarbageCollec() call in there, as well. As expected ,the processing slowed down - but it WORKED! The code did not get locked up.



I needto make some more mods to the code and then I'll run it again. If the code does not lock up I'll mark this issue as RESOLVED!



Thank You AgaIn!



Thank you for the suggestions!
  1. Helpful
John Fauss
  1. John Fauss
  2. Friday, 31 July 2020 18:20 PM UTC
You're welcome, Olan. I'm glad it appears to have helped.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.