SQLite Forum

How db access is handled by sqlite3?
Login

How db access is handled by sqlite3?

(1) By 6kEs4Majrd on 2020-06-13 21:48:21 [link] [source]

I see that there are at least two ways to access a file, i.e., mmap() vs fread/fwrite(). Which one is used by sqlite3 when it access a database file? Thanks.

https://stackoverflow.com/questions/30385635/why-use-mmap-over-fread

(2.1) By Keith Medcalf (kmedcalf) on 2020-06-13 22:23:04 edited from 2.0 in reply to 1 [link] [source]

pragma mmap_size will tell you the current mmap_size. If the result is 0 then it is not being used. If there is no result then it cannot be used.

https://sqlite.org/pragma.html#pragma_mmap_size

When in WAL mode the WAL Index always uses mmap, unless it doesn't.

(3) By Richard Hipp (drh) on 2020-06-13 22:31:39 in reply to 1 [link] [source]

Why use read() instead of mmap()

If you are using read() and you hit an I/O error (perhaps somebody pulled the USB memory stick that you were reading out of its socket) then you will get an error code back from read(). SQLite detects this and unwinds its stack gracefully, returning a sensible error to the calling application.

In contrast, if you get an I/O error while reading with mmap(), there is no return code. Instead you get a signal. There is no way for SQLite to intercept this and recover gracefully. The application just crashes.

SQLite Can Use MMap(), Nevertheless

In spite of the problems with mmap(), SQLite does give you the option to use mmap(), on many systems. (This capability is disabled on systems where we know that the underlying OS does not have a coherent page cache.) Just set PRAGMA nnao)size=N where N is the size of the prefix of the file to be memory mapped, and the rest is automatic.

(4) By 6kEs4Majrd on 2020-06-14 15:32:20 in reply to 1 [link] [source]

https://www.man7.org/linux/man-pages/man3/shm_open.3.html

If multiple processes access the same database, does sqlite3 share some memory among these processes using something like shm_open()? Thanks.

(5) By Keith Medcalf (kmedcalf) on 2020-06-14 16:49:52 in reply to 4 [source]

For reading, yes. mmap directly maps the OS page cache into process address space using the process page table (in most implementations).

However, writing requires that the library "copy" the page being modified into process private storage for modification before being written to the database.

This should be obvious since if the changes were directly made to the mapped pages, they would be visible to other processes immediately rather than only after commit.