Documentation Source Text

Check-in [c3a0f6d745]
Login

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

Overview
Comment:Work on requirements in fileio.html.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c3a0f6d745df0a7bde5808a6d9651f8f9fce3531
User & Date: dan 2008-09-09 13:48:52
Context
2008-09-09
15:12
Updates to the malloc document. check-in: 98e50e4e24 user: drh tags: trunk
13:48
Work on requirements in fileio.html. check-in: c3a0f6d745 user: dan tags: trunk
2008-09-04
17:09
Add documentation for SQLITE_DEFAULT_MEMSTATUS to compile.html. check-in: 1445db0dec user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/fileio.in.

56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
...
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
...
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
...
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
...
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633







634
635






636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659

660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694




695
696
697
698
699
700
701
...
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
...
759
760
761
762
763
764
765




766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794

795
796
797
798
799
800
801
802


803
804












805
806


807
808
809
810
811
812
813
814
815
816

817
818
819
820
821
822





823
824
825
826
827

828
829
830
831
832
833
834
...
835
836
837
838
839
840
841
842


843
844
845
846
847
848
849
850
851
852
853
854
855
856
...
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
...
900
901
902
903
904
905
906
907

908
909
910
911




912




913
914
915
916






917
918
919
920
921
922
923
924




925
926
927
928
929
930
931
...
983
984
985
986
987
988
989






990
991
992
993
994

995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007






1008
1009
1010
1011


1012
1013
1014



1015
























































































1016
1017
1018
1019

1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038

1039
1040
1041
1042

1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
....
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
....
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
....
1109
1110
1111
1112
1113
1114
1115
1116





1117
1118
1119
1120
1121
1122
1123
....
1554
1555
1556
1557
1558
1559
1560




































