multithreadingcommon-lispclsql

SQLITE3 CLSQL multithreaded insert results in error


I want to use my sqlite3 database with multiple threads in parallel. I read that using connection pools makes the access threadsafe but I still get errors while inserting data.

(make-thread
   #'(lambda()
       (dotimes (i 100)
          (with-database (db ("/path/to/db") 
                         :database-type :sqlite3 :pool T)
            (do-stuff-with db)))))

When using multiple threads in this fashion in this error

While accessing database # with expression "INSERT INTO ...": Error 5 / database is locked

Is it even possible to do a multi threaded insert with an sqlite3 database? If yes how?


Solution

  • SQLite does not support concurrency of multiple write transactions. From the SQlite site:

    SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writer queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.

    Cl-sql has been written to give a "unified" interface for the typical client-server relational DBMS, like other "standardized" libraries (e.g. JDBC or ODBC), but SQLite is an "untypical" database management system: in practice it is a library that offers SQL as language to access a simple "database-in-a-file", and a few other functionalities of DBMSs. For instance, it has no real concurrency control (it uses the Operating Systems functions to lock the db file), so it cannot be considered a "real" DBMS, and cl-sql cannot offer nothing more than the functionalities of the underlying system.

    So, if you need concurrent insertions into a database, you should use something else, for instance PostgreSQL.