SQLite Forum

Dispatch Sqlite Instance with event loop

Dispatch Sqlite Instance with event loop

(1) By anonymous on 2022-01-11 11:22:09 [link] [source]

I use a single thread event loop for my code, like nodejs.

I need create a sqlite connection pool for each request, and dispatch the sqlite connection into a dedicated thread to do some query with uv_work.


I can guarantee each sqlite connection will be used by one worker thread all the time.

I can not bind sqlite connection into one thread all the time, every uv_work call will assign a random idle thread to do the work. (every time I create a uv_work for a request if I need database query)

This design will cause problem ?

The sqlite will be always open by one process with excl-unix vfs, SQLITE_CONFIG_MULTITHREAD will be setup.

(2) By Gunter Hick (gunter_hick) on 2022-01-11 13:15:33 in reply to 1 [link] [source]

Creating a new connection for each request will probably be slow (SQLite needs to parse the schema when creating a connection).

Having one global connection and running multiple threads against it will cause problems with transactions (no thread can be sure when a transaction begins and when it ends) and with concurrency.

Using a dedicated connection for each thread will probably give the best results.

How to handle thread local data is a question best handled in a forum dedicated to the programming language you are using.

(3) By anonymous on 2022-01-11 13:36:13 in reply to 2 [link] [source]

Thanks to explain.

I am not sure how to put connection into worker thread local storage, and I need to close it when app quit.

So I create multi connection from event loop thread, when I need run a query from worker thread, I take one from pool and push back into pool after work done. and close them all when app quit.

My question is the Sqlite connection instance will be execute from diff worker thread, is this safe with thread_mode=2?

(4) By Larry Brasfield (larrybr) on 2022-01-11 13:53:38 in reply to 3 [link] [source]

If I understand your intended usage correctly, it is safe with SQLITE_THREADSAFE=2. The SQLite library does not use thread-local storage or anything else which would cause it to work differently when a connection is passed from one thread to another at a time when no locks are held on behalf of the thread, a condition that certainly holds when no calls into the library are active. And as long as the work to be done is complete (as defined below) when the connection passes between threads, no other misbehavior, undefined behavior, or resource leakage will occur.

The "work to be done is complete" state means: No prepared statement exists (because the ones created have been finalized); and all explicit and implicit allocations due to be given to sqlite3_free() have been so handled. The former condition necessarily means no transactions can be open. (They are rolled back if still open at statement finalization.)

(5) By Gunter Hick (gunter_hick) on 2022-01-11 13:53:46 in reply to 3 [link] [source]

SQLite connections in multithread mode can be safely used by different threads inside the same process, just as long as no connection is attempted to be used simultaneously from two distinct threads.

There should be no problem as long as you are sure to return connections into the pool in the same state they were taken from the pool. Typically this would be "no open transaction". You could still prepare commonly used statements on each connection when creating that connection and finalize them during app shutdown. Just be careful to always run them to completion in the worker threads.

(6) By anonymous on 2022-01-11 14:19:22 in reply to 5 [link] [source]

Thanks for the explain.

Correct my if I am wrong.

no open transaction != No prepared statement exists

Accord by Larry Brasfield explain, I can not pass connection into other thread if there is prepared statement exists.

I use prepared statement to speedup query, and I can make sure no open transaction when work done.

Should I keep prepared statement to improve speed, or it will cause problem?

(7) By Larry Brasfield (larrybr) on 2022-01-11 14:26:42 in reply to 6 [link] [source]

I did not intend to state the barely necessary conditions. I stated sufficient conditions which exceed the minimum necessary in a way unlikely to engender mishaps. An "at rest" state for prepared statements could be defined (as Gunter did) so that those also could pass from one thread to another.

(8) By Gunter Hick (gunter_hick) on 2022-01-11 16:00:30 in reply to 6 [link] [source]

If you were to open a connection for each request and each request is completed by returning zero or more rows of data, you would do

1) connect to DB
2) prepare statement
3) bind parameters (if the statement has parameters)
4) step until SQLITE_DONE (or you have all the answers you want)
5) reset statement (if you did not step to completion)
6) finalize statement
7) disconnect

Note that there is no BEGIN and no COMMIT; SQLite implicitly does a BEGIN on the first step and a COMMIT when SQLITE_DONE is returned or the statement is reset. In a request that needs to execute more than one statement while seeing a consistent state, execute BEGIN before the first step and COMMIT after the last reset.

Larry suggested doing steps 2-6 within the work thread. That would allow the "return connection to pool" function to check for and finalize any statements that an erroneous work thread may have left open, which would result in maximum safety.

Preparing commonly used statements during pool initialization would require keeping the statement handles together with the connection handle as an element of the pool. The "return to pool" function would then reset "known statements" (an extra reset does not do any harm) and finalize unknown statements, implicitly ending any transaction.

(9) By anonymous on 2022-01-11 18:07:38 in reply to 6 [link] [source]

A prepared statement is associated with the connection that was used to create it (the 'db' argument of the sqlite3_prepare functions).

When you pass a connection to another thread you should also pass to the same thread the set of the preapared statements associated with that connection.

(10) By anonymous on 2022-01-12 06:10:49 in reply to 8 [source]

Thanks for explain.

I bind statement into connection object, also make sure all statement reseted before quit lexical scope.

Transaction always execute in one thread, and in one lexical scope.

One more question related to this design:

The task is initiated by main event loop, pass to worker thread by message. after work done notify main event loop thread by message. (check libuv worker docuement)

My question is from the worker thread, is it safe to read/write main event loop task object direct ? (the task object will never access by event loop thread before worker done message)