1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
  <b>Javascript is required for some features of this document, including 
     table of contents, figure numbering and internal references (section
     numbers and hyper-links.
  </b>
</div>
<!-- End of standard rt docs header -->

<h1>Overview</h1>

  <p>
    SQLite stores an entire database within a single file, the format of
    which is described in the <i>SQLite File Database File Format</i> 
    document <cite>ff_sqlitert_requirements</cite>. Each database file is
    stored within a file system, presumably provided by the host operating
    system. Instead of interfacing with the operating system directly, 
................................................................................
    <li><p>For performance reasons, it is advantageous to <b>minimize the 
        quantity of data read and written</b> to and from the file-system.

        <p>As one might expect, the amount of data read from the database 
        file is minimized by caching portions of the database file in main 
        memory. Additionally, multiple updates to the database file that
        are part of the same <i>write transaction</i> may be cached in
        main memory and written to the file periodically, allowing for
        more efficient IO patterns and eliminating the redundant write 
        operations that could take place if part of the database file is
        modified more than once within a single <i>write transaction</i>.

  </ol>

  <p class=todo>
................................................................................
      It is assumed that writing a series of sequential blocks of data to 
      a file in order is faster than writing the same blocks in an arbitrary
      order.

  <h2 id=fs_characteristics>System Failure Related Assumptions</h2>
    <p>
      In the event of an operating system or power failure, the various 
      combinations of file-system and storage hardware available provide
      varying levels of guarantee as to the integrity of the data written
      to the file system just before or during the failure. The exact
      combination of IO operations that SQLite is required to perform
      in order to safely modify a database file depend on the exact 
      characteristics of the target platform.

    <p>
      This section describes the assumptions that SQLite makes about the
      the content of a file-system following a power or system failure. In
      other words, it describes the extent of file and file-system corruption
      that such an event may cause.
................................................................................
      untrustworthy as defined by A21008 and neither A21011 or A21012 
      apply to the range of bytes written, then no assumption can be
      made about the content of the sector following recovery. It is
      assumed that it is possible for such a sector to be written 
      correctly, not written at all, populated with garbage data or any
      combination thereof.

    <p class=todo>
      Fix the requirement below. The idea is to say that extending a file
      cannot cause the file size to become corrupted and thereby cause the
      whole file to be lost.

    ASSUMPTION A21009
      If a system failure occurs during or after a "write file"
      operation that causes the file to grow, but before the corresponding 
      file has been <i>synced</i>, then it is assumed that the size of 
      the file following recovery is as large or larger than it was before 
      the "write file" operation that, if successful, would cause the file 
      to grow.

    <p>
      If a system supports the <i>sequential-write</i> property, then further
      assumptions may be made with respect to the state of the file-system
      following recovery from a <i>system failure</i>. Specifically, it is
      assumed that create, truncate, delete and write file operations are
      applied to the persistent representation in the same order as they 
................................................................................
  <p>
    Within this document, the term <i>database connection</i> has a slightly
    different meaning from that which one might assume. The handles returned
    by the <code>sqlite3_open()</code> and <code>sqlite3_open16()</code>
    APIs (<span class=todo>reference</span>) are referred to as <i>database
    handles</i>.  A <i>database connection</i> is a connection to a single
    database file using a single file-handle, which is held open for the
    lifetime of the connection. Using the "ATTACH" syntax, multiple <i>database
    connections</i> may be accessed via a single <i>database handle</i>. Or,
    using SQLite's <i>shared-cache mode</i> feature, multiple <i>database
    handles</i> may access a single <i>database connection</i>.

    <center><img src="images/fileformat/db_connection.gif">
    <p><i>Figure <span class=fig id=figure_db_connection></span> - Relationship between Database Connections and Database Handles.</i>
      </center>

  <p>
    As well as a file-handle open on the database file, each
    <i>database connection</i> has a <i>page cache</i> associated with it.
    The <i>page cache</i> is used to cache data read from the database file
    to reduce the amount of data that must be read from the file-handle. It
    is also used to accumulate data written to the database file so that 
    write operations can be batched for greater efficiency. Figure 
    <cite>figure_db_connection</cite> illustrates a system containing two
    database connections, each to a separate database file. The leftmost of
    the two depicted <i>database connections</i> is shared between two 
    <i>database handles</i>. The connection illustrated towards the right of
    the diagram is used by a single <i>database handle</i>.








  <p>






    It may at first seem odd to mention the <i>page cache</i>, primarily
    an implementation detail, in this document. However, it is necessary to 
    acknowledge and describe the <i>page cache</i> in order to provide a
    more complete explanation of the nature and quantity of IO performed
    by SQLite. Further description of the <i>page cache</i> is provided in 
    section <cite>page_cache_descripton</cite>.
    

<!--
  <p>
    A database connection is always in one of the following states:

  <ol>
    <li><i>Unlocked state</i> (no transaction).
    <li><i>Shared lock state</i> (read-only transaction).
    <li><i>Reserved lock state</i> (read/write transaction).
    <li><i>Pending lock state</i> (read/write transaction).
    <li><i>Exclusive lock state</i> (read/write transaction).
  </ol>

  <p> Obviously, each state corresponds to the type of lock held on the
      database file. In some cases, various actions apart from simply obtaining
      the file-system lock must take place when a <i>database connection</i>
      transitions from one state to another.

 
  <p class=todo>
    Maybe a state diagram will be possible...
 -->

  <h2 id=page_cache_descripton>The Page Cache</h2>
    <p>
      The contents of an SQLite database file is formatted as a set of 
      fixed size pages (see <cite>ff_sqlitert_requirements</cite>) for a
      complete description of the format used. The <i>page size</i> used
      for a particular database is stored as part of the database file
      header at a well-known offset within the first 100 bytes of the 
      file.

    <p>
      As one might imagine, the <i>page cache</i> caches data read from the
      database file on a page basis. Whenever data is read from the database
      file to satisfy user queries, it is loaded in units of a page at a
      time (see section <cite>reading_data</cite> for further details). 
      After being read, page content is stored by the <i>page cache</i> in
      main memory. The next time the page data is required, it may be read
      from the <i>page cache</i> instead of from the database file.

    <p>
      Data is also cached within the <i>page cache</i> before it is written
      to the database file. Usually, when a user issues a command that modifies
      the content of the database file, only the cached version of the 
      page within the connection's <i>page cache</i> is modified. When the
      containing <i>write transaction</i> is committed, the content of all
      modified pages within the <i>page cache</i> are copied into the
      database file.

    <p class=todo>
      Some kind of reference to the 'page cache algorithms' section.
 





  <h2 id=open_new_connection>Opening a New Connection</h2>

    <p>
      This section describes the VFS operations that take place when a
      new database connection is created. 

................................................................................

    REQ H21009
      If the <i>database file header</i> cannot be read from a newly opened 
      database file (because the file is less than 100 bytes in size), the 
      connections <i>expected page-size</i> shall be set to the compile time
      value of the SQLITE_DEFAULT_PAGESIZE option.

  <h2>Closing a Connection</h2>

    <p>
      This section describes the VFS operations that take place when an
      existing database connection is closed (destroyed). 

    <p>
      Closing a database connection is a simple matter. The open VFS 
................................................................................
      file-handle is closed and in-memory <i>page cache</i> related resources
      are released. 

    REQ H21040
      When a <i>database connection</i> is closed, SQLite shall close the 
      associated file handle at the VFS level.





<h1 id=reading_data>Reading Data</h1>
  <p>
    In order to return data from the database to the user, for example as
    the results of a SELECT query, SQLite must at some point read data
    from the database file. Usually, data is read from the database file in 
    aligned blocks of <i>page-size</i> bytes. The exception is when the
    database file header fields are being inspected, before the
    <i>page-size</i> used by the database can be known.

  <p>
    With two exceptions, a <i>database connection</i> must have an open 
    transaction (either a <i>read-only transaction</i> or a 
    <i>read/write transaction</i>) on the database file before data may be 
    read from the database connection. In this case, data "read from the 
    database connection" includes data that is read from the database file 
    and data that is already present in the <i>page cache</i>. Without an 
    open transaction on the database file, the contents of the 
    <i>page cache</i> may not be trusted.

  <p>
    The two exceptions are:
  <ul>
    <li> When an attempt is made to read the 100 byte <i>database file
	 header</i> immediately after opening the <i>database connection</i>
	 (see section <cite>open_new_connection</cite>). When this occurs
         no lock is held on the database file.
    <li> Data read while in the process of opening a read-only transaction
         (see section <cite>open_read_only_trans</cite>). These occur after
         a <i>shared lock</i> is held on the database file.

  </ul>

  <p>
    Once a transaction has been opened, reading data from a database 
    connection is a simple operation. Using the xRead() method of the 
    file-handle open on the database file, the required database file 
    pages are read one at a time. SQLite never reads partial pages and
    always uses a single call to xRead() for each required page. After


    reading the data for a database page, SQLite adds it to the connections
    <i>page cache</i> so that it does not have to be read if required












    again. Refer to section <cite>page_cache_algorithms</cite> for a
    description of how this affects the IO performed by SQLite.



  REQ H21001
    Except for the read operation required by H21007 and those reads made
    as part of opening a read-only transaction, SQLite shall only read
    data from a <i>database connection</i> while the 
    <i>database connection</i> has an open read-only or read/write transaction.

  <p>
    In the above requirement, reading data from a database connection
    includes retrieving data from the connections <i>page cache</i>.


  REQ H21002
    Aside from those read operations described by H21007 and H21XXX, SQLite
    shall read data from the database in aligned blocks of <i>page-size</i>
    bytes, where <i>page-size</i> is the database page size used by the
    database file.






  <h2 id=open_read_only_trans>Opening a Read-Only Transaction</h2>
    <p>
      Before data may be read from a <i>database connection</i>, a 
      <i>read-only transaction</i> must be successfully opened (this is true

      even if the connection will eventually write to the database, as a
      <i>read/write transaction</i> may only be opened by upgrading from a
      <i>read-only transaction</i>). This section describes the procedure
      for opening a <i>read-only transaction</i>.

    <p>
      The key element of a <i>read-only transaction</i> is that the 
................................................................................
      file-handle open on the database file obtains and holds a
      <i>shared-lock</i> on the database file. Because a connection requires
      an <i>exclusive-lock</i> before it may actually modify the contents
      of the database file, and by definition while one connection is holding
      a <i>shared-lock</i> no other connection may hold an 
      <i>exclusive-lock</i>, holding a <i>shared-lock</i> guarantees that
      no other process may modify the database file while the <i>read-only
      transaction</i> remains open.



    <p>Obtaining the <i>shared lock</i> itself on the database file is quite
       simple, SQLite just calls the xLock() method of the database file 
       handle. Some of the other processes that take place as part of 
       opening the <i>read-only transaction</i> are quite complex. The list
       of steps SQLite is required to take to open a <i>read-only
       transaction</i>, in the order in which the must occur, is as follows:

    <ol>
      <li>A <i>shared-lock</i> is obtained on the database file.
      <li>The connection checks if a <i>hot journal file</i> exists in the
          file-system. If one does, then it is rolled back before continuing.
      <li>The connection checks if the data in the <i>page cache</i> may 
          still be trusted. If not, all page cache data is discarded.
................................................................................
      the <i>shared-lock</i> on the database file, then the process is
      abandoned, no transaction is opened and an error returned to the user.

    <p>
      The most common reason an attempt to obtain a <i>shared-lock</i> may
      fail is that some other connection is holding an <i>exclusive</i> or
      <i>pending lock</i>. However it may also fail because some other
      error (e.g. IO, comms related) occurs within the call to the xLock()
      method.

    REQ H21003
      While opening a <i>read-only transaction</i>, after successfully
      obtaining a <i>shared lock</i> on the database file, SQLite shall 
      attempt to detect and roll back a <i>hot journal file</i> associated 
      with the same database file.

................................................................................
    <p>
      Section <cite>hot_journal_detection</cite> contains a description of
      and requirements governing the detection of a hot-journal file refered
      to in the above requirements.

    REQ H21004
      Assuming no errors have occured, then after attempting to detect and
      roll back a <i>hot journal file</i>, if the connections 

      <i>page cache</i> is not empty, then SQLite shall validate the contents 
      of the <i>page cache</i> by testing the <i>file change counter</i>. 
      This procedure is known as <i>cache validiation</i>.





    REQ H21005




      If the contents of the <i>page cache</i> are found to be invalid by
      the check prescribed by F20040, SQLite shall discard the cache contents
      before continuing.
      







  <h3 id=hot_journal_detection>Hot Journal Detection</h3>
    <p>
      This section describes the procedure that SQLite uses to detect a
      <i>hot journal file</i>. If a <i>hot journal file</i> is detected,
      this indicates that at some point the process of writing a 
      transaction to the database was interrupted and a recovery operation
      (<i>hot journal rollback</i>) needs to take place.





    <p>
      The procedure used to detect a <i>hot-journal file</i> is quite
      complex. The following steps take place:

      <ol class=spacedlist>
        <li>Using the VFS xAccess() method, SQLite queries the file-system 
................................................................................
      more detail.

    REQ H21014
      When required to attempt to detect a <i>hot-journal file</i>, SQLite
      shall first use the xAccess() method of the VFS layer to check if a
      journal file exists in the file-system.







    REQ H21015
      When required to attempt to detect a <i>hot-journal file</i>, if the
      call to xAccess() required by H21014 indicates that a journal file does
      not exist, then the attempt to detect a <i>hot-journal file</i> is
      finished. A <i>hot-journal file</i> was not detected.


    <p>
      The following requirements describe step 2 of the above procedure in
      more detail.

    REQ H21016
      When required to attempt to detect a <i>hot-journal file</i>, if the
      call to xAccess() required by H21014 indicates that a journal file
      is present, then the xCheckReservedLock() method of the database file
      file-handle is invoked to determine whether or not some other 
      process is holding a <i>reserved</i> or greater lock on the database 
      file.







    REQ H21017
      If the call to xCheckReservedLock() required by H21016 indicates that
      some other <i>database connection</i> is holding a <i>reserved</i>
      or greater lock on the database file, 



    <p class=todo>
      Finish this section.




























































































  <h3 id=cache_validation>Cache Validation</h3>
    <p>
      When a <i>database connection</i> opens a <i>read transaction</i>, the
      associated <i>page cache</i> may already contain data. However, if

      another process has modified the database file since the cached pages
      were loaded it is possible that the cached data is invalid.

    <p>
      SQLite determines whether or not the contents of a <i>page cache</i>
      are valid or not using the <i>file change counter</i>, a field
      in the <i>database file header</i>. The <i>file change counter</i>
      is a 4-byte big-endian integer field stored starting at byte offset
      24 of the <i>database file header</i>. Before the conclusion of a 
      <i>read/write transaction</i> that modifies the contents of the
      database file in any way (see section <cite>writing_data</cite>),
      the value stored in the <i>file change counter</i> is incremented.
      When a <i>database connection</i> unlocks the database file, it stores
      the current value of the <i>file change counter</i>. Later, while
      opening a new <i>read-only transaction</i>, SQLite checks the value
      of the <i>file change counter</i> stored in the database file. If the
      value has not changed since the database file was unlocked, then the
      contents of the <i>page cache</i> can be trusted. If the value has
      changed, then the <i>page cache</i> cannot be trusted and all data

      is discarded.
   
    REQ H21018
      When a file-handle open on a database file is unlocked, if the

      <i>page cache</i> belonging to the associated <i>database connection</i>
      is not empty, SQLite shall store the value of the <i>file change
      counter</i> internally.

    REQ H21019
      When required to perform <i>cache validation</i> as part of opening
      a <i>read transaction</i>, SQLite shall read a 16 byte block 
      starting at byte offset 24 of the <i>database file</i> using the xRead()
      method of the <i>database connections</i> file handle.

................................................................................
      the contents of the cache are invalid.

    <p>
      Requirement H21005 (section <cite>open_read_only_trans</cite>) 
      specifies the action SQLite is required to take upon determining that 
      the cache contents are invalid.


  <h3 id=read_page_one>Page 1 and the Expected Page Size</h3>
    <p>
      As the last step in opening a <i>read transaction</i> on a database
      file that is more than 0 bytes in size, SQLite is required to load 
      data for page 1 of the database into the <i>page cache</i>, if it is 
      not already there. This is slightly more complicated than it seems, 
      as the database <i>page-size</i> is no known at this point.
................................................................................
    <p>
      Even though the database <i>page-size</i> cannot be known for sure,
      SQLite is usually able to guess correctly by assuming it to be equal to
      the connections <i>expected page size</i>. The <i>expected page size</i>
      is the value of the <i>page-size</i> field read from the 
      <i>database file header</i> while opening the database connection 
      (see section <cite>open_new_connection</cite>), or the <i>page-size</i>
      stored of the database file when the most <i>read transaction</i> was
      concluded.

    REQ H21021
      During the conclusing of a <i>read transaction</i>, before unlocking
      the database file, SQLite shall set the connections 
      <i>expected page size</i> to the current database <i>page-size</i>.

    REQ H21022
      As part of opening a new <i>read transaction</i>, immediately after 
      performing <i>cache validation</i>, if there is no data for database
      page 1 in the <i>page cache</i>, SQLite shall read <i>N</i> bytes from
................................................................................
      is repeated.

    REQ H21024
      If page 1 data is read as required by H21023, then the value of the
      <i>page-size</i> field that appears in the database file header that
      consumes the first 100 bytes of the read block is the same as the
      connections current <i>expected page size</i>, then the block of data
      read is added to the connections <i>page cache</i> as page 1.






  <h2>Ending a Read-only Transaction</h2>
    <p>
      To end a <i>read-only transaction</i>, SQLite simply relinquishes the
      <i>shared lock</i> on the file-handle open on the database file. No
      other action is required.

................................................................................

<h1 id=rollback>Rollback</h1>
  <h2 id=hot_journal_rollback>Hot Journal Rollback</h2>
  <h2>Transaction Rollback</h2>
  <h2>Statement Rollback</h2>

<h1 id=page_cache_algorithms>Page Cache Algorithms</h1>





































<h1>References</h1>
  <table id="refs" style="width:auto; margin: 1em 5ex">
    <tr><td style="width:5ex" id="capi_sqlitert_requirements">[1]<td>
      C API Requirements Document.
    <tr><td style="width:5ex" id="sql_sqlitert_requirements">[2]<td>
      SQL Requirements Document.
    <tr><td style="width:5ex" id="ff_sqlitert_requirements">[3]<td>
      File Format Requirements Document.
  </table>
}]</tcl>








