SQLITE with WAL (multiuser) mode: behavior that disappoints me

olivierV · January 14, 2016, 7:34pm

Hi all,

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?

Norman_P · January 14, 2016, 7:38pm

https://www.sqlite.org/wal.html#concurrency

scott_boss · January 14, 2016, 7:50pm

WAL is not perfect but it is much better than it was before we had it.

Norman_P · January 14, 2016, 8:00pm

High concurrency is NOT Sqlite’s forte
They tell you this
http://sqlite.org/whentouse.html
See Situations Where A Client/Server RDBMS May Work Better

olivierV · January 15, 2016, 12:37pm

Thank you Norman and Scott.

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.

Configuration:

Xojo IDE Windows 2015R4
Web App
SQLITE:
-> Journal = WAL
-> Synchronous = 1
-> cache_size = 100000
-> page_size = 4096
-> busy_timeout = 10000

Example :

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!

KevinW · January 15, 2016, 8:31pm

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.

olivierV · January 16, 2016, 12:02pm

Yes.

olivierV · January 16, 2016, 3:05pm

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.

Here is my test project:
https://dl.dropboxusercontent.com/u/24945071/testSqlXOJO.xojo_binary_project

Start building
Launch the web app
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?

Eli_Ott · January 16, 2016, 6:56pm

WAL is not on!

olivierV · January 16, 2016, 7:18pm

It is activated. In the Open event of the App:

db.SQLexecute("PRAGMA journal_mode=WAL;")

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!

Eli_Ott · January 16, 2016, 7:24pm

You don’t need that line in App.Open delete it. Note also that you close the file, so it doesn’t make sense at all.

Anyway, you need to turn on WAL mode on each connection. So you must do it in Session’s Open event.

In addition when you have issues like this, comment out all lines which optimize something. As soon as it works, turn them on again.

This will work:

[code] self.DB = new SQLiteDatabase
dim f As folderItem
f=getfolderItem(“testXOJO.sqlite”)

self.db.DatabaseFile=f

db.threadYieldInterval=10

if self.db.Connect=true then

self.db.MultiUser = True   // Turn on WAL mode

// Comment out the following lines for test purposes
// self.db.SQLExecute("PRAGMA busy_timeout=10000);")
// self.db.SQLExecute("PRAGMA foreign_keys=ON;")
// self.db.SQLExecute("PRAGMA cache_size=100000;") 
// self.db.SQLExecute("PRAGMA page_size = 4096") 
// self.db.SQLExecute("PRAGMA case_sensitive_like=OFF;")
// self.db.sqlexecute("PRAGMA synchronous=1;")

end if[/code]

olivierV · January 16, 2016, 7:42pm

NO.

This is one of the few pragma that does not need to be put to each connection.
As written in my previous comment, I tried … You have tried ?

https://www.sqlite.org/pragma.html#pragma_journal_mode

[quote]SQLITE Doc:

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.

Eli_Ott · January 16, 2016, 7:46pm

Try it. With the code amended like proposed it works here.

olivierV · January 16, 2016, 8:04pm

I just tried your code, it does not work … Same problem. You use which version of Xojo and what operating system?

KevinW · January 17, 2016, 1:16pm

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.

olivierV · January 17, 2016, 1:58pm

yes

But look at my last test:

https://forum.xojo.com/29395-sqlite-with-wal-multiuser-mode-behavior-that-disappoints-me/p1#p241524

This is actually not a SQLITE problem (WAL or not), but a Xojo thread/session problem or a miscommunication between Xojo and database engine.

olivierV · January 17, 2016, 2:14pm

I made a Feedback with my latest findings:

(https://xojo.com/issue/42212)>]<https://xojo.com/issue/42212>

KevinW · January 18, 2016, 1:28am

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.