CODEWITHSUNDEEP
multithreadingsqlitethread-safety
user26404
user26404

Reputation: 1391

Thread-safety and concurrent modification of a table in SQLite3

Does thread-safety of SQLite3 mean different threads can modify the same table of a database concurrently?

Upvotes: 4

Views: 3561

Answers (4)

Przemysław Michalski
Przemysław Michalski

Reputation: 9857

You can use SQLite in 3 different modes:

http://www.sqlite.org/threadsafe.html

If you decide to multi-thread mode or serialized mode, you can easy use SQLite in multi-thread application. In those situations you can read from all your threads simultaneously anyway. If you need to write simultaneously, the opened table will be lock automatycally for current writing thread and unlock after that (next thread will be waiting (mutex) for his turn until the table will be unlocked). In all those cases, you need to create separate connection string for every thread (.NET Data.Sqlite.dll). If you're using other implementation (e.g. any Android wrapper) sometimes the things are different.

Upvotes: 1

Liron Levi
Liron Levi

Reputation:

No - SQLite does not support concurrent write access to the same database file. SQLite will simply block one of the transactions until the other one has finished.

Upvotes: 6

Jeremiah Rose
Jeremiah Rose

Reputation: 4122

note that if you're using python, to access a sqlite3 connection from different threads you need to disable the check_same_thread argument, e.g:

sqlite.connect(":memory:", check_same_thread = False)

as of the 24th of may 2010, the docs omit this option. the omission is listed as a bug here

Upvotes: 4

Mikeage
Mikeage

Reputation: 6564

Not necessarily. If sqlite3 is compiled with the thread safe macro (check via the 

int sqlite3_threadsafe(void)
function), then you can try to access the same DB from multiple threads without the risk of corruption. Depending on the lock(s) required, however, you may or may not be able to actually modify data (I don't believe sqlite3 supports row locking, which means that to write, you'll need to get a table lock). However, you can try; if one threads blocks, then it will automatically write as soon as the other thread finishes with the DB.

Upvotes: 1

Related Questions