|







 







|







 







|
|
|
|
|







 







<
<
<
<
<




|
|
<







 







|
|
|
|

<
<
<
<

<
|
<
<
<
<
<
<
<
<
|
>
>
>
>
>
>
>


>
>
>
>
>
>
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
|
<
>
|

<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
>
>
>
>







 







|







 







>
>
>
>












|
<
|
<
<
<









|
|
>







|
>
>
|
<
>
>
>
>
>
>
>
>
>
>
>
>
|
<
>
>



|
<
|
<
<
<
<
>



|
|
|
>
>
>
>
>



|
|
>







 







|
>
>




|
|
|







 







|
|







 







|
>
|
|
|

>
>
>
>

>
>
>
>
|
<
<
|
>
>
>
>
>
>







|
>
>
>
>







 







>
>
>
>
>
>



|
|
>













>
>
>
>
>
>



|
>
>

<
<
>
>
>

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



|
>
|
|


|
|
|
|
|
|
|
|
|
|
|
|
|
|
<
>
|



>
|
<
|







 







<







 







|
<


|







 







|
>
>
>
>
>







 







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












56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
...
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
...
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
...
497
498
499
500
501
502
503





504
505
506
507
508
509

510
511
512
513
514
515
516
...
600
601
602
603
604
605
606
607
608
609
610
611




