SQLite non-exclusive RESERVED lock?

Question

I am working on improving SQLite performance for my site, especially regarding transactions. Essentially, I'm looking for a way to defer a database entry in the process so that they can all be executed immediately. However, when I accumulate update requests, I would like other processes to be able to read and write to the database, and only lock the file for writing as soon as the commit is issued in the process.

When viewing the documentation, it seems that as soon as an update command is issued in a transaction, the process receives a RESERVED lock, which (if I remember correctly) means that any other process that tries to either add an update request, its own transaction or transaction commit is unable to do this and therefore locks until the transaction locks the process with the lock.

I am sure there are very good reasons for data integrity regarding this particular function. All I can say is that in my case there is no threat when performing these updates at the same time.

One solution is that in each process, I could accumulate the text of the queries that I want to call in an array, and then loop it when I am ready to write, but I wonder if it is possible that the SQLite transaction can do this for me automatically.

update: What do I mean when I say "do all my updates at once", in fact, it uses transactions in SQLite to get only an EXCLUSIVE lock and write it to disk once per process, and not once per request. This results in 100x speedup using SQLite.

I did some basic testing, and it seems that when you have several processes adding requests to their transactions, as soon as you click on the update request, the process tries to get a RESERVED lock. Since only one process can have a reserved lock, this means that any other processes trying to obtain a lock will be blocked until the process with the lock completes the transaction.

I admit that this problem can be premature optimization, since I have not yet encountered any penalties, but I conducted several simple tests and 100 users, each of whom created and performed a transaction with 100 requests, takes about 4 seconds to PHP is my machine.

Answer 1

SQLite supports ATTACH to join one database to another database. Perhaps you can accumulate your data in a separate database, and when you are ready to combine the accumulated rows, attach a separate database, copy the rows into one statement and disconnect.

Edit: A similar OP proposal was made on the sqlite users mailing list , with some further discussion.

Answer 2

Better than attaching a database, just create a temporary table. (CREATE TEMPORARILY ...)

And take a look at the new WAL log mode, which does what you are trying to do manually and allows simultaneous writing and reading (but not simultaneous writing).

#pragma journal_mode = WAL