SQLite Concurrency with Python?

Question

Suppose that I have a huge SQLite file (say, 500[MB]). Can 10 different python instances access this file at the same time and update different records of it?. Note, the emphasis here is on different records.

For example, suppose that the SQLite file has say 1M rows:

instance 1 will deal with (and update) rows 0 - 100000

instance 2 will will deal with (and update) rows 100001 - 200000

.........................

instance 10 will deal with (and update) rows 900001 - 1000000

Meaning, each python instance will only be updating a unique subset of the file. Will this work, or will I have serious integrity issues?

pabdulin · Accepted Answer · 2011-06-10 04:43:20Z

4

Updated, thanks to André Caron.

You can do that, but only read operations supports concurrency in SQLite, since entire database is locked on any write operation. SQLite engine will return SQLITE_BUSY status in this situation (if it exceeds default timeout for access). Also consider that this heavily depends on how good file locking is implemented for given OS and file system. In general I wouldn't recommend to use proposed solution, especially considering that DB file is quite large, but you can try.

It will be better to use server process based database (MySQL, PostgreSQL, etc.) to implement desired app behaviour.

edited Jun 10, 2011 at 4:43

answered Jun 10, 2011 at 4:13

pabdulin

35.6k9 gold badges66 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

André Caron Over a year ago

The SQLite FAQ clearly explains that SQLite uses reader/writer locks, allowing concurrent access. Writes are serialized, but concurrency with write access is supported.

pabdulin Over a year ago

I agree, however, in general, concurrent write access to SQLite db is not very safe (depends how good is file locking is implemented for given OS and FS).

user234932 Over a year ago

"entire database is locked on any write operation" is usually not for very long. Especially with journaling enabled, it's tens of milliseconds at most while the journal is flushed, and even then, reads should still succeed.

André Caron · Accepted Answer · 2011-06-10 04:21:00Z

4

Somewhat. Only one instance can write at any single time, which means concurrent writes will block (refer to the SQLite FAQ). You won't get integrity issues, but I'm not sure you'll really benefit from concurrency, although that depends on how much you write versus how much you read.

answered Jun 10, 2011 at 4:21

André Caron

45.5k12 gold badges72 silver badges125 bronze badges

Collectives™ on Stack Overflow

SQLite Concurrency with Python?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related