1<?xml version="1.0" encoding="UTF-8" standalone="no"?> 2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 3<html xmlns="http://www.w3.org/1999/xhtml"> 4 <head> 5 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> 6 <title>Degrees of isolation</title> 7 <link rel="stylesheet" href="gettingStarted.css" type="text/css" /> 8 <meta name="generator" content="DocBook XSL Stylesheets V1.73.2" /> 9 <link rel="start" href="index.html" title="Berkeley DB Programmer's Reference Guide" /> 10 <link rel="up" href="transapp.html" title="Chapter 11. Berkeley DB Transactional Data Store Applications" /> 11 <link rel="prev" href="transapp_inc.html" title="Isolation" /> 12 <link rel="next" href="transapp_cursor.html" title="Transactional cursors" /> 13 </head> 14 <body> 15 <div class="navheader"> 16 <table width="100%" summary="Navigation header"> 17 <tr> 18 <th colspan="3" align="center">Degrees of isolation</th> 19 </tr> 20 <tr> 21 <td width="20%" align="left"><a accesskey="p" href="transapp_inc.html">Prev</a> </td> 22 <th width="60%" align="center">Chapter 11. 23 Berkeley DB Transactional Data Store Applications 24 </th> 25 <td width="20%" align="right"> <a accesskey="n" href="transapp_cursor.html">Next</a></td> 26 </tr> 27 </table> 28 <hr /> 29 </div> 30 <div class="sect1" lang="en" xml:lang="en"> 31 <div class="titlepage"> 32 <div> 33 <div> 34 <h2 class="title" style="clear: both"><a id="transapp_read"></a>Degrees of isolation</h2> 35 </div> 36 </div> 37 </div> 38 <div class="toc"> 39 <dl> 40 <dt> 41 <span class="sect2"> 42 <a href="transapp_read.html#id1609658">Snapshot Isolation</a> 43 </span> 44 </dt> 45 </dl> 46 </div> 47 <p>Transactions can be isolated from each other to different degrees. 48<span class="emphasis"><em>Serializable</em></span> provides the most isolation, and means that, for 49the life of the transaction, every time a thread of control reads a data 50item, it will be unchanged from its previous value (assuming, of course, 51the thread of control does not itself modify the item). By default, 52Berkeley DB enforces serializability whenever database reads are wrapped in 53transactions. This is also known as <span class="emphasis"><em>degree 3 isolation</em></span>.</p> 54 <p>Most applications do not need to enclose all reads in transactions, and 55when possible, transactionally protected reads at serializable isolation 56should be avoided as they can cause performance problems. For example, 57a serializable cursor sequentially reading each key/data pair in a 58database, will acquire a read lock on most of the pages in the database 59and so will gradually block all write operations on the databases until 60the transaction commits or aborts. Note, however, that if there are 61update transactions present in the application, the read operations must 62still use locking, and must be prepared to repeat any operation 63(possibly closing and reopening a cursor) that fails with a return value 64of <a class="link" href="program_errorret.html#program_errorret.DB_LOCK_DEADLOCK">DB_LOCK_DEADLOCK</a>. 65Applications that need repeatable reads 66are ones that require the ability to repeatedly access a data item 67knowing that it will not have changed (for example, an operation 68modifying a data item based on its existing value).</p> 69 <p><span class="emphasis"><em>Snapshot isolation</em></span> also guarantees repeatable reads, but 70avoids read locks by using multiversion concurrency control (MVCC). 71This makes update operations more expensive, because they have to 72allocate space for new versions of pages in cache and make copies, but 73avoiding read locks can significantly increase throughput for many 74applications. Snapshot isolation is discussed in detail below.</p> 75 <p>A transaction may only require 76<span class="emphasis"><em>cursor stability</em></span>, that is only 77be guaranteed that cursors see committed data that does not change so 78long as it is addressed by the cursor, but may change before the reading 79transaction completes. This is also called <span class="emphasis"><em>degree 2 80isolation</em></span>. Berkeley DB provides this level of isolation when a transaction 81is started with the <a href="../api_reference/C/dbcget.html#dbcget_DB_READ_COMMITTED" class="olink">DB_READ_COMMITTED</a> flag. This flag may also 82be specified when opening a cursor within a fully isolated 83transaction.</p> 84 <p>Berkeley DB optionally supports reading uncommitted data; that is, read 85operations may request data which has been modified but not yet 86committed by another transaction. This is also called <span class="emphasis"><em>degree 871 isolation</em></span>. This is done by first specifying the 88<a href="../api_reference/C/dbopen.html#dbopen_DB_READ_UNCOMMITTED" class="olink">DB_READ_UNCOMMITTED</a> flag when opening the underlying database, 89and then specifying the <a href="../api_reference/C/dbopen.html#dbopen_DB_READ_UNCOMMITTED" class="olink">DB_READ_UNCOMMITTED</a> flag when beginning 90a transaction, opening a cursor, or performing a read operation. The 91advantage of using <a href="../api_reference/C/dbopen.html#dbopen_DB_READ_UNCOMMITTED" class="olink">DB_READ_UNCOMMITTED</a> is that read operations 92will not block when another transaction holds a write lock on the 93requested data; the disadvantage is that read operations may return data 94that will disappear should the transaction holding the write lock 95abort.</p> 96 <div class="sect2" lang="en" xml:lang="en"> 97 <div class="titlepage"> 98 <div> 99 <div> 100 <h3 class="title"><a id="id1609658"></a>Snapshot Isolation</h3> 101 </div> 102 </div> 103 </div> 104 <p>To make use of snapshot isolation, databases must first be configured 105for multiversion access by calling <a href="../api_reference/C/dbopen.html" class="olink">DB->open()</a> with the 106<a href="../api_reference/C/dbopen.html#dbopen_DB_MULTIVERSION" class="olink">DB_MULTIVERSION</a> flag. Then transactions or cursors must be 107configured with the <a href="../api_reference/C/txnbegin.html#txnbegin_DB_TXN_SNAPSHOT" class="olink">DB_TXN_SNAPSHOT</a> flag.</p> 108 <p>When configuring an environment for snapshot isolation, it is important 109to realize that having multiple versions of pages in cache means that 110the working set will take up more of the cache. As a result, snapshot 111isolation is best suited for use with larger cache sizes.</p> 112 <p>If the cache becomes full of page copies before the old copies can be 113discarded, additional I/O will occur as pages are written to temporary 114"freezer" files. This can substantially reduce throughput, and should 115be avoided if possible by configuring a large cache and keeping snapshot 116isolation transactions short. The amount of cache required to avoid 117freezing buffers can be estimated by taking a checkpoint followed by a 118call to <a href="../api_reference/C/logarchive.html" class="olink">DB_ENV->log_archive()</a>. The amount of cache required is 119approximately double the size of logs that remains.</p> 120 <p>The environment should also be configured for sufficient transactions 121using <a href="../api_reference/C/envset_tx_max.html" class="olink">DB_ENV->set_tx_max()</a>. The maximum number of transactions 122needs to include all transactions executed concurrently by the 123application plus all cursors configured for snapshot isolation. 124Further, the transactions are retained until the last page they created 125is evicted from cache, so in the extreme case, an additional transaction 126may be needed for each page in the cache. Note that cache sizes under 127500MB are increased by 25%, so the calculation of number of pages needs 128to take this into account.</p> 129 <p>So when <span class="emphasis"><em>should</em></span> applications use snapshot isolation?</p> 130 <div class="itemizedlist"> 131 <ul type="disc"> 132 <li>There is a large cache relative to size of updates performed by 133concurrent transactions; and</li> 134 <li>Read/write contention is limiting the throughput of the application; 135or</li> 136 <li>The application is all or mostly read-only, and contention for the lock 137manager mutex is limiting throughput.</li> 138 </ul> 139 </div> 140 <p>The simplest way to take advantage of snapshot isolation is for queries: 141keep update transactions using full read/write locking and set 142<a href="../api_reference/C/txnbegin.html#txnbegin_DB_TXN_SNAPSHOT" class="olink">DB_TXN_SNAPSHOT</a> on read-only transactions or cursors. This 143should minimize blocking of snapshot isolation transactions and will 144avoid introducing new 145<a class="link" href="program_errorret.html#program_errorret.DB_LOCK_DEADLOCK">DB_LOCK_DEADLOCK</a> 146errors.</p> 147 <p>If the application has update transactions which read many items and 148only update a small set (for example, scanning until a desired record is 149found, then modifying it), throughput may be improved by running some 150updates at snapshot isolation as well.</p> 151 </div> 152 </div> 153 <div class="navfooter"> 154 <hr /> 155 <table width="100%" summary="Navigation footer"> 156 <tr> 157 <td width="40%" align="left"><a accesskey="p" href="transapp_inc.html">Prev</a> </td> 158 <td width="20%" align="center"> 159 <a accesskey="u" href="transapp.html">Up</a> 160 </td> 161 <td width="40%" align="right"> <a accesskey="n" href="transapp_cursor.html">Next</a></td> 162 </tr> 163 <tr> 164 <td width="40%" align="left" valign="top">Isolation </td> 165 <td width="20%" align="center"> 166 <a accesskey="h" href="index.html">Home</a> 167 </td> 168 <td width="40%" align="right" valign="top"> Transactional cursors</td> 169 </tr> 170 </table> 171 </div> 172 </body> 173</html> 174