diff options
Diffstat (limited to 'www/isolation.html')
| -rw-r--r-- | www/isolation.html | 370 |
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> + |