How threadsafe is SQLite3?
The current SQLite (version 3.23.1) documentation states that the default threading mode is "serialized", and that in this mode:
SQLite can be safely used by multiple threads with no restriction.
Thread safety is controlled at compile-time. You can query the threading mode of your SQLite distribution using:
pragma COMPILE_OPTIONS
The documentation (which is written by the people who created this software and lived, breathed, and thought about it for many years) tells you not to share database connections. Why don't you simply believe them? It's not as if it is actually very hard to create multiple database connections.
Yes, it's true. The worst thing that can happen is undefined behaviour, which may or may not include the summoning of black holes, naked singularities, and Cthulhu.
You got lucky. Possibly all hundred inserts in your test already executed before the next thread even started.
You assume correctly. You can avoid the failure by testing for it, and repeating the SQL command if you experience it. Setting a high timeout will not help.
Sqlite is not a particularly good match for any problem that has multithreaded (or multi-process) access to a database, and the "database is locked" message is, in my experience, pretty much unavoidable in such a scenario. You can get better performance by accessing the database from just a single thread, and combining inserts into a single statement (i.e. use a multivalued insert). If that is not enough, hammering a sqlite database with multiple threads will not help performance at all. If you have a use case where such an approach seems necessary, you should consider installing a database built for that purpose, like PostgreSQL.