Adding upstream version 3.45.1.upstream/3.45.1

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
author: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-04-13 14:07:11 +0000
committer: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-04-13 14:07:11 +0000
commit: 63847496f14c813a5d80efd5b7de0f1294ffe1e3 (patch)
tree: 01c7571c7c762ceee70638549a99834fdd7c411b /www/isolation.html
parent: Initial commit. (diff)
download: sqlite3-63847496f14c813a5d80efd5b7de0f1294ffe1e3.tar.xz
sqlite3-63847496f14c813a5d80efd5b7de0f1294ffe1e3.zip
1 files changed, 370 insertions, 0 deletions
diff --git a/www/isolation.html b/www/isolation.html
new file mode 100644
index 0000000..c45e37a
--- /dev/null
+++ b/www/isolation.html
@@ -0,0 +1,370 @@
+<!DOCTYPE html>
+<html><head>
+<meta name="viewport" content="width=device-width, initial-scale=1.0">
+<meta http-equiv="content-type" content="text/html; charset=UTF-8">
+<link href="sqlite.css" rel="stylesheet">
+<title>Isolation In SQLite</title>
+<!-- path= -->
+</head>
+<body>
+<div class=nosearch>
+<a href="index.html">
+<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
+</a>
+<div><!-- IE hack to prevent disappearing logo --></div>
+<div class="tagline desktoponly">
+Small. Fast. Reliable.<br>Choose any three.
+</div>
+<div class="menu mainmenu">
+<ul>
+<li><a href="index.html">Home</a>
+<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
+<li class='wideonly'><a href='about.html'>About</a>
+<li class='desktoponly'><a href="docs.html">Documentation</a>
+<li class='desktoponly'><a href="download.html">Download</a>
+<li class='wideonly'><a href='copyright.html'>License</a>
+<li class='desktoponly'><a href="support.html">Support</a>
+<li class='desktoponly'><a href="prosupport.html">Purchase</a>
+<li class='search' id='search_menubutton'>
+<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
+</ul>
+</div>
+<div class="menu submenu" id="submenu">
+<ul>
+<li><a href='about.html'>About</a>
+<li><a href='docs.html'>Documentation</a>
+<li><a href='download.html'>Download</a>
+<li><a href='support.html'>Support</a>
+<li><a href='prosupport.html'>Purchase</a>
+</ul>
+</div>
+<div class="searchmenu" id="searchmenu">
+<form method="GET" action="search">
+<select name="s" id="searchtype">
+<option value="d">Search Documentation</option>
+<option value="c">Search Changelog</option>
+</select>
+<input type="text" name="q" id="searchbox" value="">
+<input type="submit" value="Go">
+</form>
+</div>
+</div>
+<script>
+function toggle_div(nm) {
+var w = document.getElementById(nm);
+if( w.style.display=="block" ){
+w.style.display = "none";
+}else{
+w.style.display = "block";
+}
+}
+function toggle_search() {
+var w = document.getElementById("searchmenu");
+if( w.style.display=="block" ){
+w.style.display = "none";
+} else {
+w.style.display = "block";
+setTimeout(function(){
+document.getElementById("searchbox").focus()
+}, 30);
+}
+}
+function div_off(nm){document.getElementById(nm).style.display="none";}
+window.onbeforeunload = function(e){div_off("submenu");}
+/* Disable the Search feature if we are not operating from CGI, since */
+/* Search is accomplished using CGI and will not work without it. */
+if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
+document.getElementById("search_menubutton").style.display = "none";
+}
+/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
+function hideorshow(btn,obj){
+var x = document.getElementById(obj);
+var b = document.getElementById(btn);
+if( x.style.display!='none' ){
+x.style.display = 'none';
+b.innerHTML='show';
+}else{
+x.style.display = '';
+b.innerHTML='hide';
+}
+return false;
+}
+var antiRobot = 0;
+function antiRobotGo(){
+if( antiRobot!=3 ) return;
+antiRobot = 7;
+var j = document.getElementById("mtimelink");
+if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
+}
+function antiRobotDefense(){
+document.body.onmousedown=function(){
+antiRobot |= 2;
+antiRobotGo();
+document.body.onmousedown=null;
+}
+document.body.onmousemove=function(){
+antiRobot |= 2;
+antiRobotGo();
+document.body.onmousemove=null;
+}
+setTimeout(function(){
+antiRobot |= 1;
+antiRobotGo();
+}, 100)
+antiRobotGo();
+}
+antiRobotDefense();
+</script>
+
+
+
+<h1 align="center">
+Isolation In SQLite
+</h1>
+
+<p>
+The "isolation" property of a database determines when changes made to 
+the database by one operation become visible to other concurrent operations.
+</p>
+
+<h2>Isolation Between Database Connections</h2>
+
+<p>
+If the same database is being read and written using two different
+<a href="c3ref/sqlite3.html">database connections</a> (two different <a href="c3ref/sqlite3.html">sqlite3</a> objects returned by
+separate calls to <a href="c3ref/open.html">sqlite3_open()</a>) and the two database connections
+do not have a <a href="sharedcache.html">shared cache</a>, then the reader is only able to
+see complete committed transactions from the writer.  Partial changes
+by the writer that have not been committed are invisible to the reader.
+This is true regardless of whether the two database connections are in
+the same thread, in different threads of the same process, or in
+different processes.  This
+is the usual and expected behavior for SQL database systems.
+</p>
+
+<p>
+The previous paragraph is also true (separate database connections are
+isolated from one another) in <a href="sharedcache.html">shared cache mode</a> as long as the
+<a href="pragma.html#pragma_read_uncommitted">read_uncommitted pragma</a> remains turned off.  The <a href="pragma.html#pragma_read_uncommitted">read_uncommitted pragma</a>
+is off by default and so if the application does nothing to turn it on, 
+it will remain off.  Hence, unless the <a href="pragma.html#pragma_read_uncommitted">read_uncommitted pragma</a> is used
+to change the default behavior, changes made by one database connection
+are invisible to readers on a different database connection sharing the
+same cache until the writer commits its transaction.
+</p>
+
+<p>
+If two database connections share the same cache and the reader has 
+enabled the <a href="pragma.html#pragma_read_uncommitted">read_uncommitted pragma</a>, then the reader will be able to
+see changes made by the writer before the writer transaction commits.
+The combined use of <a href="sharedcache.html">shared cache mode</a> and the <a href="pragma.html#pragma_read_uncommitted">read_uncommitted pragma</a> 
+is the only way that one database connection can see uncommitted changes
+on a different database connection.  In all other circumstances, separate
+database connections are completely isolated from one another.
+</p>
+
+<p>Except in the case of <a href="sharedcache.html">shared cache</a> database connections with
+<a href="pragma.html#pragma_read_uncommitted">PRAGMA read_uncommitted</a> turned on, all transactions in SQLite show
+"serializable" isolation.  SQLite implements serializable transactions
+by actually serializing the writes.  There can only be a single writer
+at a time to an SQLite database.  There can be multiple database connections
+open at the same time, and all of those database connections can write
+to the database file, but they have to take turns.  SQLite uses locks
+to serialize the writes automatically; this is not something that
+the applications using SQLite need to worry about.</p>
+
+
+<h2>Isolation And Concurrency</h2>
+
+<p>
+SQLite implements isolation and concurrency control (and atomicity) using
+transient journal files that appear in the same directory as the database file.
+There are two major "journal modes".
+The older "rollback mode" corresponds to using the "DELETE", "PERSIST",
+or "TRUNCATE" options to the <a href="pragma.html#pragma_journal_mode">journal_mode pragma</a>.  In rollback mode,
+changes are written directly into the database file, while simultaneously
+a separate rollback journal file is constructed that is able to restore
+the database to its original state if the transaction rolls back.
+Rollback mode (specifically DELETE mode, meaning that the rollback journal
+is deleted from disk at the conclusion of each transaction) is the current
+default behavior.
+</p>
+
+<p>Since <a href="releaselog/3_7_0.html">version 3.7.0</a> (2010-07-21), 
+SQLite also supports "<a href="wal.html">WAL mode</a>".  In WAL mode,
+changes are not written to the original database file.  Instead, changes
+go into a separate "write-ahead log" or "WAL" file.  
+Later, after the transaction
+commits, those changes will be moved from the WAL file back into the
+original database in an operation called "checkpoint".  WAL mode is
+enabled by running "<a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode=WAL</a>".
+</p>
+
+<p>
+In rollback mode, SQLite implements isolation by locking the database
+file and preventing any reads by other database connections
+while each write transaction is underway.
+Readers can be active at the beginning of a write, before any content
+is flushed to disk and while all changes are still held in the writer's
+private memory space.  But before any changes are made to the database file
+on disk, all readers must be (temporarily) expelled in order to give the writer
+exclusive access to the database file.  
+Hence, readers are prohibited from seeing incomplete
+transactions by virtue of being locked out of the database while the
+transaction is being written to disk.  Only after the transaction is
+completely written and synced to disk and committed are the readers allowed
+back into the database.  Hence readers never get a chance to see partially
+written changes.
+</p>
+
+<p>
+WAL mode permits simultaneous readers and writers.  It can do this because
+changes do not overwrite the original database file, but rather go
+into the separate write-ahead log file.  That means that readers can continue
+to read the old, original, unaltered content from the original database file
+at the same time that the writer is appending to the write-ahead log.
+In <a href="wal.html">WAL mode</a>, SQLite exhibits "snapshot isolation".  When a read transaction
+starts, that reader continues to see an unchanging "snapshot" of the database
+file as it existed at the moment in time when the read transaction started.
+Any write transactions that commit while the read transaction is
+active are still invisible to the read transaction, because the reader is
+seeing a snapshot of database file from a prior moment in time.
+</p>
+
+<p>
+An example:  Suppose there are two database connections X and Y.  X starts
+a read transaction using <a href="lang_transaction.html">BEGIN</a> followed by one or more <a href="lang_select.html">SELECT</a> statements.
+Then Y comes along and runs an <a href="lang_update.html">UPDATE</a> statement to modify the database.
+X can subsequently do a <a href="lang_select.html">SELECT</a> against the records that Y modified but
+X will see the older unmodified entries because Y's changes are all
+invisible to X while X is holding a read transaction.  If X wants to see
+the changes that Y made, then X must end its read transaction and
+start a new one (by running <a href="lang_transaction.html">COMMIT</a> followed by another <a href="lang_transaction.html">BEGIN</a>.)
+</p>
+
+<p>
+Another example: X starts a read transaction using <a href="lang_transaction.html">BEGIN</a> and <a href="lang_select.html">SELECT</a>, then
+Y makes a changes to the database using <a href="lang_update.html">UPDATE</a>.  Then X tries to make a
+change to the database using <a href="lang_update.html">UPDATE</a>.  The attempt by X to escalate its
+transaction from a read transaction to a write transaction fails with an
+<a href="rescode.html#busy_snapshot">SQLITE_BUSY_SNAPSHOT</a> error because the snapshot of the database being
+viewed by X is no longer the latest version of the database.  If X were
+allowed to write, it would fork the history of the database file, which is
+something SQLite does not support.  In order for X to write to the database,
+it must first release its snapshot (using <a href="lang_transaction.html">ROLLBACK</a> for example) then
+start a new transaction with a subsequent <a href="lang_transaction.html">BEGIN</a>.
+</p>
+
+<p>
+If X starts a transaction that will initially only read but X knows it
+will eventually want to write and does not want to be troubled with
+possible SQLITE_BUSY_SNAPSHOT errors that arise because another connection
+jumped ahead of it in line, then X can issue <a href="lang_transaction.html#immediate">BEGIN IMMEDIATE</a> to start
+its transaction instead of just an ordinary BEGIN.  The <a href="lang_transaction.html#immediate">BEGIN IMMEDIATE</a>
+command goes ahead and starts a write transaction, and thus blocks all
+other writers.  If the <a href="lang_transaction.html#immediate">BEGIN IMMEDIATE</a> operation succeeds, then no
+subsequent operations in that transaction will ever fail with an
+<a href="rescode.html#busy">SQLITE_BUSY</a> error.
+</p>
+
+<h2>No Isolation Between Operations On The Same Database Connection</h2>
+
+<p>SQLite provides isolation between operations in separate database
+connections.  However, there is no isolation between operations that
+occur within the same database connection.</p>
+
+<p>In other words, if X begins a write transaction using <a href="lang_transaction.html#immediate">BEGIN IMMEDIATE</a>
+then issues one or more <a href="lang_update.html">UPDATE</a>, <a href="lang_delete.html">DELETE</a>, and/or <a href="lang_insert.html">INSERT</a>
+statements, then those changes are visible to subsequent <a href="lang_select.html">SELECT</a> statements
+that are evaluated in database connection X.  <a href="lang_select.html">SELECT</a> statements on
+a different  database connection Y will show no changes until the X
+transaction commits.  But <a href="lang_select.html">SELECT</a> statements in X will show the changes
+prior to the commit.</p>
+
+<p>Within a single database connection X, a SELECT statement always sees all
+changes to the database that are completed prior to the start of the SELECT
+statement, whether committed or uncommitted.  And the SELECT statement
+obviously does not see any changes that occur after the SELECT statement
+completes.  But what about changes that occur while the SELECT statement
+is running?  What if a SELECT statement is started and the <a href="c3ref/step.html">sqlite3_step()</a>
+interface steps through roughly half of its output, then some <a href="lang_update.html">UPDATE</a>
+statements are run by the application that modify the table that the
+SELECT statement is reading, then more calls to <a href="c3ref/step.html">sqlite3_step()</a> are made
+to finish out the SELECT statement?  Will the later steps of the SELECT
+statement see the changes made by the UPDATE or not?  The answer is that
+this behavior is undefined.  In particular, whether or not the SELECT statement
+sees the concurrent changes depends on which release of SQLite is
+running, the schema of the database file, whether or not <a href="lang_analyze.html">ANALYZE</a> has
+been run, and the details of the query.  In some cases, it might depend
+on the content of the database file, too.  There is no good way to know whether
+or not a SELECT statement will see changes that were made to the database
+by the same database connection after the SELECT statement was started.
+And hence, developers should diligently avoid writing applications
+that make assumptions about what will occur in that circumstance.</p>
+
+<p>
+If an application issues a SELECT statement on a single table like
+"<i>SELECT rowid, * FROM table WHERE ...</i>" and starts stepping through 
+the output of that statement using <a href="c3ref/step.html">sqlite3_step()</a> and examining each
+row, then it is safe for the application to delete the current row or
+any prior row using "DELETE FROM table WHERE rowid=?".  It is also safe
+(in the sense that it will not harm the database) for the application to
+delete a row that expected to appear later in the query but has not
+appeared yet.  If a future row is deleted, however, it might happen that
+the row turns up after a subsequent sqlite3_step(), even after it has
+allegedly been deleted.  Or it might not.  That behavior is undefined.
+The application can 
+also INSERT new rows into the table while the SELECT statement is 
+running, but whether or not the new rows appear
+in subsequent sqlite3_step()s of the query is undefined.  And the application
+can UPDATE the current row or any prior row, though doing so might cause 
+that row to reappear in a subsequent sqlite3_step().  As long as the 
+application is prepared to deal with these ambiguities, the operations 
+themselves are safe and will not harm the database file.</p>
+
+<p>
+For the purposes of the previous two paragraphs, two database connections
+that have the same <a href="sharedcache.html">shared cache</a> and which have enabled
+<a href="pragma.html#pragma_read_uncommitted">PRAGMA read_uncommitted</a> are considered to be the same database connection.
+</p>
+
+<h2>Summary</h2>
+
+<ol>
+<li><p>
+Transactions in SQLite are SERIALIZABLE.
+</p>
+
+<li><p>
+Changes made in one database connection are invisible to all other database
+connections prior to commit.
+</p>
+
+<li><p>
+A query sees all changes that are completed on the same database connection
+prior to the start of the query, regardless of whether or not those changes
+have been committed.
+</p>
+
+<li><p>
+If changes occur on the same database connection after a query 
+starts running but before the query completes, then it is undefined whether 
+or not the query will see those changes.
+</p>
+
+<li><p>
+If changes occur on the same database connection after a query 
+starts running but before the query completes, then the query might return
+a changed row more than once, or it might return a row that was previously
+deleted.
+</p>
+
+<li><p>
+For the purposes of the previous four items, two database connections that 
+use the same <a href="sharedcache.html">shared cache</a> and which enable <a href="pragma.html#pragma_read_uncommitted">PRAGMA read_uncommitted</a> are
+considered to be the same database connection, not separate database
+connections.
+</p>
+</ol>
+<p align="center"><small><i>This page last modified on  <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink"  data-href="https://sqlite.org/docsrc/finfo/pages/isolation.in?m=aa8b8e3ef6">2022-04-18 02:55:50</a> UTC </small></i></p>
+
author	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-04-13 14:07:11 +0000
committer	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-04-13 14:07:11 +0000
commit	63847496f14c813a5d80efd5b7de0f1294ffe1e3 (patch)
tree	01c7571c7c762ceee70638549a99834fdd7c411b /www/isolation.html
parent	Initial commit. (diff)
download	sqlite3-63847496f14c813a5d80efd5b7de0f1294ffe1e3.tar.xz sqlite3-63847496f14c813a5d80efd5b7de0f1294ffe1e3.zip