2

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?

Sim
  • 4,199
  • 4
  • 39
  • 77

1 Answers1

4

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.

Rptx
  • 1,159
  • 1
  • 12
  • 17
Renzo
  • 26,848
  • 5
  • 49
  • 61
  • I was afraid this is the answer. Though I am still baffled why clsql does not account for this and ensures thread safety as I'd expect from `with-database`. – Sim Jul 09 '15 at 21:46
  • 2
    I think the reason is that clsql has been written to give a "unified" interfaces for all "normal" relational DBMS, and it does a good work, but SQLite is a very "special" database, since it is not a "normal" client-server database system, but is a *library* to access a file as if it were a relational database. It does a very good job for efficient access to data with SQL, but in practice it does not have any real concurrency (it uses the Operating Systems functions to lock the db file, for instance). So it is not a "real" DBMS, and clsql cannot do anything about it. – Renzo Jul 09 '15 at 21:55
  • can you incorporate this comment into your answer so it will not be lost for later reader? – Sim Jul 10 '15 at 08:38