With WAL mode, Sqlite theoretically can simultaneously handle 1 writing thread and many reading threads.
But in practice, I noticed that there is already a thread writing (eg a long operation), and a second thread tries to write at the same time: The second thread is blocked of course, but during the timeout (10 seconds by default) where it is blocked, the reading threads are also blocked.
As soon as the second writer thread timeout is complete, other threads can again read while the first thread is still writing.
Of course, you can improve the situation by reducing the timeout, but it’s still disappointing. I did tests using the sqlite.threadYieldInterval and doevent, but nothing conclusive. You know what this limitation is due?
I have done many tests, including changing pragmas (Synchronous…), it changes nothing.
I thought it was a problem of SQLITE, but I wonder now if this is not a problem of Xojo, because I realize that while the first SQLITE thread writes, and that the second SQLITE thread attempts to write (for the duration of timeout), it is not only the SQLITE reading threads that are blocked, but in fact other Xojo threads/sessions. Even those who do not use the database.
Therefore, a new transaction (with an IMMEDIATE RESERVED LOCK) is sufficient to block the app if it is already a thread that writes to the database.
Now if I replace my IMMEDIATE transaction by DIFFERED transaction (For some transactions, it is dangerous), the application is no longer blocked. For the timeout is not supported!
Does each session have it’s own db property and connection? I’ve not noticed what you’re seeing and I’ve used SQLite quite a bit with web apps. It might just be that our usage patterns are too different for me to stumble on this particular behavior.
I redid many tests from a new blank project, with the Xojo SQLITE engine, and with the MBS SQLITE engine.
On this new project, the DEFERRED transactions are complying with the TIMEOUT.
By cons, a Xojo web application is actually frozen when:
A session starts a long write transaction.
At this point, other threads (and sessions) of Xojo are always responsives.
Another session is trying to start a new write transaction. Of course, SQLITE engine refuses this request, the first transaction is not complete, ok. But before refusing, the entire application is frozen during the timeout (10 seconds by default). All other threads and sessions are frozen during this timeout!
There are only two active sessions.
I tested on Windows 10, 32 bits web app, Xojo 2015R4.
Open the app on two browsers (or two tabs on one browser, it’s the same thing).
On a browser, click the “INSERT” button. The app begins a transaction to import one million lines (So that the transaction lasts long enough for there to be another write request at the same time).
Click on “go page2”, “go page1” on the same webPage, and then do the same on the other browser: no problem, the app responds well during import.
Now click on the “insert 1 line” button (importation of a single line) on the page 2 of the first or second browser.
Both browsers which now frozen for the duration of the timeout.
I did some tests with SQLiteDatabase.ThreadYieldInterval, DoEvents, etc. No change. Each transaction is also launched in a new thread (not the session thread), no change.
Then I did the same tests with SQL MBS plugin. Same sqlite engine. I have the same problem. Unless I use the “preemptive threads” methods of MBS (awesome). There is no problem, no gel. So the problem comes from Xojo, not from SQLITE.
Question for Xojo team:
Is it normal behavior, the framework is designed like that, or is this a bug or something improvable? I understand that we are limited by cooperative threads. But here, it seems a bug, how only two threads can take all application resources?
This pragma is not need to be reminded at each connection. If you open the data file in a SQL Editor and you tapes “pragma journal_mode”, you will see that the response will be well “WAL”. I tried also with the Xojo alias (sqliteDatabase.multiuser), it is the same thing.
And even without WAL, this behavior is not normal. The second write lock request should be refused at the end of timeout for requesting thread, that’s all, there is no reason that *all of the other threads/sessions are frozen during the timeout!
The WAL journaling mode is persistent; after being set it stays in effect across multiple database connections and after closing and reopening the database.[/quote]
it makes sense, since I no longer need this connection. The database is created, and WAL activated.
Then I open a connection to each new session. And I close with the closure of the session.
DO you have a wal file next to the db? I was just now reminded that I would not get the wal file using Xojo when I tried a while back, so I always set it using Valentina Studio or some other tool outside Xojo. That always seemed to work so it became a habit for me. It may not be necessary now though. Haven’t checked in a while.
I understand what you’re seeing now. It might be that since you have the same process accessing the db that the multi user support doesn’t work well. The SQLite FAQ seems to indicate it supports multiple processes having simultaneous access.
Have you tried duplicating the test but with two different copies of the app. It might not be possible for Xojo to behave the way you expect without preemptive threads. The MBS plugin sounds like a simple answer, or you could have a separate helper app that long requests get sent to. I think I haven’t seen this in my apps because I try to keep everything short. I can’t think of anything I have in any of my apps that would write to the db for more than a fraction of a second.