Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.
|Comment:||Merge documentation changes from branch "begin-concurrent" into this branch.|
|Downloads:||Tarball | ZIP archive | SQL archive|
|Timelines:||family | ancestors | descendants | both | begin-concurrent-wal2|
|Files:||files | file ages | folders|
|User & Date:||dan 2019-01-11 15:22:27|
|15:26||Merge documentation changes from branch begin-concurrent-wal2 into this branch. (check-in: cf8a0c71 user: dan tags: begin-concurrent-pnu-wal2)|
|15:22||Merge documentation changes from branch "begin-concurrent" into this branch. (Leaf check-in: 41e742bd user: dan tags: begin-concurrent-wal2)|
|15:06||Add new documentation file begin_concurrent.md. (check-in: fdbf97e6 user: dan tags: begin-concurrent)|
|14:59||Merge latest wal2 changes (documentation only) into this branch. (check-in: 820ba1cc user: dan tags: begin-concurrent-wal2)|
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(<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 */