1<!--$Id: read.so,v 1.15 2008/01/17 07:58:08 mjc Exp $--> 2<!--Copyright (c) 1997,2008 Oracle. All rights reserved.--> 3<!--See the file LICENSE for redistribution information.--> 4<html> 5<head> 6<title>Berkeley DB Reference Guide: Degrees of isolation</title> 7<meta name="description" content="Berkeley DB: An embedded database programmatic toolkit."> 8<meta name="keywords" content="embedded,database,programmatic,toolkit,btree,hash,hashing,transaction,transactions,locking,logging,access method,access methods,Java,C,C++"> 9</head> 10<body bgcolor=white> 11<table width="100%"><tr valign=top> 12<td><b><dl><dt>Berkeley DB Reference Guide:<dd>Berkeley DB Transactional Data Store Applications</dl></b></td> 13<td align=right><a href="../transapp/inc.html"><img src="../../images/prev.gif" alt="Prev"></a><a href="../toc.html"><img src="../../images/ref.gif" alt="Ref"></a><a href="../transapp/cursor.html"><img src="../../images/next.gif" alt="Next"></a> 14</td></tr></table> 15<p align=center><b>Degrees of isolation</b></p> 16<a name="2"><!--meow--></a> 17<p>Transactions can be isolated from each other to different degrees. 18<i>Serializable</i> provides the most isolation, and means that, for 19the life of the transaction, every time a thread of control reads a data 20item, it will be unchanged from its previous value (assuming, of course, 21the thread of control does not itself modify the item). By default, 22Berkeley DB enforces serializability whenever database reads are wrapped in 23transactions. This is also known as <i>degree 3 isolation</i>.</p> 24<p>Most applications do not need to enclose all reads in transactions, and 25when possible, transactionally protected reads at serializable isolation 26should be avoided as they can cause performance problems. For example, 27a serializable cursor sequentially reading each key/data pair in a 28database, will acquire a read lock on most of the pages in the database 29and so will gradually block all write operations on the databases until 30the transaction commits or aborts. Note, however, that if there are 31update transactions present in the application, the read operations must 32still use locking, and must be prepared to repeat any operation 33(possibly closing and reopening a cursor) that fails with a return value 34of <a href="../../ref/program/errorret.html#DB_LOCK_DEADLOCK">DB_LOCK_DEADLOCK</a>. Applications that need repeatable reads 35are ones that require the ability to repeatedly access a data item 36knowing that it will not have changed (for example, an operation 37modifying a data item based on its existing value).</p> 38<p><i>Snapshot isolation</i> also guarantees repeatable reads, but 39avoids read locks by using multiversion concurrency control (MVCC). 40This makes update operations more expensive, because they have to 41allocate space for new versions of pages in cache and make copies, but 42avoiding read locks can significantly increase throughput for many 43applications. Snapshot isolation is discussed in detail below.</p> 44<a name="3"><!--meow--></a> 45<a name="4"><!--meow--></a> 46<a name="5"><!--meow--></a> 47<a name="6"><!--meow--></a> 48<p>A transaction may only require <i>cursor stability</i>, that is only 49be guaranteed that cursors see committed data that does not change so 50long as it is addressed by the cursor, but may change before the reading 51transaction completes. This is also called <i>degree 2 52isolation</i>. Berkeley DB provides this level of isolation when a transaction 53is started with the <a href="../../api_c/db_cursor.html#DB_READ_COMMITTED">DB_READ_COMMITTED</a> flag. This flag may also 54be specified when opening a cursor within a fully isolated 55transaction.</p> 56<a name="7"><!--meow--></a> 57<a name="8"><!--meow--></a> 58<a name="9"><!--meow--></a> 59<a name="10"><!--meow--></a> 60<p>Berkeley DB optionally supports reading uncommitted data; that is, read 61operations may request data which has been modified but not yet 62committed by another transaction. This is also called <i>degree 631 isolation</i>. This is done by first specifying the 64<a href="../../api_c/db_open.html#DB_READ_UNCOMMITTED">DB_READ_UNCOMMITTED</a> flag when opening the underlying database, 65and then specifying the <a href="../../api_c/db_open.html#DB_READ_UNCOMMITTED">DB_READ_UNCOMMITTED</a> flag when beginning 66a transaction, opening a cursor, or performing a read operation. The 67advantage of using <a href="../../api_c/db_open.html#DB_READ_UNCOMMITTED">DB_READ_UNCOMMITTED</a> is that read operations 68will not block when another transaction holds a write lock on the 69requested data; the disadvantage is that read operations may return data 70that will disappear should the transaction holding the write lock 71abort.</p> 72<a name="11"><!--meow--></a> 73<a name="12"><!--meow--></a> 74<a name="13"><!--meow--></a> 75<b>Snapshot Isolation</b> 76<p>To make use of snapshot isolation, databases must first be configured 77for multiversion access by calling <a href="../../api_c/db_open.html">DB->open</a> with the 78<a href="../../api_c/db_open.html#DB_MULTIVERSION">DB_MULTIVERSION</a> flag. Then transactions or cursors must be 79configured with the <a href="../../api_c/txn_begin.html#DB_TXN_SNAPSHOT">DB_TXN_SNAPSHOT</a> flag.</p> 80<p>When configuring an environment for snapshot isolation, it is important 81to realize that having multiple versions of pages in cache means that 82the working set will take up more of the cache. As a result, snapshot 83isolation is best suited for use with larger cache sizes.</p> 84<p>If the cache becomes full of page copies before the old copies can be 85discarded, additional I/O will occur as pages are written to temporary 86"freezer" files. This can substantially reduce throughput, and should 87be avoided if possible by configuring a large cache and keeping snapshot 88isolation transactions short. The amount of cache required to avoid 89freezing buffers can be estimated by taking a checkpoint followed by a 90call to <a href="../../api_c/log_archive.html">DB_ENV->log_archive</a>. The amount of cache required is 91approximately double the size of logs that remains.</p> 92<p>The environment should also be configured for sufficient transactions 93using <a href="../../api_c/env_set_tx_max.html">DB_ENV->set_tx_max</a>. The maximum number of transactions 94needs to include all transactions executed concurrently by the 95application plus all cursors configured for snapshot isolation. 96Further, the transactions are retained until the last page they created 97is evicted from cache, so in the extreme case, an additional transaction 98may be needed for each page in the cache. Note that cache sizes under 99500MB are increased by 25%, so the calculation of number of pages needs 100to take this into account.</p> 101<p>So when <i>should</i> applications use snapshot isolation? 102<p><ul type=disc> 103<li>There is a large cache relative to size of updates performed by 104concurrent transactions; and 105<li>Read/write contention is limiting the throughput of the application; 106or 107<li>The application is all or mostly read-only, and contention for the lock 108manager mutex is limiting throughput. 109</ul></p> 110<p>The simplest way to take advantage of snapshot isolation is for queries: 111keep update transactions using full read/write locking and set 112<a href="../../api_c/txn_begin.html#DB_TXN_SNAPSHOT">DB_TXN_SNAPSHOT</a> on read-only transactions or cursors. This 113should minimize blocking of snapshot isolation transactions and will 114avoid introducing new <a href="../../ref/program/errorret.html#DB_LOCK_DEADLOCK">DB_LOCK_DEADLOCK</a> errors.</p> 115<p>If the application has update transactions which read many items and 116only update a small set (for example, scanning until a desired record is 117found, then modifying it), throughput may be improved by running some 118updates at snapshot isolation as well.</p> 119<table width="100%"><tr><td><br></td><td align=right><a href="../transapp/inc.html"><img src="../../images/prev.gif" alt="Prev"></a><a href="../toc.html"><img src="../../images/ref.gif" alt="Ref"></a><a href="../transapp/cursor.html"><img src="../../images/next.gif" alt="Next"></a> 120</td></tr></table> 121<p><font size=1>Copyright (c) 1996,2008 Oracle. All rights reserved.</font> 122</body> 123</html> 124