Multithreading Documentation

[Nikratio]

https://rogerbinns.github.io/apsw/execution.html#multi-threading-and-re-entrancy says:

Note that you cannot use the same cursor object in multiple threads concurrently to execute statements.

which seems to imply that using separate cursors obtained from the same connection concurrently in multiple threads is fine. However, https://www.sqlite.org/threadsafe.html refers to multiple potential threading modes. Should the APSW documentation perhaps mention that? Or is APSW ensuring internally that SQLite is used in serialized mode?

[rogerbinns]

The implication is correct. You can certainly try to use APSW as heavily concurrently as you want and there is no way that you will cause problems with APSW or SQLite (there is even a test for this). APSW does not limit the inherent concurrency SQLite provides.

But APSW does protect from problems. Consider calling execute in one thread and close in another on the same cursor. There is no control over what order the GIL is released and reacquired so there is a race between sqlite3_step and sqlite3_finalize calls on the same sqlite3_stmt. The underlying APSW cursor object tracks whether it is in a call, and refuses to attempt this, giving a ThreadingViolationError. The same tracking exists on the Connection for the same reason.

So the quoted text is true - you cannot use the same cursor object in multiple threads concurrently to execute statements - because the call tracking will refuse it. The text can be updated to be simpler - tracked in #427.

[Nikratio]

Right, but isn't the documentation actively misleading if SQLite happens to be compiled (or put into) multi-threaded or single-threaded mode? Currently, the documentation makes it sound as if it is always safe to concurrently use multiple cursors and connections.

[rogerbinns]

It is always safe to concurrently use cursors and connections! Doing so will never result in deadlocks, memory corruption, operation on closed handles etc in APSW. But there is a list of details:

• If you try to do something that would have resulted in deadlocks, memory corruption, operation on closed handles etc then it will be detected and refused with ThreadingViolationError
• You are unlikely to get useful results - executing two different queries concurrently on the same cursor means one wins and so the other thread will see the results to the other query
• APSW refuses to initialize unless SQLite was compiled threadsafe
• APSW relies on the GIL to protect APSW's data structures, and SQLite's mutexes to protect SQLite's data structures.
• APSW releases the GIL around every sqlite3 call allowing for maximum concurrency in SQLite. (There are a small number of calls where it doesn't happen because the calls are trivial and SQLite doesn't do mutexes in them.)
• If you use sqlite3_config to put SQLite into single-thread mode (aka turn off SQLite mutexes) and then do concurrency you no longer have protection of SQLite's data structures
• The text was originally written during the days of pysqlite3 which made virtually no attempts to get concurrency right, hence being so emphatic that APSW does

[Nikratio]

"APSW refuses to initialize unless SQLite was compiled threadsafe" is the critical information I was looking for, thanks!

It would be great to add this to the multithreading doc page, together with the note that "If you use sqlite3_config to put SQLite into single-thread mode (aka turn off SQLite mutexes) and then do concurrency you no longer have protection of SQLite's data structures"

[rogerbinns]

It does not refuse sqlite3_config for single thread mode. Someone deliberately turning off SQLite mutexes and then using concurrency presumably knows what they are doing and keep the pieces of whatever they break :)

[rogerbinns]

I did a doc update. Didn't see your edit about using sqlite3_config but I don't think it is worth mentioning because it takes some determination to do that and then expect concurrency to somehow work! You can also pass flags to sqlite3_open to disable mutexes with the same consequences, which APSW also doesn't prevent. Anybody doing this presumably knows exactly what they are doing.