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>Isolation</title> 7 <link rel="stylesheet" href="gettingStarted.css" type="text/css" /> 8 <meta name="generator" content="DocBook XSL Stylesheets V1.62.4" /> 9 <link rel="home" href="index.html" title="Getting Started with Berkeley DB Transaction Processing" /> 10 <link rel="up" href="txnconcurrency.html" title="Chapter 4. Concurrency" /> 11 <link rel="previous" href="lockingsubsystem.html" title="The Locking Subsystem" /> 12 <link rel="next" href="txn_ccursor.html" title="Transactional Cursors and Concurrent Applications" /> 13 </head> 14 <body> 15 <div class="navheader"> 16 <table width="100%" summary="Navigation header"> 17 <tr> 18 <th colspan="3" align="center">Isolation</th> 19 </tr> 20 <tr> 21 <td width="20%" align="left"><a accesskey="p" href="lockingsubsystem.html">Prev</a> </td> 22 <th width="60%" align="center">Chapter 4. Concurrency</th> 23 <td width="20%" align="right"> <a accesskey="n" href="txn_ccursor.html">Next</a></td> 24 </tr> 25 </table> 26 <hr /> 27 </div> 28 <div class="sect1" lang="en" xml:lang="en"> 29 <div class="titlepage"> 30 <div> 31 <div> 32 <h2 class="title" style="clear: both"><a id="isolation"></a>Isolation</h2> 33 </div> 34 </div> 35 <div></div> 36 </div> 37 <p> 38 Isolation guarantees are an important aspect of transactional 39 protection. Transactions 40 ensure the data your transaction is working with will not be changed by some other transaction. 41 Moreover, the modifications made by a transaction will never be viewable outside of that transaction until 42 the changes have been committed. 43 </p> 44 <p> 45 That said, there are different degrees of isolation, and you can choose to relax your isolation 46 guarantees to one degree or another depending on your application's requirements. The primary reason why 47 you might want to do this is because of performance; the more isolation you ask your transactions to 48 provide, the more locking that your application must do. With more locking comes a greater chance of 49 blocking, which in turn causes your threads to pause while waiting for a lock. Therefore, by relaxing 50 your isolation guarantees, you can <span class="emphasis"><em>potentially</em></span> improve your application's throughput. 51 Whether you actually see any improvement depends, of course, on 52 the nature of your application's data and transactions. 53 </p> 54 <div class="sect2" lang="en" xml:lang="en"> 55 <div class="titlepage"> 56 <div> 57 <div> 58 <h3 class="title"><a id="degreesofisolation"></a>Supported Degrees of Isolation</h3> 59 </div> 60 </div> 61 <div></div> 62 </div> 63 <p> 64 DB supports the following levels of isolation: 65 </p> 66 <div class="informaltable"> 67 <table border="1" width="80%"> 68 <colgroup> 69 <col /> 70 <col /> 71 <col /> 72 </colgroup> 73 <thead> 74 <tr> 75 <th>Degree</th> 76 <th>ANSI Term</th> 77 <th>Definition</th> 78 </tr> 79 </thead> 80 <tbody> 81 <tr> 82 <td>1</td> 83 <td>READ UNCOMMITTED</td> 84 <td> 85 Uncommitted reads means that one transaction will never 86 overwrite another transaction's dirty data. Dirty data is 87 data that a transaction has modified but not yet committed 88 to the underlying data store. However, uncommitted reads allows a 89 transaction to see data dirtied by another 90 transaction. In addition, a transaction may read data 91 dirtied by another transaction, but which subsequently 92 is aborted by that other transaction. In this latter 93 case, the reading transaction may be reading data that 94 never really existed in the database. 95 </td> 96 </tr> 97 <tr> 98 <td>2</td> 99 <td>READ COMMITTED</td> 100 <td> 101 <p> 102 Committed read isolation means that degree 1 is observed, except that dirty data is never read. 103 </p> 104 <p> 105 In addition, this isolation level guarantees that data will never change so long as 106 it is addressed by the cursor, but the data may change before the reading cursor is closed. 107 In the case of a transaction, data at the current 108 cursor position will not change, but once the cursor 109 moves, the previous referenced data can change. This 110 means that readers release read locks before the cursor 111 is closed, and therefore, before the transaction 112 completes. Note that this level of isolation causes the 113 cursor to operate in exactly the same way as it does in 114 the absence of a transaction. 115 </p> 116 </td> 117 </tr> 118 <tr> 119 <td>3</td> 120 <td>SERIALIZABLE</td> 121 <td> 122 <p> 123 Committed read is observed, plus the data read by a transaction, T, 124 will never be dirtied by another transaction before T completes. 125 This means that both read and write locks are not 126 released until the transaction completes. 127 </p> 128 <p> 129 <span> 130 In addition, 131 </span> 132 133 134 135 no transactions will see phantoms. Phantoms are records 136 returned as a result of a search, but which were not seen by 137 the same transaction when the identical 138 search criteria was previously used. 139 </p> 140 <p> 141 This is DB's default isolation guarantee. 142 </p> 143 </td> 144 </tr> 145 </tbody> 146 </table> 147 </div> 148 <p> 149 150 By default, DB transactions and transactional cursors offer 151 <span> 152 serializable isolation. 153 </span> 154 155 156 You can optionally reduce your isolation level by configuring DB to use 157 uncommitted read isolation. See 158 <a href="isolation.html#dirtyreads">Reading Uncommitted Data</a> 159 for more information. 160 161 You can also configure DB to use committed read isolation. See 162 <a href="isolation.html#readcommitted">Committed Reads</a> 163 for more information. 164 165 </p> 166 <p> 167 Finally, in addition to DB's normal degrees of isolation, you 168 can also use <span class="emphasis"><em>snapshot isolation</em></span>. This allows 169 you to avoid the read locks that serializable isolation requires. See 170 <a href="isolation.html#snapshot_isolation">Using Snapshot Isolation</a> 171 for details. 172 </p> 173 </div> 174 <div class="sect2" lang="en" xml:lang="en"> 175 <div class="titlepage"> 176 <div> 177 <div> 178 <h3 class="title"><a id="dirtyreads"></a>Reading Uncommitted Data</h3> 179 </div> 180 </div> 181 <div></div> 182 </div> 183 <p> 184 You can configure your application to read data that has been modified but not yet 185 committed by another transaction; that is, dirty data. When you do this, you 186 may see a performance benefit by allowing your 187 application to not have to block waiting for write locks. On the other hand, the data that your 188 application is reading may change before the transaction has completed. 189 </p> 190 <p> 191 When used with transactions, uncommitted reads means that one transaction can see data 192 modified but not yet committed by another transaction. When 193 used with transactional cursors, uncommitted reads means 194 that any database reader can see data modified by the 195 cursor before the cursor's transaction has committed. 196 </p> 197 <p> 198 Because of this, uncommitted reads allow a transaction to read data 199 that may subsequently be aborted by another transaction. In 200 this case, the reading transaction will have read data that 201 never really existed in the database. 202 </p> 203 <p> 204 To configure your application to read uncommitted data: 205 </p> 206 <div class="orderedlist"> 207 <ol type="1"> 208 <li> 209 <p> 210 Open your database such that it will allow uncommitted reads. You do this by 211 <span> 212 specifying <tt class="literal">DB_READ_UNCOMMITTED</tt> when you open your database. 213 </span> 214 215 </p> 216 </li> 217 <li> 218 <p> 219 <span> 220 Specify <tt class="literal">DB_READ_UNCOMMITTED</tt> 221 when you create the transaction, 222 <span> 223 open the cursor, or read a record from the database. 224 </span> 225 226 </span> 227 228 229 </p> 230 </li> 231 </ol> 232 </div> 233 <p> 234 For example, the following opens the database such that it supports uncommitted reads, and then creates a 235 transaction that causes all reads performed within it to use uncommitted reads. Remember that simply opening 236 the database to support uncommitted reads is not enough; you must also declare your read operations to be 237 performed using uncommitted reads. 238 </p> 239 <pre class="programlisting">#include "db_cxx.h" 240 241... 242 243int main(void) 244{ 245 u_int32_t env_flags = DB_CREATE | // If the environment does not 246 // exist, create it. 247 DB_INIT_LOCK | // Initialize locking 248 DB_INIT_LOG | // Initialize logging 249 DB_INIT_MPOOL | // Initialize the cache 250 DB_THREAD | // Free-thread the env handle 251 DB_INIT_TXN; // Initialize transactions 252 253 u_int32_t db_flags = DB_CREATE | // Create the db if it does 254 // not exist 255 DB_AUTO_COMMIT | // Enable auto commit 256 DB_READ_UNCOMMITTED; // Enable uncommitted reads 257 258 Db *dbp = NULL; 259 const char *file_name = "mydb.db"; 260 const char *keystr ="thekey"; 261 const char *datastr = "thedata"; 262 263 std::string envHome("/export1/testEnv"); 264 DbEnv myEnv(0); 265 266 try { 267 268 myEnv.open(envHome.c_str(), env_flags, 0); 269 dbp = new Db(&myEnv, 0); 270 dbp->open(NULL, // Txn pointer 271 file_name, // File name 272 NULL, // Logical db name 273 DB_BTREE, // Database type (using btree) 274 db_flags, // Open flags 275 0); // File mode. Using defaults 276 277 DbTxn *txn = NULL; 278 myEnv.txn_begin(NULL, &txn, DB_READ_UNCOMMITTED); 279 280 // From here, you perform your database reads and writes as normal, 281 // committing and aborting the transactions as is necessary, and 282 // testing for deadlock exceptions as normal (omitted for brevity). 283 284 ...</pre> 285 </div> 286 <div class="sect2" lang="en" xml:lang="en"> 287 <div class="titlepage"> 288 <div> 289 <div> 290 <h3 class="title"><a id="readcommitted"></a>Committed Reads</h3> 291 </div> 292 </div> 293 <div></div> 294 </div> 295 <p> 296 You can configure your transaction so that the data being 297 read by a transactional cursor is consistent so long as it 298 is being addressed by the cursor. However, once the cursor is done reading the 299 300 <span> 301 record (that is, reading records from the page that it currently has locked), 302 </span> 303 304 the cursor releases its lock on that 305 306 <span> 307 record or page. 308 </span> 309 310 This means that the data the cursor has read and released 311 may change before the cursor's transaction has completed. 312 </p> 313 <p> 314 For example, 315 suppose you have two transactions, <tt class="literal">Ta</tt> and <tt class="literal">Tb</tt>. Suppose further that 316 <tt class="literal">Ta</tt> has a cursor that reads <tt class="literal">record R</tt>, but does not modify it. Normally, 317 <tt class="literal">Tb</tt> would then be unable to write <tt class="literal">record R</tt> because 318 <tt class="literal">Ta</tt> would be holding a read lock on it. But when you configure your transaction for 319 committed reads, <tt class="literal">Tb</tt> <span class="emphasis"><em>can</em></span> modify <tt class="literal">record 320 R</tt> before <tt class="literal">Ta</tt> completes, so long as the reading cursor is no longer 321 addressing the 322 323 <span> 324 record or page. 325 </span> 326 327 </p> 328 <p> 329 When you configure your application for this level of isolation, you may see better performance 330 throughput because there are fewer read locks being held by your transactions. 331 Read committed isolation is most useful when you have a cursor that is reading and/or writing records in 332 a single direction, and that does not ever have to go back to re-read those same records. In this case, 333 you can allow DB to release read locks as it goes, rather than hold them for the life of the 334 transaction. 335 </p> 336 <p> 337 To configure your application to use committed reads, do one of the following: 338 </p> 339 <div class="itemizedlist"> 340 <ul type="disc"> 341 <li> 342 <p> 343 Create your transaction such that it allows committed reads. You do this by 344 <span> 345 specifying <tt class="literal">DB_READ_COMMITTED</tt> when you open the transaction. 346 </span> 347 348 </p> 349 </li> 350 <li> 351 <p> 352 <span> 353 Specify <tt class="literal">DB_READ_COMMITTED</tt> 354 when you open the cursor. 355 </span> 356 357 </p> 358 </li> 359 </ul> 360 </div> 361 <p> 362 For example, the following creates a transaction that allows committed reads: 363 </p> 364 <pre class="programlisting">#include "db_cxx.h" 365 366... 367 368int main(void) 369{ 370 u_int32_t env_flags = DB_CREATE | // If the environment does not 371 // exist, create it. 372 DB_INIT_LOCK | // Initialize locking 373 DB_INIT_LOG | // Initialize logging 374 DB_INIT_MPOOL | // Initialize the cache 375 DB_THREAD | // Free-thread the env handle 376 DB_INIT_TXN; // Initialize transactions 377 378 // Notice that we do not have to specify any flags to the database to 379 // allow committed reads (this is as opposed to uncommitted reads 380 // where we DO have to specify a flag on the database open. 381 u_int32_t db_flags = DB_CREATE | DB_AUTO_COMMIT; 382 Db *dbp = NULL; 383 const char *file_name = "mydb.db"; 384 385 std::string envHome("/export1/testEnv"); 386 DbEnv myEnv(0); 387 388 try { 389 390 myEnv.open(envHome.c_str(), env_flags, 0); 391 dbp = new Db(&myEnv, 0); 392 dbp->open(NULL, // Txn pointer 393 file_name, // File name 394 NULL, // Logical db name 395 DB_BTREE, // Database type (using btree) 396 db_flags, // Open flags 397 0); // File mode. Using defaults 398 399 DbTxn *txn = NULL; 400 401 // Open the transaction and enable committed reads. All cursors 402 // open with this transaction handle will use read committed 403 // isolation. 404 myEnv.txn_begin(NULL, &txn, DB_READ_COMMITTED); 405 406 // From here, you perform your database reads and writes as normal, 407 // committing and aborting the transactions as is necessary, 408 // testing for deadlock exceptions as normal (omitted for brevity). 409 410 // Using transactional cursors with concurrent applications is 411 // described in more detail in the following section. 412 413 ...</pre> 414 </div> 415 <div class="sect2" lang="en" xml:lang="en"> 416 <div class="titlepage"> 417 <div> 418 <div> 419 <h3 class="title"><a id="snapshot_isolation"></a>Using Snapshot Isolation</h3> 420 </div> 421 </div> 422 <div></div> 423 </div> 424 <p> 425 By default DB uses serializable isolation. An 426 important side effect of this isolation level is that 427 read operations obtain read locks on database pages, 428 and then hold those locks until the read operation is 429 completed. 430 431 <span> 432 When you are using transactional cursors, this 433 means that read locks are held until the transaction commits or 434 aborts. In that case, over time a transactional cursor 435 can gradually block all other transactions from writing 436 to the database. 437 </span> 438 </p> 439 <p> 440 You can avoid this by using snapshot isolation. 441 Snapshot isolation uses <span class="emphasis"><em>multiversion 442 concurrency control</em></span> to guarantee 443 repeatable reads. What this means is that every time a 444 writer would take a read lock on a page, instead a copy of 445 the page is made and the writer operates on that page 446 copy. This frees other writers from blocking due to a 447 read lock held on the page. 448 </p> 449 <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"> 450 <h3 class="title">Note</h3> 451 <p> 452 Snapshot isolation is strongly recommended for read-only threads when writer 453 threads are also running, as this will eliminate read-write contention and 454 greatly improve transaction throughput for your writer threads. However, in 455 order for snapshot isolation to work for your reader-only threads, you must 456 of course use transactions for your DB reads. 457 </p> 458 </div> 459 <div class="sect3" lang="en" xml:lang="en"> 460 <div class="titlepage"> 461 <div> 462 <div> 463 <h4 class="title"><a id="sisolation_cost"></a>Snapshot Isolation Cost</h4> 464 </div> 465 </div> 466 <div></div> 467 </div> 468 <p> 469 Snapshot isolation does not come without a cost. 470 Because pages are being duplicated before being 471 operated upon, the cache will fill up faster. This 472 means that you might need a larger cache in order to 473 hold the entire working set in memory. 474 </p> 475 <p> 476 If the cache becomes full of page copies before old 477 copies can be discarded, additional I/O will occur as 478 pages are written to temporary "freezer" files on disk. 479 This can substantially reduce throughput, and should be 480 avoided if possible by configuring a large cache and 481 keeping snapshot isolation transactions short. 482 </p> 483 <p> 484 You can estimate how large your cache should be by 485 taking a checkpoint, followed by a call to the 486 487 <tt class="methodname">DbEnv::log_archive()</tt> 488 489 method. The amount of cache required is approximately 490 double the size of the remaining log files (that is, 491 the log files that cannot be archived). 492 </p> 493 </div> 494 <div class="sect3" lang="en" xml:lang="en"> 495 <div class="titlepage"> 496 <div> 497 <div> 498 <h4 class="title"><a id="sisolation_maxtxn"></a>Snapshot Isolation Transactional Requirements</h4> 499 </div> 500 </div> 501 <div></div> 502 </div> 503 <p> 504 In addition to an increased cache size, you may also 505 need to increase the maximum number of transactions 506 that your application supports. (See 507 <a href="maxtxns.html">Configuring the Transaction Subsystem</a> 508 for details on how to set this.) 509 In the worst case scenario, you might need to configure your application for one 510 more transaction for every page in the cache. This is 511 because transactions are retained until the last page 512 they created is evicted from the cache. 513 </p> 514 </div> 515 <div class="sect3" lang="en" xml:lang="en"> 516 <div class="titlepage"> 517 <div> 518 <div> 519 <h4 class="title"><a id="sisolation_whenuse"></a>When to Use Snapshot Isolation</h4> 520 </div> 521 </div> 522 <div></div> 523 </div> 524 <p> 525 Snapshot isolation is best used when all or most 526 of the following conditions are true: 527 </p> 528 <div class="itemizedlist"> 529 <ul type="disc"> 530 <li> 531 <p> 532 You can have a large cache relative to your working data set size. 533 </p> 534 </li> 535 <li> 536 <p> 537 You require repeatable reads. 538 </p> 539 </li> 540 <li> 541 <p> 542 You will be using transactions that routinely work on 543 the entire database, or more commonly, 544 there is data in your database that will be very 545 frequently written by more than one transaction. 546 </p> 547 </li> 548 <li> 549 <p> 550 Read/write contention is 551 limiting your application's 552 throughput, or the application 553 is all or mostly read-only and 554 contention for the lock manager 555 mutex is limiting throughput. 556 </p> 557 </li> 558 </ul> 559 </div> 560 </div> 561 <div class="sect3" lang="en" xml:lang="en"> 562 <div class="titlepage"> 563 <div> 564 <div> 565 <h4 class="title"><a id="sisolation_howuse"></a>How to use Snapshot Isolation</h4> 566 </div> 567 </div> 568 <div></div> 569 </div> 570 <p> 571 You use snapshot isolation by: 572 </p> 573 <div class="itemizedlist"> 574 <ul type="disc"> 575 <li> 576 <p> 577 Opening the database with 578 multiversion support. You can 579 configure this either when you 580 open your environment or when 581 you open your 582 583 <span> 584 database. 585 </span> 586 587 588 <span> 589 Use the 590 <tt class="literal">DB_MULTIVERSION</tt> 591 flag to configure this support. 592 </span> 593 594 595 </p> 596 </li> 597 <li> 598 <p> 599 Configure your <span>cursor or</span> 600 transaction to use snapshot isolation. 601 </p> 602 <p> 603 To do this, 604 605 <span> 606 pass the <tt class="literal">DB_TXN_SNAPSHOT</tt> flag 607 when you 608 609 <span> 610 open the cursor or 611 </span> 612 613 create the transaction. 614 615 <span> 616 If configured for the transaction, 617 then this flag is not required 618 when the cursor is opened. 619 </span> 620 621 </span> 622 623 624 </p> 625 </li> 626 </ul> 627 </div> 628 <p> 629 The simplest way to take advantage of snapshot 630 isolation is for queries: keep update 631 transactions using full read/write locking and 632 use snapshot isolation on read-only transactions or 633 cursors. This should minimize blocking of 634 snapshot isolation transactions and will avoid 635 deadlock errors. 636 </p> 637 <p> 638 If the application has update transactions which 639 read many items and only update a small set (for 640 example, scanning until a desired record is 641 found, then modifying it), throughput may be 642 improved by running some updates at snapshot 643 isolation as well. But doing this means that 644 you must manage deadlock errors. 645 See 646 <a href="lockingsubsystem.html#deadlockresolve">Resolving Deadlocks</a> 647 for details. 648 </p> 649 <p> 650 The following code fragment turns 651 on snapshot isolation for a transaction: 652 </p> 653 <pre class="programlisting">#include "db_cxx.h" 654 655... 656 657int main(void) 658{ 659 u_int32_t env_flags = DB_CREATE | // If the environment does not 660 // exist, create it. 661 DB_INIT_LOCK | // Initialize locking 662 DB_INIT_LOG | // Initialize logging 663 DB_INIT_MPOOL | // Initialize the cache 664 DB_INIT_TXN | // Initialize transactions 665 <b class="userinput"><tt>DB_MULTIVERSION; // Support snapshot isolation.</tt></b> 666 667 // Note that no special flags are required here for snapshot isolation. 668 // This is because it is already enabled at the environment level. 669 u_int32_t db_flags = DB_CREATE | DB_AUTO_COMMIT; 670 Db *dbp = NULL; 671 const char *file_name = "mydb.db"; 672 673 std::string envHome("/export1/testEnv"); 674 DbEnv myEnv(0); 675 676 try { 677 678 myEnv.open(envHome.c_str(), env_flags, 0); 679 dbp = new Db(&myEnv, 0); 680 dbp->open(NULL, // Txn pointer 681 file_name, // File name 682 NULL, // Logical db name 683 DB_BTREE, // Database type (using btree) 684 db_flags, // Open flags 685 0); // File mode. Using defaults 686 687 } catch(DbException &e) { 688 std::cerr << "Error opening database and environment: " 689 << file_name << ", " 690 << envHome << std::endl; 691 std::cerr << e.what() << std::endl; 692 } 693 694 .... 695 696 envp->txn_begin(NULL, txn, <b class="userinput"><tt>DB_TXN_SNAPSHOT</tt></b>); 697 698 // Remainder of program omitted for brevity. 699 700 </pre> 701 </div> 702 </div> 703 </div> 704 <div class="navfooter"> 705 <hr /> 706 <table width="100%" summary="Navigation footer"> 707 <tr> 708 <td width="40%" align="left"><a accesskey="p" href="lockingsubsystem.html">Prev</a> </td> 709 <td width="20%" align="center"> 710 <a accesskey="u" href="txnconcurrency.html">Up</a> 711 </td> 712 <td width="40%" align="right"> <a accesskey="n" href="txn_ccursor.html">Next</a></td> 713 </tr> 714 <tr> 715 <td width="40%" align="left" valign="top">The Locking Subsystem </td> 716 <td width="20%" align="center"> 717 <a accesskey="h" href="index.html">Home</a> 718 </td> 719 <td width="40%" align="right" valign="top"> Transactional Cursors and Concurrent Applications</td> 720 </tr> 721 </table> 722 </div> 723 </body> 724</html> 725