Hi Team,
   To quote the Beatles:  "...with a little help from my friends.".
   Many of the Apps in my App suite have a TLabel that displays the current # of sessions in use v the allowed sessions.
   In order to get the number of sessions in use, a simple query is executed:
   {sql}
   SELECT * FROM Configuration.ServerSessions WHERE (Connected = True)
   {sql}
   Now, each App has a Timer running that triggers the sql via a Query. Their times vary.  As does when any App is opened.
   There are up to 29 Users and around 20 different Apps for them to be using one or more of, of which 16 have the above query/display functionality.
   It is/was therefore expected that at some time, one or more of the Apps will try to run the query at the same time or while an existing query is still running.
   This results in a 506 error as the second query can't lock the Session Manager.
   When you have a lot of Users with a lot of Apps running, the odds of the conflict get a lot higher.
   I tried all sorts of tricks, several from these forums, but could never truly get rid of the 506 error.
   Until now.
   In the wee hours of the other morning, a thought came to me, as they do, what about taking out all those queries and adding just one query into the EDB Server as a Job?  That would get rid of the multiple, conflicting attampts.
   Good idea, but what to do with it?
   Ah Ha!  Second wee hours brain wave, write the result and the datetime to a table, lets call it DBiSessions.
   Then all the Apps have to do is read the table.
   Timing:  Ahhh.  The smallest time frame for a Job in the EDB Server is 1 minute.  What would be the impact?
   Worst case, a minute before the value can be initially shown at any one time in an App.  It used to be every 30 seconds, or so.  We/they will survive.  Probably won't even notice.
       I would prefer every 30 seconds but the Jobs functionality doesn't allow it.  I'll survive.
   Bottom line:  I now have this query running as a Job every minute from 0700 to 1900 every day.  Works beautifully.
   No more 506 errors.  
       Yes, I kept the original routines/queries in there so that if the Job fails for some reason, there is a fall back.
       Hope this helps somebody/anybody dealing with 506 in the future.

Regards,
Ian

Ian


Nice one.

I had a thought - I don't know how many database tasks you run in a thread which should have a separate session, but if there are any don't forget to allow for those in the licensed settings. I did for a short while (ie until people could no longer log in.

Roy Lambert

Tks Roy.  Noted.

Roy, Fernando,
I decided to not go with using 'Licensed Sessions', it is plain text and can be manipulated by an enterprising Sys Admin.

For my Apps I have the number of allowed sessions encrypted in a string, along with other 'sensitive' information, stored in an .ini file.
 
Yes they could find it, but any attempt to play with/change the encrypted string will result in their App(s) failing to work.

Thanks for your thoughts guys.

Regards,
Ian

Thanks Ian. This is useful.