1

If I have multiple database readers, which are basically instances of Sqlite that open the db file in "read" mode, is it normal that for a single writer that opens the db in "create" mode to not be able to release a transaction until all readers close the database?

I'm trying to figure out if it's normal behaviour of sqlite, or it's bug/limitation of a specific sqlite library I'm using.

I don't understand why would a writer need to wait for readers to stop using the database file, doesn't make sense to me, because readers cannot alter data

Alex
  • 181
  • 3
  • 11

3 Answers3

1

No. It is not normal that your writer cannot release a transaction until all readers exit database.

This begs a question: just how you open the database file?

The sqlite engine does have ability to open database in readonly mode: https://www.sqlite.org/c3ref/open.html

sqlite3 *db;
sqlite3_open_v2("mydb.dat", &db, SQLITE_OPEN_READONLY, NULL);

The default flags (third parameter) however are SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE.

If you have one application opening file in normal SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE. And multiple applications opening in SQLITE_OPEN_READONLY mode, then writer should not wait for readers. It wont even know about them.

If you can, try doing your queries with sqlite3 console tool. It has a --readonly option, which will really open database file in readonly mode.

However, if you are working not in C, but in some language binding - it could be, that the file is always opened normally and then switched to read-only mode in the binding level.

From a formal point of view, the select statement can put a lock to a table schema, so one user cannot do alter table while another user is selecting from it. But it is the only real lock the reader can put on a table.

The only lock the sqlite does in read-only mode is to prevent database file to be deleted.

It can also be good to read about shared cache mode: https://www.sqlite.org/sharedcache.html Which can be done in some cases, and it also has ability to interfere with transactions if they are based on the same cache.

White Owl
  • 1,029
  • 3
  • 9
0

I don't understand why would a writer need to wait for readers to stop using the database file

Because every SQLite transaction--implicit or explicit--locks the database file.

No reads or writes occur except within a transaction. Transactions

You can test this by running SQLite3 in two different terminal sessions connecting to the same SQLite database file. Exact behavior depends on several details, especially "Write-Ahead Logging" (WAL) option. Again, see Transactions

Writers don't "release" transactions; they either commit or rollback. If a writer tries to commit an insert statement, for example, and another process has a select transaction open--that is, it's "reading the database"--the writer gets this error on my machine.

[snip]
sqlite> commit;
Error: stepping, database is locked (5)
Mike Sherrill 'Cat Recall'
  • 11,017
  • 1
  • 38
  • 45
0

In addition to what Mike said, regarding your thoughts on:

I don't understand why would a writer need to wait for readers to stop using the database file, doesn't make sense to me, because readers cannot alter data

Sure, but writers do alter the database, and if a reader is in the middle of reading data and a writer changes some of that data, unexpected results would be possible for the readers.

For example, if the read query is in the middle of scanning every row in an index, it would make sense for a writer who wants to update a key value that would change the position of a row in that index to have to wait until the reader is done scanning the index. Otherwise it's possible for the reader to re-read the same data row twice returning inaccurate results. This would be a known as a non-repeatable read.

Another problem that can occur is a dirty ready, where the reader hasn't finished scanning the index yet, a writer makes a change to a row, the reader then reads that row, and the writer's transaction gets rolled back (undone / not committed), resulting in data being read by the reader that never got saved to the database, causing inaccuracies.

These very simplified examples are some of the reasons why readers usually block writers besides writers also blocking readers. There is a concept of isolation levels and optimistic concurrency that allow for writers and readers to not block each other when concurrently operating on the same data. But the ability to use such features vary in implementation from each database system, and I'm not sure if SQLite even offers a feature for this.

J.D.
  • 40,776
  • 12
  • 62
  • 141