Documentation Source Text

Check-in [ba027764e3]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Update information on read-only WAL-mode databases to conform with the new capabilities added in version 3.22.0.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: ba027764e3bcda86fd4911b65bfa8762bde54d98d977d3caf90275de7ed21ab8
User & Date: drh 2018-05-22 15:28:58
Context
2018-05-22
15:31
Read-only WAL mode in 3.22, not 3.20. check-in: f5137e463c user: drh tags: trunk
15:28
Update information on read-only WAL-mode databases to conform with the new capabilities added in version 3.22.0. check-in: ba027764e3 user: drh tags: trunk
2018-05-19
14:21
Updates to the TH3 documentation. check-in: ac3ef1f6aa user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/wal.in.

38
39
40
41
42
43
44
45
46
47
48
49



50
51
52
53
54
55
56
...
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
<li>Transactions that involve changes against multiple [ATTACH | ATTACHed]
    databases are atomic for each individual database, but are not
    atomic across all databases as a set.
<li>It is not possible to change the [page_size] after entering WAL
    mode, either on an empty database or by using [VACUUM] or by restoring
    from a backup using the [backup API].  You must be in a rollback journal
    mode to change the page size.
<li>It is not possible to open [read-only WAL databases].
    The opening process must have write privileges for "<tt>-shm</tt>"
    [wal-index] shared memory file associated with the database, if that
    file exists, or else write access on the directory containing
    the database file if the "<tt>-shm</tt>" file does not exist.



<li>WAL might be very slightly slower (perhaps 1% or 2% slower)
    than the traditional rollback-journal approach
    in applications that do mostly reads and seldom write.
<li>There is an additional quasi-persistent "<tt>-wal</tt>" file and
    "<tt>-shm</tt>" shared memory file associated with each
    database, which can make SQLite less appealing for use as an 
    [application file-format].
................................................................................
<p>The WAL journal mode will be set on all
connections to the same database file if it is set on any one connection.
</p>

<tcl>hd_fragment {readonly} {read-only WAL databases}</tcl>
<h1>Read-Only Databases</h1>

<p>No SQLite database (regardless of whether or not it is WAL mode) is
readable if it is located on read-only media and it requires recovery.
So, for example, if an application crashes and leaves an SQLite database
with a [hot journal], that database cannot be opened unless the opening
process has write privilege on the database file, the directory
containing the database file, and the hot journal.  This is because the 
incomplete transaction left over from the crash must be rolled back prior 
to reading the database and that rollback cannot occur without write 
permission on all files and the directory containing them.</p>

<p>A database in WAL mode cannot generally be opened from read-only 
media because even ordinary reads in WAL mode require recovery-like
operations.</p>

<p>An efficient implementation of the [WAL read algorithm] requires that
there exist a hash table in shared memory over the content of the WAL file.
This hash table is called the [wal-index].
The wal-index is in shared memory, and so technically it does not have 
to have a name in the host computer filesystem.  Custom
[VFS] implementations are free to implement shared 
memory in any way they see fit, but the default unix and windows 
drivers that come built-in with SQLite implement shared memory
using <a href="http://en.wikipedia.org/wiki/Mmap">mmapped files</a>
named using the suffix "<tt>-shm</tt>" and
located in the same directory as the database file.  The wal-index must
be rebuilt upon first access, even by readers, and so in order to open
the WAL database, write access is required on the "<tt>-shm</tt>" shared
memory file if the file exists, or else write access is required on the
directory containing the database so that the wal-index can be created if
it does not already exist.
This does not preclude custom VFS implementations that implement shared 
memory differently from being able to access read-only WAL databases, but
it does prevent the default unix and windows backends from accessing
WAL databases on read-only media.</p>

<p>Hence, SQLite databases should always be converted to 
[journal_mode | PRAGMA journal_mode=DELETE] prior to being transferred
to read-only media.</p>

<p>Also, if multiple processes are to access a WAL mode database, then
all processes should run under user or group IDs that give them write
access to the database files, the WAL file, the shared memory 
<tt>-shm</tt> file, and the containing directory.</p>

<tcl>hd_fragment bigwal {large WAL files} {avoiding large WAL files}</tcl>
<h1>Avoiding Excessively Large WAL Files</h1>

<p>In normal cases, new content is appended to the WAL file until the
WAL file accumulates about 1000 pages (and is thus about 4MB 
in size) at which point a checkpoint is automatically run and the WAL file







|



|
>
>
>







 







|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
...
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343






















344
345
346
347
348
349
350
<li>Transactions that involve changes against multiple [ATTACH | ATTACHed]
    databases are atomic for each individual database, but are not
    atomic across all databases as a set.
<li>It is not possible to change the [page_size] after entering WAL
    mode, either on an empty database or by using [VACUUM] or by restoring
    from a backup using the [backup API].  You must be in a rollback journal
    mode to change the page size.
<li><s>It is not possible to open [read-only WAL databases].
    The opening process must have write privileges for "<tt>-shm</tt>"
    [wal-index] shared memory file associated with the database, if that
    file exists, or else write access on the directory containing
    the database file if the "<tt>-shm</tt>" file does not exist.</s>
    Beginning with [version 3.22.0] ([dateof:3.22.0]) one can open a
    WAL mode database read-only as long as either the <tt>-shm</tt> file
    already exists or the <tt>-shm</tt> file can be created.
<li>WAL might be very slightly slower (perhaps 1% or 2% slower)
    than the traditional rollback-journal approach
    in applications that do mostly reads and seldom write.
<li>There is an additional quasi-persistent "<tt>-wal</tt>" file and
    "<tt>-shm</tt>" shared memory file associated with each
    database, which can make SQLite less appealing for use as an 
    [application file-format].
................................................................................
<p>The WAL journal mode will be set on all
connections to the same database file if it is set on any one connection.
</p>

<tcl>hd_fragment {readonly} {read-only WAL databases}</tcl>
<h1>Read-Only Databases</h1>

<p>Older versions of SQLite could not read a WAL-mode database that was
read-only.  In other words, write access was required in order ot read a
WAL-mode database.  This constraint was relaxed beginning with
SQLite [version 3.20.0] ([dateof:3.20.0]).

<p>On newer versions of SQLite,
a WAL-mode database on read-only media, or a WAL-mode database that lacks
write permission, can still be read as long as one or more of the following
conditions are met:
<ol>
<li>The <tt>-shm</tt> and <tt>-wal</tt> files already exists and are readable
<li>There is write permission on the directory containing the database so
    that the <tt>-shm</tt> and <tt>-wal</tt> files can be created.
<li>The database connection is opened using the
    [immutable query parameter].
</ol>

<p>Even though it is possible to open a read-only WAL-mode database,
it is good practice is to converted to 
[journal_mode | PRAGMA journal_mode=DELETE] prior to burning an
SQLite database image onto read-only media.</p>























<tcl>hd_fragment bigwal {large WAL files} {avoiding large WAL files}</tcl>
<h1>Avoiding Excessively Large WAL Files</h1>

<p>In normal cases, new content is appended to the WAL file until the
WAL file accumulates about 1000 pages (and is thus about 4MB 
in size) at which point a checkpoint is automatically run and the WAL file