Limitations of this ADO.NET SQLite Data Provider

As providers go, this one doesn't have many restrictions. SQLite has no support for row-level or table-level locks. When a connection locks the database for writing, no other connection or process may read or write to the database until the write operation is complete. The SQLite.NET provider attempts to retry internally if a database is locked, up to the CommandTimeout property of the command in question.

SQLite is inherently type-less, and only understands a few basic datatypes natively. They are (in .NET-speak) Int64, Double, String and Blob. The SQLite.NET provider will use the database schema information it can glean to enforce type-ness, but it is an inexact science.

Hierarchical DataReaders are not supported. In the case of transactions, any SQLiteCommand created on a connection will (when executed) automatically join a transaction in progress, regardless of whether that transaction was created before or after the command.

A SQLiteCommand object can be re-assigned a new SQLiteConnection object as long as no DataReaders are active on the command.

Opening a transaction is considered a write operation, so only use them when you want to write to the database! If you hold open an "immediate" transaction, all readers on other connections will be blocked until the transaction is closed!

Thread Safety

Multi-threading in SQLite must be done carefully. Here are the restrictions:

Understand again that SQLite has no fine-grained locking mechanisms. It is therefore your own responsibility in a multi-threaded environment to handle potential timeouts that may occur if a long-running query in one thread prevents a query in another thread from executing. These timeouts will only occur if one thread is attempting to read while another thread is attempting to write. Whichever thread got its lock first will be the one to execute, and the other thread will block until the CommandTimeout value elapses or the other thread finishes.