Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Updates to PRAGMA locking_mode and WAL documentation. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
8ff114503512cdfb6d5e393c526609ef |
User & Date: | drh 2010-11-03 01:22:05.000 |
Context
2010-11-10
| ||
18:43 | Add some more documentation for EXPLAIN QUERY PLAN. (check-in: 37f6e9f261 user: dan tags: trunk) | |
2010-11-03
| ||
01:22 | Updates to PRAGMA locking_mode and WAL documentation. (check-in: 8ff1145035 user: drh tags: trunk) | |
2010-11-02
| ||
14:46 | Documentation of the WAL in EXCLUSIVE locking-mode changes. (check-in: 0aa0be4de5 user: drh tags: trunk) | |
Changes
Changes to pages/pragma.in.
︙ | ︙ | |||
494 495 496 497 498 499 500 | locking-mode back to NORMAL using this pragma and then accessing the database file (for read or write). ^Simply setting the locking-mode to NORMAL is not enough - locks are not be released until the next time the database file is accessed.</p> <p>There are three reasons to set the locking-mode to EXCLUSIVE. <ol> | | | | 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 | locking-mode back to NORMAL using this pragma and then accessing the database file (for read or write). ^Simply setting the locking-mode to NORMAL is not enough - locks are not be released until the next time the database file is accessed.</p> <p>There are three reasons to set the locking-mode to EXCLUSIVE. <ol> <li>^The application wants to prevent other processes from accessing the database file. <li>^The number of system calls for filesystem operations is reduced, possibly resulting in a small performance increase. <li>^[WAL] databases can be accessed in EXCLUSIVE mode without the use of shared memory. ([WAL without shared memory | Additional information]) </ol> </p> |
︙ | ︙ | |||
522 523 524 525 526 527 528 529 530 531 532 533 534 535 | <p>^The "temp" database (in which TEMP tables and indices are stored) and [in-memory databases] always uses exclusive locking mode. ^The locking mode of temp and [in-memory databases] cannot be changed. ^All other databases use the normal locking mode by default and are affected by this pragma.</p> } Pragma page_size { <p>^(<b>PRAGMA page_size; <br>PRAGMA page_size = </b><i>bytes</i><b>;</b></p> <p>Query or set the page size of the database.)^ ^The page size may only be set if the database has not yet been created. ^The page | > > > > > > > > | 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 | <p>^The "temp" database (in which TEMP tables and indices are stored) and [in-memory databases] always uses exclusive locking mode. ^The locking mode of temp and [in-memory databases] cannot be changed. ^All other databases use the normal locking mode by default and are affected by this pragma.</p> <p>^If the locking mode is EXCLUSIVE when first entering [WAL | WAL journal mode], then the locking mode cannot be changed to NORMAL until after exiting WAL journal mode. ^If the locking mode is NORMAL when first entering WAL journal mode, then the locking mode can be changed between NORMAL and EXCLUSIVE and back again at any time and without needing to exit WAL journal mode.</p> } Pragma page_size { <p>^(<b>PRAGMA page_size; <br>PRAGMA page_size = </b><i>bytes</i><b>;</b></p> <p>Query or set the page size of the database.)^ ^The page size may only be set if the database has not yet been created. ^The page |
︙ | ︙ |
Changes to pages/wal.in.
︙ | ︙ | |||
20 21 22 23 24 25 26 | <li>WAL uses many fewer fsync() operations and is thus less vulnerable to problems on systems where the fsync() system call is broken. </ol> <p>But there are also disadvantages:</p> <ol> | | | > > | 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | <li>WAL uses many fewer fsync() operations and is thus less vulnerable to problems on systems where the fsync() system call is broken. </ol> <p>But there are also disadvantages:</p> <ol> <li>WAL normally requires that the [sqlite3_vfs | VFS] supports shared-memory primitives. (Exception: [WAL without shared memory]) The built-in unix and windows VFSes support this but third-party extension VFSes for custom operating systems might not. <li>All processes using a database must be on the same host computer; WAL does not work over a network filesystem. <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. |
︙ | ︙ | |||
365 366 367 368 369 370 371 | For example, if it is known that a particular database will only be accessed by threads within a single process, the wal-index can be implemented using heap memory instead of true shared memory.</p> <tcl>hd_fragment noshm {WAL without shared memory}</tcl> <h2>Use of WAL Without Shared-Memory</h2> | | | > | | > > > > | > > | < > > | < > | > > > > > | | > > | > > | 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 | For example, if it is known that a particular database will only be accessed by threads within a single process, the wal-index can be implemented using heap memory instead of true shared memory.</p> <tcl>hd_fragment noshm {WAL without shared memory}</tcl> <h2>Use of WAL Without Shared-Memory</h2> <p>Beginning in SQLite version 3.7.4, ^WAL databases can be created, read, and written even if shared memory is unavailable as long as the [locking_mode] is set to EXCLUSIVE before the first attempted access. In other words, a process can interact with a WAL database without using shared memory if that process is guaranteed to be the only process accessing the database. ^This feature allows WAL databases to be created, read, and written by legacy [sqlite3_vfs | VFSes] that lack the "version 2" shared-memory methods xShmMap, xShmLock, xShmBarrier, and xShmUnmap on the [sqlite3_io_methods] object.</p> <p>^(If EXCLUSIVE locking mode is set prior to the first WAL-mode database access, then SQLite never attempts to call any of the shared-memory methods and hence no shared-memory wal-index is ever created.)^ ^(In that case, the database connection remains in EXCLUSIVE mode as long as the journal mode is WAL; attempts to change the locking mode using "<tt>PRAGMA locking_mode=NORMAL;</tt>" are no-ops.)^ ^The only way to change out of EXCLUSIVE locking mode is to first change out of WAL journal mode.</p> <p>^If NORMAL locking mode is in effect for the first WAL-mode database access, then the shared-memory wal-index is created. ^This means that the underlying VFS must support the "version 2" shared-memory. ^If the VFS does not support shared-memory methods, then the attempt to open a database that is already in WAL mode, or the attempt convert a database into WAL mode, will fail. ^As long a 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> <h2>Backwards Compatibility</h2> <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 how to recover a crashed SQLite database that was operating in WAL mode when the crash occurred. |
︙ | ︙ |