Documentation Source Text

Check-in [9466a94ec8]
Login

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

Overview
Comment:Add a section to the WAL documentation that describes some of the scenarios in which a query can get SQLITE_BUSY.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 9466a94ec8ec47b5aefa47a3008586b48efa3416
User & Date: drh 2016-09-13 20:28:40.984
Context
2016-09-13
20:39
Enable a table-of-contents on the WAL documentation. Fix typos in the previous check-in. (check-in: e1960990d2 user: drh tags: trunk)
20:28
Add a section to the WAL documentation that describes some of the scenarios in which a query can get SQLITE_BUSY. (check-in: 9466a94ec8 user: drh tags: trunk)
01:12
Use <codeblock> in more places, instead of <blockquote><pre>. Improved CSS for codeblock and for syntax diagrams. (check-in: 14e4769852 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/wal.in.
9
10
11
12
13
14
15

16
17
18
19
20
21
22
[atomic commit | atomic commit and rollback] is a [rollback journal].
Beginning with [version 3.7.0], a new "Write-Ahead Log" option
(hereafter referred to as "WAL") is available.</p>

<p>There are advantages and disadvantages to using WAL instead of
a rollback journal.  Advantages include:</p>


<ol>
<li>WAL is significantly faster in most scenarios.
<li>WAL provides more concurrency as readers do not block writers and 
    a writer does not block readers.  Reading and writing can proceed 
    concurrently.
<li>Disk I/O operations tends to be more sequential using WAL.
<li>WAL uses many fewer fsync() operations and is thus less vulnerable to







>







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[atomic commit | atomic commit and rollback] is a [rollback journal].
Beginning with [version 3.7.0], a new "Write-Ahead Log" option
(hereafter referred to as "WAL") is available.</p>

<p>There are advantages and disadvantages to using WAL instead of
a rollback journal.  Advantages include:</p>

<tcl>hd_fragment advantages {advantages of WAL-mode}</tcl>
<ol>
<li>WAL is significantly faster in most scenarios.
<li>WAL provides more concurrency as readers do not block writers and 
    a writer does not block readers.  Reading and writing can proceed 
    concurrently.
<li>Disk I/O operations tends to be more sequential using WAL.
<li>WAL uses many fewer fsync() operations and is thus less vulnerable to
498
499
500
501
502
503
504





































505
506
507
508
509
510
511
open a database that is already in WAL mode, or the attempt convert a
database into WAL mode, will fail.
^As long as exactly one connection is using a shared-memory wal-index, 
the locking mode can be changed freely between NORMAL and EXCLUSIVE.  
^It is only when the shared-memory wal-index is omitted, when the locking 
mode is EXCLUSIVE prior to the first WAL-mode database access, that the 
locking mode is stuck in EXCLUSIVE.</p>






































<tcl>hd_fragment bkwrds {WAL backwards compatibility}</tcl>
<h1>Backwards Compatibility</h1>

<p>The database file format is unchanged for WAL mode.  However, the
WAL file and the [wal-index] are new concepts and so older versions of 
SQLite will not know







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
open a database that is already in WAL mode, or the attempt convert a
database into WAL mode, will fail.
^As long as exactly one connection is using a shared-memory wal-index, 
the locking mode can be changed freely between NORMAL and EXCLUSIVE.  
^It is only when the shared-memory wal-index is omitted, when the locking 
mode is EXCLUSIVE prior to the first WAL-mode database access, that the 
locking mode is stuck in EXCLUSIVE.</p>

<tcl>hd_fragment busy {WAL-mode read blocking}</tcl>
<h1>Sometimes Queries Return [SQLITE_BUSY] In WAL Mode</h1>

<p>The [advantages of WAL-mode|second advantage of WAL-mode] is that
writers do not block readers and readers to do not block writers.
This is <u>mostly</u> true.
But there are some obscure cases where a query against a WAL-mode
database can return [SQLITE_BUSY], so applications should be prepared
for that happenstance.

<p>Cases where a query against a WAL-mode database returns [SQLITE_BUSY]
include the following:

<ul>
<li><p>Another database connection has the database mode open
in [locking_mode|exclusive locking mode] then all queries against the
database will return [SQLITE_BUSY].  Both Chrome and Firefox open their
database files in exclusive locking mode, so attempts to read Chrome or
Firefox databases while the applications are running will run into this
problem, for example.

<li><p>
When the last connection to a particular database is closing, that
connection will acquire an exclusive lock for a short time while it
cleans up the WAL and shared-memory files.  If another database tries
to open and query the database while the first one is still in the middle
of its cleanup process, the second connection might get an [SQLITE_BUSY]
error.

<li><p>
If the last connection to a database crashed, then the first new
connection to open the database will start a recovery process.  An
exclusive lock is held during recovery.  So if a third database connection
tries to jump in and query while the second connection is running recovery,
the third connection will get an [SQLITE_BUSY] error.
</ul>

<tcl>hd_fragment bkwrds {WAL backwards compatibility}</tcl>
<h1>Backwards Compatibility</h1>

<p>The database file format is unchanged for WAL mode.  However, the
WAL file and the [wal-index] are new concepts and so older versions of 
SQLite will not know