How to use SQLite in a multi-threaded application?

by

Some steps when starting out with SQLlite for multithreaded use:

  1. Make sure sqlite is compiled with the multi threaded flag.
  2. You must call open on your sqlite file to create a connection on each thread, don’t share connections between threads.
  3. SQLite has a very conservative threading model, when you do a write operation, which includes opening transactions that are about to do an INSERT/UPDATE/DELETE, other threads will be blocked until this operation completes.
  4. If you don’t use a transaction, then transactions are implicit, so if you start a INSERT/DELETE/UPDATE, sqlite will try to acquire an exclusive lock, and complete the operation before releasing it.
  5. If you do a BEGIN EXCLUSIVE statement, it will acquire an exclusive lock before doing operations in that transaction. A COMMIT or ROLLBACK will release the lock.
  6. Your sqlite3_step, sqlite3_prepare and some other calls may return SQLITE_BUSY or SQLITE_LOCKED. SQLITE_BUSY usually means that sqlite needs to acquire the lock. The biggest difference between the two return values:
    • SQLITE_LOCKED: if you get this from a sqlite3_step statement, you MUST call sqlite3_reset on the statement handle. You should only get this on the first call to sqlite3_step, so once reset is called you can actually “retry” your sqlite3_step call. On other operations, it’s the same as SQLITE_BUSY
    • SQLITE_BUSY : There is no need to call sqlite3_reset, just retry your operation after waiting a bit for the lock to be released.

More Related Contents:

  1. Why is volatile not considered useful in multithreaded C or C++ programming?
  2. Simple example of threading in C++
  3. Concurrency: Atomic and volatile in C++11 memory model
  4. Programmatically find the number of cores on a machine
  5. How can I implement ABA counter with c++11 CAS?
  6. Using stdlib’s rand() from multiple threads
  7. Lock-free Progress Guarantees in a circular buffer queue
  8. Are C++ Reads and Writes of an int Atomic?
  9. When should I use _mm_sfence _mm_lfence and _mm_mfence
  10. C++11 thread-safe queue
  11. How to efficiently display OpenCV video in Qt?
  12. What’s the difference between “static” and “dynamic” schedule in OpenMP?
  13. Faster bulk inserts in sqlite3?
  14. Precise thread sleep needed. Max 1ms error
  15. Which std::async implementations use thread pools?
  16. pthreads: thread starvation caused by quick re-locking
  17. Get the status of a std::future
  18. How to name a thread in Linux? [duplicate]
  19. Is it ok to read a shared boolean flag without locking it when another thread may set it (at most once)?
  20. Boost::asio – how to interrupt a blocked tcp server thread?
  21. Why does Windows 10 start extra threads in my program?
  22. Can I use std::async without waiting for the future limitation?
  23. C++ terminate called without an active exception
  24. How to estimate the thread context switching overhead?
  25. C++ – std::thread crashes upon execution
  26. Error: C2228: left of ” must have class/struct/union
  27. Why there are three unexpected worker threads when a Win32 console application starts up? [duplicate]
  28. is winsock2 thread safe?
  29. Is a memory barrier required to read a value that is atomically modified?
  30. C++11 Implementation of Spinlock using header “

Leave a Comment