612

613








614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630





















631

632
633
634

































635
636
637
638
639
640
641
642
643
644
645
...
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
...
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726

727



728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750

751
752
753
754
755
756
757
758
759
760
761
762
763

764
765
766
767
768
769

770




771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
...
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
...
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
...
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885


886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
...
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007


1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122

1123
1124
1125
1126
1127
1128
1129

1130
1131
1132
1133
1134
1135
1136
1137
....
1148
1149
1150
1151
1152
1153
1154

1155
1156
1157
1158
1159
1160
1161
....
1163
1164
1165
1166
1167
1168
1169
1170

1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
....
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
....
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
  <b>Javascript is required for some features of this document, including 
     table of contents, figure numbering and internal references (section
     numbers and hyper-links.
  </b>
</div>
<!-- End of standard rt docs header -->

<h1 id=overview>Overview</h1>

  <p>
    SQLite stores an entire database within a single file, the format of
    which is described in the <i>SQLite File Database File Format</i> 
    document <cite>ff_sqlitert_requirements</cite>. Each database file is
    stored within a file system, presumably provided by the host operating
    system. Instead of interfacing with the operating system directly, 
................................................................................
    <li><p>For performance reasons, it is advantageous to <b>minimize the 
        quantity of data read and written</b> to and from the file-system.

        <p>As one might expect, the amount of data read from the database 
        file is minimized by caching portions of the database file in main 
        memory. Additionally, multiple updates to the database file that
        are part of the same <i>write transaction</i> may be cached in
        main memory and written to the file together, allowing for
        more efficient IO patterns and eliminating the redundant write 
        operations that could take place if part of the database file is
        modified more than once within a single <i>write transaction</i>.

  </ol>

  <p class=todo>
................................................................................
      It is assumed that writing a series of sequential blocks of data to 
      a file in order is faster than writing the same blocks in an arbitrary
      order.

  <h2 id=fs_characteristics>System Failure Related Assumptions</h2>
    <p>
      In the event of an operating system or power failure, the various 
      combinations of file-system software and storage hardware available
      provide varying levels of guarantee as to the integrity of the data
      written to the file system just before or during the failure. The exact
      combination of IO operations that SQLite is required to perform in 
      order to safely modify a database file depend on the exact 
      characteristics of the target platform.

    <p>
      This section describes the assumptions that SQLite makes about the
      the content of a file-system following a power or system failure. In
      other words, it describes the extent of file and file-system corruption
      that such an event may cause.
................................................................................
      untrustworthy as defined by A21008 and neither A21011 or A21012 
      apply to the range of bytes written, then no assumption can be
      made about the content of the sector following recovery. It is
      assumed that it is possible for such a sector to be written 
      correctly, not written at all, populated with garbage data or any
      combination thereof.






    ASSUMPTION A21009
      If a system failure occurs during or after a "write file"
      operation that causes the file to grow, but before the corresponding 
      file has been <i>synced</i>, then it is assumed that the size of 
      the file following recovery is as large or larger than it was when
      it was most recently <i>synced</i>.


    <p>
      If a system supports the <i>sequential-write</i> property, then further
      assumptions may be made with respect to the state of the file-system
      following recovery from a <i>system failure</i>. Specifically, it is
      assumed that create, truncate, delete and write file operations are
      applied to the persistent representation in the same order as they 
................................................................................
  <p>
    Within this document, the term <i>database connection</i> has a slightly
    different meaning from that which one might assume. The handles returned
    by the <code>sqlite3_open()</code> and <code>sqlite3_open16()</code>
    APIs (<span class=todo>reference</span>) are referred to as <i>database
    handles</i>.  A <i>database connection</i> is a connection to a single
    database file using a single file-handle, which is held open for the
    lifetime of the connection. Using the SQL ATTACH syntax, multiple
    <i>database connections</i> may be accessed via a single <i>database
    handle</i>. Or, using SQLite's <i>shared-cache mode</i> feature, multiple
    <i>database handles</i> may access a single <i>database connection</i>.





  <p>

    Usually, a new <i>database connection</i> is opened whenever the user opens








    new <i>database handle</i> on a real database file (not an in-memory
    database) or when a database file is attached to an existing <i>database
    connection</i> using the SQL ATTACH syntax. However if the <i>shared-cache
    mode</i> feature is enabled, then the database file may be accessed through
    an existing <i>database connection</i>. For more information on
    <i>shared-cache mode</i>, refer to <span class=todo>Reference</span>.  The
    various IO operations required to open a new connection are detailed in
    section <cite>open_new_connection</cite> of this document.

  <p>
    Similarly, a <i>database connection</i> is usually closed when the user
    closes a <i>database handle</i> that is open on a real database file or
    has had one or more real database files attached to it using the ATTACH
    mechanism, or when a real database file is detached from a <i>database
    connection</i> using the DETACH syntax. Again, the exception is if
    <i>shared-cache mode</i> is enabled. In this case, a <i>database
    connection</i> is not closed until its number of users reaches zero.





















    The IO related steps required to close a <i>database connection</i> are

    described in section <cite>closing_database_connection</cite>.

  <p class=todo>

































    After sections 4 and 5 are finished, come back here and see if we can add a
    list of state items associated with each database connection to make things
    easier to understand. i.e each database connection has a file handle, a set
    of entries in the page cache, an expected page size etc.

  <h2 id=open_new_connection>Opening a New Connection</h2>

    <p>
      This section describes the VFS operations that take place when a
      new database connection is created. 

................................................................................

    REQ H21009
      If the <i>database file header</i> cannot be read from a newly opened 
      database file (because the file is less than 100 bytes in size), the 
      connections <i>expected page-size</i> shall be set to the compile time
      value of the SQLITE_DEFAULT_PAGESIZE option.

  <h2 id=closing_database_connection>Closing a Connection</h2>

    <p>
      This section describes the VFS operations that take place when an
      existing database connection is closed (destroyed). 

    <p>
      Closing a database connection is a simple matter. The open VFS 
................................................................................
      file-handle is closed and in-memory <i>page cache</i> related resources
      are released. 

    REQ H21040
      When a <i>database connection</i> is closed, SQLite shall close the 
      associated file handle at the VFS level.

    REQ H21043
      When a <i>database connection</i> is closed, all associated <i>page
      cache</i> entries shall be discarded.

<h1 id=reading_data>Reading Data</h1>
  <p>
    In order to return data from the database to the user, for example as
    the results of a SELECT query, SQLite must at some point read data
    from the database file. Usually, data is read from the database file in 
    aligned blocks of <i>page-size</i> bytes. The exception is when the
    database file header fields are being inspected, before the
    <i>page-size</i> used by the database can be known.

  <p>
    With two exceptions, a <i>database connection</i> must have an open 
    transaction (either a <i>read-only transaction</i> or a 
    <i>read/write transaction</i>) on the database before data may be 

    read from the database file. 




  <p>
    The two exceptions are:
  <ul>
    <li> When an attempt is made to read the 100 byte <i>database file
	 header</i> immediately after opening the <i>database connection</i>
	 (see section <cite>open_new_connection</cite>). When this occurs
         no lock is held on the database file.
    <li> Data read while in the process of opening a read-only transaction
         (see section <cite>open_read_only_trans</cite>). These read 
	 operations occur after a <i>shared lock</i> is held on the database
	 file.
  </ul>

  <p>
    Once a transaction has been opened, reading data from a database 
    connection is a simple operation. Using the xRead() method of the 
    file-handle open on the database file, the required database file 
    pages are read one at a time. SQLite never reads partial pages and
    always uses a single call to xRead() for each required page. 

   <p>
    After reading the data for a database page, SQLite stores the raw

    page of data in the <i>page cache</i>. Each time a page of data is 
    required by the upper layers, the <i>page cache</i> is queried
    to see if it contains a copy of the required page stored by
    the current <i>database connection</i>. If such an entry can be
    found, then the required data is read from the <i>page cache</i> instead
    of the database file. Only a connection with an open transaction
    transaction (either a <i>read-only transaction</i> or a 
    <i>read/write transaction</i>) on the database may read data from the
    <i>page cache</i>. In this sense reading from the <i>page cache</i> is no
    different to reading from the <i>database file</i>.

   <p>
    Refer to section <cite>page_cache_algorithms</cite> for a description 

    of exactly how and for how long page data is stored in the 
    <i>page cache</i>.

  REQ H21001
    Except for the read operation required by H21007 and those reads made
    as part of opening a read-only transaction, SQLite shall ensure that

    a <i>database connection</i> has an open read-only or read/write 




    transaction when any data is read from the <i>database file</i>.

  REQ H21002
    Aside from those read operations described by H21007 and H21XXX, SQLite
    shall read data from the database file in aligned blocks of 
    <i>page-size</i> bytes, where <i>page-size</i> is the database page size 
    used by the database file.

  REQ H21042
    SQLite shall ensure that a <i>database connection</i> has an open
    read-only or read/write transaction before using data stored in the <i>page
    cache</i> to satisfy user queries.

  <h2 id=open_read_only_trans>Opening a Read-Only Transaction</h2>
    <p>
      Before data may be read from a <i>database file</i> or queried from
      the <i>page cache</i>, a <i>read-only transaction</i> must be
      successfully opened by the associated database connection (this is true
      even if the connection will eventually write to the database, as a
      <i>read/write transaction</i> may only be opened by upgrading from a
      <i>read-only transaction</i>). This section describes the procedure
      for opening a <i>read-only transaction</i>.

    <p>
      The key element of a <i>read-only transaction</i> is that the 
................................................................................
      file-handle open on the database file obtains and holds a
      <i>shared-lock</i> on the database file. Because a connection requires
      an <i>exclusive-lock</i> before it may actually modify the contents
      of the database file, and by definition while one connection is holding
      a <i>shared-lock</i> no other connection may hold an 
      <i>exclusive-lock</i>, holding a <i>shared-lock</i> guarantees that
      no other process may modify the database file while the <i>read-only
      transaction</i> remains open. This ensures that <i>read-only
      transactions</i> are sufficiently isolated from the transactions of
      other database users (see section <cite>overview</cite>).

    <p>Obtaining the <i>shared lock</i> itself on the database file is quite
       simple, SQLite just calls the xLock() method of the database file 
       handle. Some of the other processes that take place as part of 
       opening the <i>read-only transaction</i> are quite complex. The 
       steps that SQLite is required to take to open a <i>read-only
       transaction</i>, in the order in which they must occur, is as follows:

    <ol>
      <li>A <i>shared-lock</i> is obtained on the database file.
      <li>The connection checks if a <i>hot journal file</i> exists in the
          file-system. If one does, then it is rolled back before continuing.
      <li>The connection checks if the data in the <i>page cache</i> may 
          still be trusted. If not, all page cache data is discarded.
................................................................................
      the <i>shared-lock</i> on the database file, then the process is
      abandoned, no transaction is opened and an error returned to the user.

    <p>
      The most common reason an attempt to obtain a <i>shared-lock</i> may
      fail is that some other connection is holding an <i>exclusive</i> or
      <i>pending lock</i>. However it may also fail because some other
      error (e.g. an IO or comms related error) occurs within the call to the
      xLock() method.

    REQ H21003
      While opening a <i>read-only transaction</i>, after successfully
      obtaining a <i>shared lock</i> on the database file, SQLite shall 
      attempt to detect and roll back a <i>hot journal file</i> associated 
      with the same database file.

................................................................................
    <p>
      Section <cite>hot_journal_detection</cite> contains a description of
      and requirements governing the detection of a hot-journal file refered
      to in the above requirements.

    REQ H21004
      Assuming no errors have occured, then after attempting to detect and
      roll back a <i>hot journal file</i>, if the <i>page cache</i> contains
      any entries associated with the current <i>database connection</i>,
      then SQLite shall validate the contents of the <i>page cache</i> by
      testing the <i>file change counter</i>.  This procedure is known as
      <i>cache validiation</i>.

    <p>
      The <i>cache validiation</i> process is described in detail in section
      <cite>cache_validation</cite>

    REQ H21005
      If the cache validiate procedure prescribed by H21004 is required and
      does not prove that the <i>page cache</i> entries associated with the
      current <i>database connection</i> are valid, then SQLite shall discard
      all entries associated with the current <i>database connection</i> from
      the <i>page cache</i>.



    <p>
      The numbered list above notes that the data for the first page of the
      database file, if it exists and is not already loaded into the <i>page
      cache</i>, must be read from the database file before the <i>read-only
      transaction</i> may be considered opened. This is handled by 
      requirement H21024.

  <h3 id=hot_journal_detection>Hot Journal Detection</h3>
    <p>
      This section describes the procedure that SQLite uses to detect a
      <i>hot journal file</i>. If a <i>hot journal file</i> is detected,
      this indicates that at some point the process of writing a 
      transaction to the database was interrupted and a recovery operation
      (<i>hot journal rollback</i>) needs to take place. This section does
      not describe the process of <i>hot journal rollback</i> (see section
      <cite>hot_journal_rollback</cite>) or the processes by which a
      <i>hot journal file</i> may be created (see section
      <cite>writing_data</cite>).

    <p>
      The procedure used to detect a <i>hot-journal file</i> is quite
      complex. The following steps take place:

      <ol class=spacedlist>
        <li>Using the VFS xAccess() method, SQLite queries the file-system 
................................................................................
      more detail.

    REQ H21014
      When required to attempt to detect a <i>hot-journal file</i>, SQLite
      shall first use the xAccess() method of the VFS layer to check if a
      journal file exists in the file-system.

    REQ H21051
      If the call to xAccess() required by H21014 fails (due to an IO error or
      similar), then SQLite shall abandon the attempt to open a <i>read-only
      transaction</i>, relinquish the <i>shared lock</i> held on the database
      file and return an error to the user.

    REQ H21015
      When required to attempt to detect a <i>hot-journal file</i>, if the
      call to xAccess() required by H21014 indicates that a journal file does
      not exist, then SQLite shall conclude that there is no <i>hot-journal
      file</i> in the file system and therefore that no <i>hot journal
      rollback</i> is required.

    <p>
      The following requirements describe step 2 of the above procedure in
      more detail.

    REQ H21016
      When required to attempt to detect a <i>hot-journal file</i>, if the
      call to xAccess() required by H21014 indicates that a journal file
      is present, then the xCheckReservedLock() method of the database file
      file-handle is invoked to determine whether or not some other 
      process is holding a <i>reserved</i> or greater lock on the database 
      file.

    REQ H21052
      If the call to xCheckReservedLock() required by H21016 fails (due to an
      IO or other internal VFS error), then SQLite shall abandon the attempt
      to open a <i>read-only transaction</i>, relinquish the <i>shared lock</i>
      held on the database file and return an error to the user.

    REQ H21017
      If the call to xCheckReservedLock() required by H21016 indicates that
      some other <i>database connection</i> is holding a <i>reserved</i>
      or greater lock on the database file, then SQLite shall conclude that
      there is no <i>hot journal file</i>. In this case the attempt to detect 
      a <i>hot journal file</i> is concluded.



    <p>
      The following requirements describe step 3 of the above procedure in
      more detail.

    REQ H21044
      If while attempting to detect a <i>hot-journal file</i> the call to
      xCheckReservedLock() indicates that no process holds a <i>reserved</i>
      or greater lock on the <i>database file</i>, then SQLite shall open
      a file handle on the potentially hot journal file using the VFS xOpen()
      method.

    REQ H21053
      If the call to xOpen() required by H21044 fails (due to an IO or other
      internal VFS error), then SQLite shall abandon the attempt to open a
      <i>read-only transaction</i>, relinquish the <i>shared lock</i> held on
      the database file and return an error to the user.

    REQ H21045
      After successfully opening a file-handle on a potentially hot journal
      file, SQLite shall query the file for its size in bytes using the
      xFileSize() method of the open file handle. 

    REQ H21054
      If the call to xFileSize() required by H21045 fails (due to an IO or
      other internal VFS error), then SQLite shall abandon the attempt to open
      a <i>read-only transaction</i>, relinquish the <i>shared lock</i> held on
      the database file, close the file handle opened on the journal file and
      return an error to the user.

    REQ H21046
      If the size of a potentially hot journal file is revealed to be zero
      bytes by a query required by H21045, then SQLite shall close the
      file handle opened on the journal file and delete the journal file using
      a call to the VFS xDelete() method. In this case SQLite shall conclude
      that there is no <i>hot journal file</i>.

    REQ H21055
      If the call to xDelete() required by H21045 fails (due to an IO or
      other internal VFS error), then SQLite shall abandon the attempt to open
      a <i>read-only transaction</i>, relinquish the <i>shared lock</i> held on
      the database file and return an error to the user.

    <p>
      The following requirements describe step 4 of the above procedure in
      more detail.

    REQ H21047
      If the size of a potentially hot journal file is revealed to be greater
      than zero bytes by a query required by H21045, then SQLite shall attempt
      to upgrade the <i>shared lock</i> held by the <i>database connection</i>
      on the <i>database file</i> directly to an <i>exclusive lock</i>.

    REQ H21048
      If an attempt to upgrade to an <i>exclusive lock</i> prescribed by 
      H21047 fails for any reason, then SQLite shall release all locks held by
      the <i>database connection</i> and close the file handle opened on the
      <i>journal file</i>. The attempt to open a <i>read-only transaction</i>
      shall be deemed to have failed and an error returned to the user.

    <p>
      Finally, the following requirements describe step 5 of the above
      procedure in more detail.

    REQ H21049
      If, as part of the <i>hot journal file</i> detection process, the
      attempt to upgrade to an <i>exclusive lock</i> mandated by H21047 is
      successful, then SQLite shall query the file-system using the xAccess()
      method of the VFS implementation to test whether or not the journal
      file is still present in the file-system.

    REQ H21056
      If the call to xAccess() required by H21049 fails (due to an IO or
      other internal VFS error), then SQLite shall abandon the attempt to open
      a <i>read-only transaction</i>, relinquish the lock held on the 
      database file, close the file handle opened on the journal file and
      return an error to the user.
    
    REQ H21057
      If the call to xAccess() required by H21049 reveals that the journal
      file is no longer present in the file system, then SQLite shall abandon 
      the attempt to open a <i>read-only transaction</i>, relinquish the 
      lock held on the database file, close the file handle opened on the 
      journal file and return an SQLITE_BUSY error to the user.

    REQ H21050
      If the xAccess() query required by H21049 reveals that the journal
      file is still present in the file system, then SQLite shall conclude
      that the journal file is a <i>hot journal file</i> that needs to
      be rolled back. SQLite shall immediately begin <i>hot journal
      rollback</i>.


  <h3 id=cache_validation>Cache Validation</h3>
    <p>
      When a <i>database connection</i> opens a <i>read transaction</i>, the
      <i>page cache</i> may already contain data associated with the
      <i>database connection</i>. However, if another process has modified 
      the database file since the cached pages were loaded it is possible that
      the cached data is invalid.

    <p>
      SQLite determines whether or not the <i>page cache</i> entries belonging
      to the <i>database connection</i> are valid or not using the <i>file
      change counter</i>, a field in the <i>database file header</i>. The
      <i>file change counter</i> is a 4-byte big-endian integer field stored
      starting at byte offset 24 of the <i>database file header</i>. Before the
      conclusion of a <i>read/write transaction</i> that modifies the contents
      of the database file in any way (see section <cite>writing_data</cite>),
      the value stored in the <i>file change counter</i> is incremented.  When
      a <i>database connection</i> unlocks the database file, it stores the
      current value of the <i>file change counter</i>. Later, while opening a
      new <i>read-only transaction</i>, SQLite checks the value of the <i>file
      change counter</i> stored in the database file. If the value has not
      changed since the database file was unlocked, then the <i>page cache</i>
      entries can be trusted. If the value has changed, then the <i>page

      cache</i> entries cannot be trusted and all entries associated with
      the current <i>database connection</i> is discarded.
   
    REQ H21018
      When a file-handle open on a database file is unlocked, if the
      <i>page cache</i> contains one or more entries belonging to the
      associated <i>database connection</i>, SQLite shall store the value 

      of the <i>file change counter</i> internally.

    REQ H21019
      When required to perform <i>cache validation</i> as part of opening
      a <i>read transaction</i>, SQLite shall read a 16 byte block 
      starting at byte offset 24 of the <i>database file</i> using the xRead()
      method of the <i>database connections</i> file handle.

................................................................................
      the contents of the cache are invalid.

    <p>
      Requirement H21005 (section <cite>open_read_only_trans</cite>) 
      specifies the action SQLite is required to take upon determining that 
      the cache contents are invalid.


  <h3 id=read_page_one>Page 1 and the Expected Page Size</h3>
    <p>
      As the last step in opening a <i>read transaction</i> on a database
      file that is more than 0 bytes in size, SQLite is required to load 
      data for page 1 of the database into the <i>page cache</i>, if it is 
      not already there. This is slightly more complicated than it seems, 
      as the database <i>page-size</i> is no known at this point.
................................................................................
    <p>
      Even though the database <i>page-size</i> cannot be known for sure,
      SQLite is usually able to guess correctly by assuming it to be equal to
      the connections <i>expected page size</i>. The <i>expected page size</i>
      is the value of the <i>page-size</i> field read from the 
      <i>database file header</i> while opening the database connection 
      (see section <cite>open_new_connection</cite>), or the <i>page-size</i>
      of the database file when the most <i>read transaction</i> was concluded.


    REQ H21021
      During the conclusion of a <i>read transaction</i>, before unlocking
      the database file, SQLite shall set the connections 
      <i>expected page size</i> to the current database <i>page-size</i>.

    REQ H21022
      As part of opening a new <i>read transaction</i>, immediately after 
      performing <i>cache validation</i>, if there is no data for database
      page 1 in the <i>page cache</i>, SQLite shall read <i>N</i> bytes from
................................................................................
      is repeated.

    REQ H21024
      If page 1 data is read as required by H21023, then the value of the
      <i>page-size</i> field that appears in the database file header that
      consumes the first 100 bytes of the read block is the same as the
      connections current <i>expected page size</i>, then the block of data
      read is stored in the <i>page cache</i> as page 1.

  <h2>Reading Database Data</h2>

  <p class=todo>
    Add something about checking the page-cache first etc.

  <h2>Ending a Read-only Transaction</h2>
    <p>
      To end a <i>read-only transaction</i>, SQLite simply relinquishes the
      <i>shared lock</i> on the file-handle open on the database file. No
      other action is required.

................................................................................

<h1 id=rollback>Rollback</h1>
  <h2 id=hot_journal_rollback>Hot Journal Rollback</h2>
  <h2>Transaction Rollback</h2>
  <h2>Statement Rollback</h2>

<h1 id=page_cache_algorithms>Page Cache Algorithms</h1>
  <p>
    The contents of an SQLite database file are formatted as a set of 
    fixed size pages. See <cite>ff_sqlitert_requirements</cite> for a
    complete description of the format used. The <i>page size</i> used
    for a particular database is stored as part of the database file
    header at a well-known offset within the first 100 bytes of the 
    file.

  <p>
    As one might imagine, the <i>page cache</i> caches data read from the
    database file on a page basis. Whenever data is read from the database
    file to satisfy user queries, it is loaded in units of a page at a
    time (see section <cite>reading_data</cite> for further details). 
    After being read, page content is stored by the <i>page cache</i> in
    main memory. The next time the page data is required, it may be retrieved
    from the <i>page cache</i> instead of from the database file.

  REQ H21041
    After reading a page of data from a database file, SQLite shall 
    store the data in the <i>page cache</i>.

  <p>
    Data is also cached within the <i>page cache</i> before it is written
    to the database file. Usually, when a user issues a command that modifies
    the content of the database file, only the cached version of the 
    page within the connection's <i>page cache</i> is modified. When the
    containing <i>write transaction</i> is committed, the content of all
    modified pages within the <i>page cache</i> are copied into the
    database file.

  <p>
    It may at first seem odd to mention the <i>page cache</i>, primarily
    an implementation detail, in this document. However, it is necessary to 
    acknowledge and describe the <i>page cache</i> in order to provide a
    more complete explanation of the nature and quantity of IO performed
    by SQLite. 

<h1>References</h1>
  <table id="refs" style="width:auto; margin: 1em 5ex">
    <tr><td style="width:5ex" id="capi_sqlitert_requirements">[1]<td>
      C API Requirements Document.
    <tr><td style="width:5ex" id="sql_sqlitert_requirements">[2]<td>
      SQL Requirements Document.
    <tr><td style="width:5ex" id="ff_sqlitert_requirements">[3]<td>
      File Format Requirements Document.
  </table>
}]</tcl>