SQLite Forum

To read file simultaneously

To read file simultaneously

(1) By anonymous on 2021-02-09 13:13:34 [source]

Hello I am Trying to write from one connection with WAL mode and reading from multiple Connection and reading is network based while writing to file and at same time reading file shows me DataBase locked error everytime

(2) By Richard Hipp (drh) on 2021-02-09 14:08:25 in reply to 1 [link] [source]

Network filesystems do not support the ability to do simultaneous reads and writes while at the same time keeping the database consistent. So if you have multiple clients on multiple different machines wanting to do simultaneous database reads and writes, you have these choices:

  1. Use a client/server database engine. PostgreSQL is an excellent choice.

  2. Use SQLite in rollback mode. This means you can have multiple simultaneous readers or one writer, but not simultaneous readers and writers.

  3. Host an SQLite database in WAL mode, but do all reads and writes from processes on the same machine that stores the database file. Implement a proxy that run on the database machine that relays read/write requests from remote machines.

Generally speaking, if your data is separated from the application by a network, you want to use a client/server database. This is due to the fact that the database engine as a bandwidth-reducing filter on the database traffic.

Your Application Low-Bandwidth Link SQL Database Engine High-Bandwidth Link Database File(s)
box "Your" "Application"
arrow <-> "Low-Bandwidth" "Link" width 250%
box "SQL" "Database" "Engine"
arrow <-> "High-Bandwidth" "Link" width 250%
cylinder "Database" "File(s)"

If your data is separated from the application by a network, you want the low-bandwidth link to be across the network, not the high-bandwidth link. This means that the database engine needs to be on the same machine as the database itself. Such is the case with a client/server database like PostgreSQL. SQLite is different in that the database engine lives on the same machine as the application, which forces the high-bandwidth link to traverse the network. That normally results in lower performance.

Choose the technology that is right for you. If your data lives on a different machine from your application, then you should consider a client/server database. SQLite is designed for situations where the data and the application coexist on the same machine. SQLite will still work if the data is remote, but a client/server solution will usually work better in that scenario.

(3) By Gunter Hick (gunter_hick) on 2021-02-09 14:09:31 in reply to 1 [link] [source]

When you say "reading is network based" does that mean that the SQLite database file resides on a different computer and the reader is using a network file system?

That would not be supported.

(4) By Larry Brasfield (larrybr) on 2021-02-09 14:18:25 in reply to 1 [link] [source]

The use of SQLite for a remote database file strongly discouraged. If, as you seem to say, only reads are done over the network, "read" connections are opened as read-only, and writes are always local, you probably won't corrupt the database file. The odds are much worse with respect to the remote readers seeing a coherent set of on-"disk" structures; the ACID guarantees that pertain to purely local connections may not be met in your scenario.

People wanting to achieve a similar effect as you intend have created wrappers over the SQLite library or other combinations which undertake to leverage the library and overcome the (legion) limitations of network filesystems. A web search on "distributed sqlite database" (with the quotes) will turn up many such systems. Unless you do not care about what results the readers obtain, using one of those would be safer.

There are also some wrappers which expose a set of local SQLite database connections over a network, in effect making a client/server arrangement. This also can be done with high assurance that, should the remote results arrive, they are correct. Similarly, they provide high assurance that, should remote modifications get to the server, they do so with ACID still in effect.