The entire app hangs during the SQLITE timeout.

Databases
1

Hi all,

This is an important (and systematic) bug, I have already reported it, but I most clearly rewritten (hopefully) here.

The problem is (in sqlite WAL mode: db.multiuser):
When the database is already busy/locked (a write transaction is already in progress), a new write request will freeze all the app during the timeout.

Of course, the second write request must be refused if the database is always busy/locked at the end of the timeout, but the app should not freeze during the timeout.

At first, this bug is invisible because the databases are small, so the transactions are very fast, and therefore are not in concurrence. But after some time, when the database becomes larger, tensions arise, especially in business applications with long transactions (While SQLITE still has resources).

Also, users tend to think that this is another problem. For example, a customer thought he had a network problem when the session was freezing from time to time (Web App). But it was this sqlite timeout problem. If the timeout is 2 seconds, the entire web app, all sessions, will be freezed for 2 seconds if the database is already busy.

it greatly reduces the interest of SQLITE WAL mode (db.multiuser) in Xojo.

I was afraid this is a limit of cooperative threads in Xojo, but no:

I spoke to Mr Hipp, creator of SQLITE. He’s very accessible. He told me:
“The interface that governs the timeout is “sqlite3_busy_handler()”.
Perhaps you can modify its callback to yield to another thread.”

Xojo does it? Maybe the problem is here.
Xojo already handles the “progress_handler” (in Xojo: sqlitedatabase.threadYieldInterval),
but according to Mr. Hipp, it’s the “busy_handler” is called when the database is busy/locked.

Below, I will put a test project and two videos showing the problem. I’ll write a new Feedback.

2

Test project:
https://dl.dropboxusercontent.com/u/24945071/testSqlXOJO2newConnexion.xojo_binary_project

A video showing the problem on Windows:
Windows Video

A video showing the problem on Mac:
Mac Video

Test:

  • Run the web app

  • Open the web app in 3 windows (3 sessions).

  • In the first session, click the “insert” button. The session launches a tread. This thread opens a new SQLITE connection, and then starts a long write for several minutes (long enough for testing). During this writing loop, the thread is sleeping regularly. I also did tests by changing the value of db.threadYieldInterval, thread.priority, app.YieldToNextThread.

  • In the second session, click on “Go page2”, and on “Select 1 line”. So this session starts a db reading in a new thread/new db connection. Therefore, simultaneously, we have a write transaction and a read. Ok, no errors, everything works.

  • All sessions are reactive. During the write operation, I can also navigate through the webPages, open multiple sessions, etc. Everything works.

  • The first session continued to write.

  • In the second or third session, click “insert 1 line” (page2): We ask a writing while the first is not complete. Result: All the app, all sessions (and thus all clients/browsers pages) will be hanging during the timeout.

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

3

I use the SQLDatabaseMBS to connect to SQLite in a thread. It has two calls (SQLExecuteMBS and SQLSelectMBS) that are thread aware and allow your app to keep responsive even when the database is occupied with a large search.

4

Olivier doesn’t have a problem with a simultaneous read and write either, as I understand it his problem is concurrent writes freeze every client for the duration of the timeout interval.

5

Even PostgreSQL can do simultaneous reads, but only one write command at once. It will ‘freeze’ if you want to write while a large SELECT command is running. It will block all other users. If your databases are large, you need to place as many SQL commands as possible into threads, unless your users are happy to have the apps freezing.

6

David , you opened the test project ? You will see that each SQL command is in a different thread .

When SQLITE is writing , all other threads that want to write have to wait. This is actually normal. The problem is that the whole xojo web app is also frozen during the timeout ,including threads that do not work with the database .

7

[quote=247197:@David Cox]Even PostgreSQL can do simultaneous reads, but only one write command at once.[/quote]I don’t have any experience of PostgreSQL but I’d be very surprised if locking wasn’t at record level rather than database level. With Firebird you only get an issue if simultaneous write transactions try to edit the same record, then the server arbitrarily chooses one transaction to carry on its way and the other(s) receive a deadlock error.

8

Only true if attempting to write to the same record, the database does not freeze.

9

Well, if you use MBS SQL Plugin and you have trouble in this direction, I can assist you.

10

well, voila , the problem is actually the SQLITE “busy_handler” that is not handled by Xojo . Christian now handles it in its SQL plugin http://www.monkeybreadsoftware.de/xojo/plugin-sql.shtml : the app no longer freezes. Congratulations to Christian ! Frankly, fortunately there are professionals such as Christian that revolve around Xojo.

To demonstrate the problem, I used a long transaction in my example . So, many believe that the problem arises only in this case . But even with small writings , all sessions, all handleUrl, will be frozen (including those that do not use the database) as soon as two writings will compete : sometimes a third of a second, sometimes a second or two, etc. And without error if the thread finishes before the timeout. It’s vicious . Some users may have a degraded experience, a lack of fluidity in the interface, etc. , without knowing the origin of the problem. Often, they will think that they are network problems or the app is overloaded!

In short, I feel that many people underestimate the problem. Unless using load balancing and almost one web app per session(!), I recommend using the Christian SQL plugin.

11

If that is indeed the issue, I would really like Xojo to address it.

12

After a request from Tim Streater, William (Xojo team) has updated the Feedback: he said that this problem had come out of their radars because there’s not enough votes…

So, if you are concerned, Thanks to vote and remind to Xojo, time to time, the existence of this problem.

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

13

I did add a function for this in MBS SQL Plugin, so if you need it, you can try my plugin.