I am working on my first real multi-user web app, and I am encountering some unexpected intermittent behavior. I am using Xojo Cloud/SQLite for development, but will be moving to AWS/SQL Server for production.
The app lets the user create jobs and add them to a queue (a db table) to be processed by an external service that occasionally connects via HandleURL. When the external service connects and asks for the next job, sometimes the app doesn’t find any jobs in the table when there are definitely jobs in the table. And sometimes it does. I haven’t been able to find a pattern for the works/doesn’t work.
The users accesses the db via a session connection, and HandleURL accesses the db via an app connection. The SQLite file lives in the SpecialFolder.Documents folder.
The app does a lot of logging and I can see the external service connecting and App.DB.GetNextJob returning 0 records, while at the same time I can see Session.DB.GetNextJob returning 1 record. And it’s not a timing issue, since the external service can connect multiple times and still not see any jobs to process.
I initially though this might be the result of multiple instances of the app being launched (one for HandleURL and one for a user session?), but it doesn’t make sense because they should all point to the same .SQLite file, right?
I wrote a class to process the request from the external service, so all HandleURL does is pass the WebRequest to a method of my class (which is currently a property of the App) that returns the WebResponse to HandleURL.
Should I create a new instance of my class every time HandleURL gets raised? And give my class instance its own DB connection?
I’m not super clear on how HandleURL really works. If another request comes in while HandleURL is busy, does the request just get queued? Is a new thread spawned for the new request?
Yes. Every HandleURL and Session should get its own connection.
As far as I know, HandleURL is outside the “Each Session gets its own Thread” design. I think it’s on the main thread, but it would be great if @Ricardo_Cruz could confirm that.
Either way, if you really need it to be atomic use a CriticalSection; but my armchair advice would be to start with making sure each HandleURL gets its own Database connection.
I really don’t think its anything to do with timing on commits or anything like that. Right at this moment I have two different users logged into the app and each user can see the job in the job list (returned by Session.DB), but HandleURL sees nothing.
Since the ‘app’ side uses a Session, this might mean persistence of the db data allows the query to finish and data to be returned, where handleURL isn’t persistent at all, and maybe the data is going out of scope before the data can be evaluated.
If HandleURL is calling the App.DB.GetNextJob() function, have you tried just putting some static value to return in there, to see if a ‘persistent’ value always gets returned?
After a bit more testing, it seems that it has something to do with the App.DB.GetNextJob function. In the same method that calls App.DB.GetNextJob, I added App.DB.SelectSQL to get the count from the Job table, and that query returns the correct number of records.
GetNextJob() requires a userID parameter so that it only returns jobs for the user that the external service is requesting for, so it somehow seems to be related to that.
I still don’t understand why it would work sometimes and sometimes not.
Anyways, i guess I’ve got some debugging ahead of me…
The Main Thread will only be used when reacting to events that aren’t coming from a web request. For example, a Shell.DataAvailable event will run in the Main Thread.
With a WebTimer, it depends. If its Location is set to Server, the Run event will happen in the Main Thread. However, if the Location is set to Browser, as the browser will send the notification, it will go through a separate Request Thread instead.
A wild shot in the dark, but can you double check if the SQLite file is being populated with data? Just in case you’re using an in-memory SQLite for each Session.
I think I found the root cause of the issue I was having. There was a misspelling (Complete vs Completed) of the job status I was passing to App.DB.GetNextJob() that resulted in no jobs ever being returned. I might need to convert those statuses to enumerations instead of strings.
@Ricardo_Cruz With my current architecture, HandleURL calls a class property of App to process the WebRequest and generate the WebRequest. Since that class is a property of the App, does the code in the class execute in the request thread? or in the main thread?
