Can someone explain in detail what exactly this means?

"If many threads and/or processes need to write the database at the same instant (and they cannot queue up and take turns"

I am making a web application where I know that I will not need a client/server architecture, however, the site will have about 500K visits a day where users will both read and write.

The writes are small writes consisting of ratings, i.e. storing a single integer in a table. Other writes are of similar type, single integers for statistics usage (did the user click on your shop?), etc.

I am considering using SQLite, but I don't quite understand if the above will cause a problem in my case.

Any advice and explanation will be greatly appreciated.

Kind regards

Di you read the rest of the statement?

SQLite only supports one writer at a time per database file. But in most cases, a write transaction only takes milliseconds and so multiple writers can simply take turns. SQLite will handle more write concurrency that many people suspect.

And it continues on.

but I don't quite understand if the above will cause a problem in my case.

Have you considered simulating what your application would do with 500k visits with reads/writes? This will give you an idea if you can use SQLite.

500K visits a day. To allow for a working day and mixed timezones, assume a 12 hour day with equal distribution. That's 42,000 visits an hour, 700 visits a minute, 12 visits a second, perhaps peak at 18 visits in a second.

You describe each visit being one or two very simple SQL commands. There's nothing in SQLite which would fail to deal with that, assuming solid state storage. You're more likely to encounter problems with your web backend (LAMP ?) or, if your web pages are huge, bandwidth.

Assuming your data is valuable, I remind you to figure out your backup strategy procedure right at the start.

So I decided to do some testing and it went really bad, with locking all over the place.

I have the following options set:

PRAGMA journal_mode = WAL;
PRAGMA temp_store = MEMORY;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = -64000;

I have a small PHP script that inserts a single row with some random data into a table.

The script gets called in a loop from just two different machines on the network.

SQLite3::prepare(): Unable to prepare statement: 5, database is locked in ...

I have tried various changes to the settings, but I get the same result. About half of the queries fails with the locking warning.

I had ecspected the queries to build up in a queue, seeing a decrease in write speed, but they don't queue, they just block.

I am thinking it is because of the multi-threaded nature of a PHP web application?

Am I missing something?

Hmm. Changing the busy timeout value solved the problem. Now I am simulating thousands of writes from the two machines and all runs fine.

$sqlite_db->busyTimeout(500);

However, I don't know what a reasonable timeout is.

That's only 0.5 sec. Next try with 20 secs.

What I don't understand is that if I use PRAGMA to set the busy_timeout and I verify it is set, I still get the locking error. But if I set it during code execution, then I don't get the error:

$sqlite_db->busyTimeout(20000);

Timeout setting is not a setting of a database file, it's associated with each connection to the database. If each web-page access makes its own connection, then you. need to set timeout for each one. Which is what you're doing.

The number in the brackets is milliseconds. 20 seconds is a fine and reasonable timeout setting and you should have a usable setup with it.

Looks like people on your reddit post also give you as much confidence as well:

https://www.reddit.com/r/sqlite/comments/modtns/deploying_sqlite_on_a_production_website/

How can one change the built-in default connection busy_timeout to something greater than zero (which seems impractical for default)?

I'm having similar issues; too many 'locked' messages from two processes that access the same database every few seconds.

Go to this page, https://sqlite.org/keyword_index.html , and search for the word "timeout". The first hit leads to the busy_timeout pragma.

But I think this pragma is still just for the current connection. The question is how to set it so that connections default to a longer timeout than 0 by default without each connection needing to set its own timeout.

Include it in a wrapper function that also takes care of error handling and reporting when setting up the connection. That way you don't have to worry about it. AFAIK you always have to set the timeout each time you connect, or perhaps supply your own timeout function (not sure about that one).

There is no compile-time option for that default. The datum which holds this timeout is one of many that are initialized to 0 in a memset(...) call. To provide customized "defaults", a wrapper around one of the sqlite3_open*() calls would be easiest, short of modifying one of those calls directly.

Create en extension that sets the timeout then register it in the autoexec list.

#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1

static int sqlite_set_autobusy(sqlite3 *db, char** pzErrMsg, void* pApi)
{
   return sqlite3_busy_timeout(db, 60000);
}

#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_autobusy_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi)
{
    SQLITE_EXTENSION_INIT2(pApi);

    sqlite3_auto_extension((void*)sqlite_set_autobusy);
    sqlite3_busy_timeout(db, 60000);
    return SQLITE_LOADED_PERMANENTLY;
}

That return code should be SQLITE_OK_LOAD_PERMANENTLY

You will not be able to do that without altering the SQLite3 code. However, if you can settle for building something which behaves as if you had done that, you can create a shim DLL which mostly just passes some/most calls through to a "real" SQLite3 DLL but does something different some select calls, such as the wrapping suggested elsewhere in this thread.

PRAGMA journal_mode = WAL;

The script gets called in a loop from just two different machines on the network.

Just to make sure I'm understanding this right, each of those two machines is accessing its own separate database. Right? Because WAL mode doesn't work across different machines.

Or is that perhaps part of your problem :)