2

I am using Python with SQLite currently and wondering if it is safe to have multiple threads reading and writing to the database simultaneously. Does SQLite handle data coming in as a queue or have sort of mechanism that will stop the data from getting corrupt?

Sam
  • 457
  • 2
  • 6
  • 21
  • 1
    Don't tag questions with things like MySQL when they're not relevant. – tadman Aug 26 '16 at 05:07
  • Possible duplicate of [Read and Write Sqlite database data concurrently from multiple connections](http://stackoverflow.com/questions/10325683/read-and-write-sqlite-database-data-concurrently-from-multiple-connections) – Thilo Aug 26 '16 at 05:09
  • @tadman Tag Removed. – Sam Aug 26 '16 at 05:28
  • 1
    This is a [FAQ](https://www.sqlite.org/faq.html#q5). – CL. Aug 26 '16 at 06:51

2 Answers2

3

This is my issue too. SQLite using some kind of locking mechanism which prevent you doing concurrency operation on a DB. But here is a trick which i use when my db are small. You can select all your tables data into memory and operate on it and then update the original table.

As i said this is just a trick and it does not always solve the problem.

I advise to create your trick.

Reza Tanzifi
  • 572
  • 4
  • 13
2

SQLite has a number of robust locking mechanisms to ensure the data doesn't get corrupted, but the problem with that is if you have a number of threads reading and writing to it simultaneously you'll suffer pretty badly in terms of performance as they all trip over the others. It's not intended to be used this way, even if it does work.

You probably want to look at using a shared database server of some sort if this is your intended usage pattern. They have much better support for concurrent operations.

tadman
  • 208,517
  • 23
  • 234
  • 262
  • What kind of shared database servers could you suggest? – Sam Aug 26 '16 at 05:29
  • 2
    Postgres, MySQL/MariaDB, MongoDB are the three most popular. – tadman Aug 26 '16 at 06:10
  • I found your answer useful, however- if I have 2 concurrent writes, then would it corrupt the data or just intimate the user that db is locked or worse? – Ganesh Jadhav Oct 21 '19 at 13:37
  • 1
    @GaneshJadhav SQLite is really great about data integrity, but it veers towards being paranoid. If two process try and write at the same time, one must finish before the other can start. You won't get corruption, but you will get locked. Under heavy loads this can be a problem and you should switch to a database that allows concurrent writes, like Postgres. – tadman Oct 21 '19 at 19:18