is concurrency automatically handled in sqlite?

Question

On my server, in order to speed up things, I have allocated a connection pool to my sqlite odbc source.

What happens if two or more hosts want to alter my data? Are these multiple connections automatically handled by the sqllite?

Read https://sqlite.org/whentouse.html if you plan to use SQLite as the backend for a server. — , Feb 20 '14 at 20:57
sqlite is single threaded for writes; your threads will be blocked by internal locking and writes will be performed serially. — Brian Roach, Feb 20 '14 at 21:01
Dup http://stackoverflow.com/questions/14217249/sqlite-and-concurrency? — lreeder, Feb 20 '14 at 21:02

Bosko Mijin · Answer 1 · 2014-02-20T21:14:17.930

2

In short: It is not good solution.

Description:

SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time.

For your situation it is not good.

Advice: Use another RDBMS.

edited Feb 20 '14 at 21:14

answered Feb 20 '14 at 21:01

Bosko Mijin

3,287
3
32
45

In short: Because you have no idea what the OP wants to do, this is patently false. There are plenty of use cases where it is perfectly fine to use SQLite on the backend of a server. You just have to understand the tradeoffs and performance implications of doing so. Do you understand how a RDBMS handles multiple writers to a table? And reads while writing? – Brian Roach Feb 20 '14 at 21:03

score 2 · Accepted Answer · edited May 23 '17 at 10:28

You can read this thread

If most of those concurrent accesses are reads (e.g. SELECT), SQLite can handle them very well. But if you start writing concurrently, lock contention could become an issue. A lot would then depend on how fast your filesystem is, since the SQLite engine itself is extremely fast and has many clever optimizations to minimize contention. Especially SQLite 3.

For most desktop/laptop/tablet/phone applications, SQLite is fast enough as there's not enough concurrency. (Firefox uses SQLite extensively for bookmarks, history, etc.)

For server applications, somebody some time ago said that anything less than 100K page views a day could be handled perfectly by a SQLite database in typical scenarios (e.g. blogs, forums), and I have yet to see any evidence to the contrary. In fact, with modern disks and processors, 95% of web sites and web services would work just fine with SQLite.

If you want really fast read/write access, use an in-memory SQLite database. RAM is several orders of magnitude faster than disk.

And check this

is concurrency automatically handled in sqlite?

2 Answers2