What is the difference between aiosqlite and SQLite in multi-threaded mode?

First of all about threads:

Sqlite ... can be used by multiple threads at one time

It will still be not the same time because of GIL, Threads are always running concurrently (not in parallel). The only thing that with GIL you don't know when thread will be interrupted. But asyncio allows you to switch between threads "manually" and on waiting for some IO operations (like database communication).

Let me explain differences between different modes:

• Single-thread - creates single database connection without any mutexes or any other mechanisms to prevent multi-threading issues.
• Multi-thread - creates single shared database connection with mutexes that locks that connection for each operation/communication with database.
• Serialized - creates multiple database connections per thread.

Answering questions in update:

1. Yes

   Sqlite in "serialized" mode can be used by multiple threads at one time, so this would be used if I used the threading module in python and spawned multiple threads. Here I have the options of either using a separate connection per thread or sharing the connection across multiple threads.

2. Yes, it will share a single connection between them.

   aiosqlite is used with asyncio. So since asyncio has multiple coroutines that share one thread, aiosqlite also works with one thread. So I create one connection that I share among all the coroutines

3. Yes.

   Since aiosqlite is basically a wrapper for sqlite, I can combine the functionality of 1 and 2. So I can have multiple threads where each thread has an asyncio event loop with multiple coroutines. So the basic sqlite functionality will handle the multi-threading and the aiosqlite will handle the coroutines.