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

Added doc/begin_concurrent.md.

            1  +
            2  +Begin Concurrent
            3  +================
            4  +
            5  +## Overview
            6  +
            7  +Usually, SQLite allows at most one writer to proceed concurrently. The
            8  +BEGIN CONCURRENT enhancement allows multiple writers to process write
            9  +transactions simultanously if the database is in "wal" or "wal2" mode,
           10  +although the system still serializes COMMIT commands.
           11  +
           12  +When a write-transaction is opened with "BEGIN CONCURRENT", actually 
           13  +locking the database is deferred until a COMMIT is executed. This means
           14  +that any number of transactions started with BEGIN CONCURRENT may proceed
           15  +concurrently. The system uses optimistic page-level-locking to prevent
           16  +conflicting concurrent transactions from being committed.
           17  +
           18  +When a BEGIN CONCURRENT transaction is committed, the system checks whether 
           19  +or not any of the database pages that the transaction has read have been
           20  +modified since the BEGIN CONCURRENT was opened. In other words - it asks 
           21  +if the transaction being committed operates on a different set of data than
           22  +all other concurrently executing transactions. If the answer is "yes, this
           23  +transaction did not read or modify any data modified by any concurrent
           24  +transaction", then the transaction is committed as normal. Otherwise, if the
           25  +transaction does conflict, it cannot be committed and an SQLITE_BUSY_SNAPSHOT
           26  +error is returned. At this point, all the client can do is ROLLBACK the
           27  +transaction.
           28  +
           29  +If SQLITE_BUSY_SNAPSHOT is returned, messages are output via the sqlite3_log
           30  +mechanism indicating the page and table or index on which the conflict
           31  +occurred. This can be useful when optimizing concurrency.
           32  +
           33  +## Application Programming Notes
           34  +
           35  +In order to serialize COMMIT processing, SQLite takes a lock on the database
           36  +as part of each COMMIT command and releases it before returning. At most one
           37  +writer may hold this lock at any one time. If a writer cannot obtain the lock,
           38  +it uses SQLite's busy-handler to pause and retry for a while:
           39  +
           40  +  <a href=https://www.sqlite.org/c3ref/busy_handler.html>
           41  +      https://www.sqlite.org/c3ref/busy_handler.html
           42  +  </a>
           43  +
           44  +If there is significant contention for the writer lock, this mechanism can be
           45  +inefficient. In this case it is better for the application to use a mutex or
           46  +some other mechanism that supports blocking to ensure that at most one writer
           47  +is attempting to COMMIT a BEGIN CONCURRENT transaction at a time. This is
           48  +usually easier if all writers are part of the same operating system process.
           49  +
           50  +If all database clients (readers and writers) are located in the same OS
           51  +process, and if that OS is a Unix variant, then it can be more efficient to
           52  +the built-in VFS "unix-excl" instead of the default "unix". This is because it
           53  +uses more efficient locking primitives.
           54  +
           55  +The key to maximizing concurrency using BEGIN CONCURRENT is to ensure that
           56  +there are a large number of non-conflicting transactions. In SQLite, each
           57  +table and each index is stored as a separate b-tree, each of which is
           58  +distributed over a discrete set of database pages. This means that:
           59  +
           60  +  * Two transactions that write to different sets of tables never 
           61  +    conflict, and that
           62  +
           63  +  * Two transactions that write to the same tables or indexes only 
           64  +    conflict if the values of the keys (either primary keys or indexed 
           65  +    rows) are fairly close together. For example, given a large 
           66  +    table with the schema:
           67  +
           68  +      <pre>     CREATE TABLE t1(a INTEGER PRIMARY KEY, b BLOB);</pre>
           69  +
           70  +    writing two rows with adjacent values for "a" probably will cause a
           71  +    conflict (as the two keys are stored on the same page), but writing two
           72  +    rows with vastly different values for "a" will not (as the keys will likly
           73  +    be stored on different pages).
           74  +
           75  +Note that, in SQLite, if values are not explicitly supplied for an INTEGER
           76  +PRIMARY KEY, as for example in:
           77  +
           78  +>
           79  +     INSERT INTO t1(b) VALUES(&lt;blob-value>);
           80  +
           81  +then monotonically increasing values are assigned automatically. This is
           82  +terrible for concurrency, as it all but ensures that all new rows are 
           83  +added to the same database page. In such situations, it is better to
           84  +explicitly assign random values to INTEGER PRIMARY KEY fields.
           85  +
           86  +This problem also comes up for non-WITHOUT ROWID tables that do not have an
           87  +explicit INTEGER PRIMARY KEY column. In these cases each table has an implicit
           88  +INTEGER PRIMARY KEY column that is assigned increasing values, leading to the
           89  +same problem as omitting to assign a value to an explicit INTEGER PRIMARY KEY
           90  +column.
           91  +
           92  +For both explicit and implicit INTEGER PRIMARY KEYs, it is possible to have
           93  +SQLite assign values at random (instead of the monotonically increasing
           94  +values) by writing a row with a rowid equal to the largest possible signed
           95  +64-bit integer to the table. For example:
           96  +
           97  +     INSERT INTO t1(a) VALUES(9223372036854775807);
           98  +
           99  +Applications should take care not to malfunction due to the presence of such
          100  +rows.
          101  +
          102  +The nature of some types of indexes, for example indexes on timestamp fields,
          103  +can also cause problems (as concurrent transactions may assign similar
          104  +timestamps that will be stored on the same db page to new records). In these
          105  +cases the database schema may need to be rethought to increase the concurrency
          106  +provided by page-level-locking.
          107  +

Changes to src/wal.h.

   155    155   
   156    156   /* Return the sqlite3_file object for the WAL file */
   157    157   sqlite3_file *sqlite3WalFile(Wal *pWal);
   158    158   
   159    159   /* Return the journal mode (WAL or WAL2) used by this Wal object. */
   160    160   int sqlite3WalJournalMode(Wal *pWal);
   161    161   
          162  +/* sqlite3_wal_info() data */
          163  +int sqlite3WalInfo(Wal *pWal, u32 *pnPrior, u32 *pnFrame);
          164  +
   162    165   /* sqlite3_wal_info() data */
   163    166   int sqlite3WalInfo(Wal *pWal, u32 *pnPrior, u32 *pnFrame);
   164    167   
   165    168   #endif /* ifndef SQLITE_OMIT_WAL */
   166    169   #endif /* SQLITE_WAL_H */