Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| SHA1 Hash: | b17469386db3bd138dad5b0ed2668796db266d53 |
|---|---|
| Date: | 2013-03-11 20:22:24 |
| User: | drh |
| Comment: | Fix typos in the atomiccommit.html document. Update the proposed release date for 3.7.16. |
Tags And Properties
- branch=trunk inherited from [b2e03e19ab]
- sym-trunk inherited from [b2e03e19ab]
Changes
Changes to pages/atomiccommit.in
28 <p>This article describes the techniques used by SQLite to create the 28 <p>This article describes the techniques used by SQLite to create the 29 illusion of atomic commit.</p> 29 illusion of atomic commit.</p> 30 30 31 <p>The information in this article applies only when SQLite is operating 31 <p>The information in this article applies only when SQLite is operating 32 in "rollback mode", or in other words when SQLite is not 32 in "rollback mode", or in other words when SQLite is not 33 using a [write-ahead log]. SQLite still supports atomic commit when 33 using a [write-ahead log]. SQLite still supports atomic commit when 34 write-ahead logging is enabled, but it accomplishes atomic commit by 34 write-ahead logging is enabled, but it accomplishes atomic commit by 35 a different mechanism from the one describe in this article. See | 35 a different mechanism from the one described in this article. See 36 the [WAL | write-ahead log documentation] for additional information on how 36 the [WAL | write-ahead log documentation] for additional information on how 37 SQLite supports atomic commit in that context.</p> 37 SQLite supports atomic commit in that context.</p> 38 38 39 <tcl>hd_fragment hardware</tcl> 39 <tcl>hd_fragment hardware</tcl> 40 <h2>2.0 Hardware Assumptions</h2> 40 <h2>2.0 Hardware Assumptions</h2> 41 41 42 <p>Throughout this article, we will call the mass storage device "disk" 42 <p>Throughout this article, we will call the mass storage device "disk" ................................................................................................................................................................................ 158 SQLite does not add any redundancy to the database file for 158 SQLite does not add any redundancy to the database file for 159 the purpose of detecting corruption or I/O errors. 159 the purpose of detecting corruption or I/O errors. 160 SQLite assumes that the data it reads is exactly the same data 160 SQLite assumes that the data it reads is exactly the same data 161 that it previously wrote.</p> 161 that it previously wrote.</p> 162 162 163 <p>By default, SQLite assumes that an operating system call to write 163 <p>By default, SQLite assumes that an operating system call to write 164 a range of bytes will not damage or alter any bytes outside of that range 164 a range of bytes will not damage or alter any bytes outside of that range 165 even if a power lose or OS crash occurs during that write. We | 165 even if a power loss or OS crash occurs during that write. We 166 call this the "[PSOW | powersafe overwrite]" property. Prior to version 3.7.9, 166 call this the "[PSOW | powersafe overwrite]" property. Prior to version 3.7.9, 167 SQLite did not assume powersafe overwrite. But with the standard 167 SQLite did not assume powersafe overwrite. But with the standard 168 sector size increasing from 512 to 4096 bytes on most disk drives, it 168 sector size increasing from 512 to 4096 bytes on most disk drives, it 169 has become necessary to assume powersafe overwrite in order to maintain 169 has become necessary to assume powersafe overwrite in order to maintain 170 historical performance levels and so powersafe overwrite is assumed by 170 historical performance levels and so powersafe overwrite is assumed by 171 default in recent versions of SQLite. The assumption of powersafe 171 default in recent versions of SQLite. The assumption of powersafe 172 overwrite property can be disabled at compile-time or a run-time if 172 overwrite property can be disabled at compile-time or a run-time if ................................................................................................................................................................................ 222 lock allows two or more database connections to read from the 222 lock allows two or more database connections to read from the 223 database file at the same time. But a shared lock prevents 223 database file at the same time. But a shared lock prevents 224 another database connection from writing to the database file 224 another database connection from writing to the database file 225 while we are reading it. This is necessary because if another 225 while we are reading it. This is necessary because if another 226 database connection were writing to the database file at the 226 database connection were writing to the database file at the 227 same time we are reading from the database file, we might read 227 same time we are reading from the database file, we might read 228 some data before the change and other data after the change. 228 some data before the change and other data after the change. 229 This would make it appears as if the change made by the other | 229 This would make it appear as if the change made by the other 230 process is not atomic.</p> 230 process is not atomic.</p> 231 231 232 <p>Notice that the shared lock is on the operating system 232 <p>Notice that the shared lock is on the operating system 233 disk cache, not on the disk itself. File locks 233 disk cache, not on the disk itself. File locks 234 really are just flags within the operating system kernel, 234 really are just flags within the operating system kernel, 235 usually. (The details depend on the specific OS layer 235 usually. (The details depend on the specific OS layer 236 interface.) Hence, the lock will instantly vanish if the 236 interface.) Hence, the lock will instantly vanish if the ................................................................................................................................................................................ 273 and shared lock allow other processes to read from the database 273 and shared lock allow other processes to read from the database 274 file. A single reserve lock can coexist with multiple shared 274 file. A single reserve lock can coexist with multiple shared 275 locks from other processes. However, there can only be a 275 locks from other processes. However, there can only be a 276 single reserved lock on the database file. Hence only a 276 single reserved lock on the database file. Hence only a 277 single process can be attempting to write to the database 277 single process can be attempting to write to the database 278 at one time.</p> 278 at one time.</p> 279 279 280 <p>The idea behind a reserved locks is that it signals that | 280 <p>The idea behind a reserved lock is that it signals that 281 a process intends to modify the database file in the near 281 a process intends to modify the database file in the near 282 future but has not yet started to make the modifications. 282 future but has not yet started to make the modifications. 283 And because the modifications have not yet started, other 283 And because the modifications have not yet started, other 284 processes can continue to read from the database. However, 284 processes can continue to read from the database. However, 285 no other process should also begin trying to write to the 285 no other process should also begin trying to write to the 286 database.</p> 286 database.</p> 287 287 ................................................................................................................................................................................ 402 402 403 <p>Another flush must occur to make sure that all the 403 <p>Another flush must occur to make sure that all the 404 database changes are written into nonvolatile storage. 404 database changes are written into nonvolatile storage. 405 This is a critical step to ensure that the database will 405 This is a critical step to ensure that the database will 406 survive a power loss without damage. However, because 406 survive a power loss without damage. However, because 407 of the inherent slowness of writing to disk or flash memory, 407 of the inherent slowness of writing to disk or flash memory, 408 this step together with the rollback journal file flush in section 408 this step together with the rollback journal file flush in section 409 3.7 above takes up most the time required to complete a | 409 3.7 above takes up most of the time required to complete a 410 transaction commit in SQLite.</p> 410 transaction commit in SQLite.</p> 411 411 412 <br clear="both"> 412 <br clear="both"> 413 <a name="section_3_11"></a> 413 <a name="section_3_11"></a> 414 <h3>3.11 Deleting The Rollback Journal</h3> 414 <h3>3.11 Deleting The Rollback Journal</h3> 415 <img src="images/ac/commit-A.gif" align="right" hspace="15"> 415 <img src="images/ac/commit-A.gif" align="right" hspace="15"> 416 416 417 <p>After the database changes are all safely on the mass 417 <p>After the database changes are all safely on the mass 418 storage device, the rollback journal file is deleted. 418 storage device, the rollback journal file is deleted. 419 This is the instant where the transaction commits. 419 This is the instant where the transaction commits. 420 If a power failure or system crash occurs prior to this 420 If a power failure or system crash occurs prior to this 421 point, then recovery processes to be described later make 421 point, then recovery processes to be described later make 422 it appears as if no changes were ever made to the database | 422 it appear as if no changes were ever made to the database 423 file. If a power failure or system crash occurs after 423 file. If a power failure or system crash occurs after 424 the rollback journal is deleted, then it appears as if 424 the rollback journal is deleted, then it appears as if 425 all changes have been written to disk. Thus, SQLite gives 425 all changes have been written to disk. Thus, SQLite gives 426 the appearance of having made no changes to the database 426 the appearance of having made no changes to the database 427 file or having made the complete set of changes to the 427 file or having made the complete set of changes to the 428 database file depending on whether or not the rollback 428 database file depending on whether or not the rollback 429 journal file exists.</p> 429 journal file exists.</p> ................................................................................................................................................................................ 499 have to "rollback" any partial changes and restore the database to 499 have to "rollback" any partial changes and restore the database to 500 the state it was in prior to the beginning of the transaction.</p> 500 the state it was in prior to the beginning of the transaction.</p> 501 501 502 <tcl>hd_fragment crisis</tcl> 502 <tcl>hd_fragment crisis</tcl> 503 <h3>4.1 When Something Goes Wrong...</h3> 503 <h3>4.1 When Something Goes Wrong...</h3> 504 <img src="images/ac/rollback-0.gif" align="right" hspace="15"> 504 <img src="images/ac/rollback-0.gif" align="right" hspace="15"> 505 505 506 <p>Suppose the power loss occurred during step 3.10 above, | 506 <p>Suppose the power loss occurred > 507 during <a href="#section_3_10">step 3.10</a> above, 507 while the database changes were being written to disk. 508 while the database changes were being written to disk. 508 After power is restored, the situation might be something 509 After power is restored, the situation might be something 509 like what is shown to the right. We were trying to change 510 like what is shown to the right. We were trying to change 510 three pages of the database file but only one page was 511 three pages of the database file but only one page was 511 successfully written. Another page was partially written 512 successfully written. Another page was partially written 512 and a third page was not written at all.</p> 513 and a third page was not written at all.</p> 513 514 514 <p>The rollback journal is complete and intact on disk when 515 <p>The rollback journal is complete and intact on disk when 515 the power is restored. This is a key point. The reason for 516 the power is restored. This is a key point. The reason for > 517 the flush operation in <a href="#section_3_7">step 3.7</a> 516 the flush operation in step 3.7 is to make absolutely sure that | 518 is to make absolutely sure that 517 all of the rollback journal is safely on nonvolatile storage 519 all of the rollback journal is safely on nonvolatile storage 518 prior to making any changes to the database file itself.</p> 520 prior to making any changes to the database file itself.</p> 519 521 520 <br clear="both"> 522 <br clear="both"> 521 <a name="section_4_2"></a> 523 <a name="section_4_2"></a> 522 <h3>4.2 Hot Rollback Journals</h3> 524 <h3>4.2 Hot Rollback Journals</h3> 523 <img src="images/ac/rollback-1.gif" align="right" hspace="15"> 525 <img src="images/ac/rollback-1.gif" align="right" hspace="15"> 524 526 525 <p>The first time that any SQLite process attempts to access 527 <p>The first time that any SQLite process attempts to access 526 the database file, it obtains a shared lock as described in 528 the database file, it obtains a shared lock as described in > 529 <a href="section_3_2">section 3.2</a> above. 527 section 3.2 above. But then it notices that there is a | 530 But then it notices that there is a 528 rollback journal file present. SQLite then checks to see if 531 rollback journal file present. SQLite then checks to see if 529 the rollback journal is a "hot journal". A hot journal is 532 the rollback journal is a "hot journal". A hot journal is 530 a rollback journal that needs to be played back in order to 533 a rollback journal that needs to be played back in order to 531 restore the database to a sane state. A hot journal only 534 restore the database to a sane state. A hot journal only 532 exists when an earlier process was in the middle of committing 535 exists when an earlier process was in the middle of committing 533 a transaction when it crashed or lost power.</p> 536 a transaction when it crashed or lost power.</p> 534 537 535 <p>A rollback journal is a "hot" journal if all of the following 538 <p>A rollback journal is a "hot" journal if all of the following 536 are true:</p> 539 are true:</p> 537 540 538 <ul> 541 <ul> 539 <li>The rollback journal exist. | 542 <li>The rollback journal exists. 540 <li>The rollback journal is not an empty file. 543 <li>The rollback journal is not an empty file. 541 <li>There is no reserved lock on the main database file. 544 <li>There is no reserved lock on the main database file. 542 <li>The header of the rollback journal is well-formed and in particular 545 <li>The header of the rollback journal is well-formed and in particular 543 has not been zeroed out. 546 has not been zeroed out. 544 <li>The rollback journal does not 547 <li>The rollback journal does not 545 contain the name of a master journal file (see 548 contain the name of a master journal file (see 546 <a href="#section_5_5">section 5.5</a> below) or if does 549 <a href="#section_5_5">section 5.5</a> below) or if does ................................................................................................................................................................................ 569 <a name="section_4_4"></a> 572 <a name="section_4_4"></a> 570 <h3>4.4 Rolling Back Incomplete Changes</h3> 573 <h3>4.4 Rolling Back Incomplete Changes</h3> 571 <img src="images/ac/rollback-3.gif" align="right" hspace="15"> 574 <img src="images/ac/rollback-3.gif" align="right" hspace="15"> 572 575 573 <p>Once a process obtains an exclusive lock, it is permitted 576 <p>Once a process obtains an exclusive lock, it is permitted 574 to write to the database file. It then proceeds to read the 577 to write to the database file. It then proceeds to read the 575 original content of pages out of the rollback journal and write 578 original content of pages out of the rollback journal and write 576 that content back to were it came from in the database file. | 579 that content back to where it came from in the database file. 577 Recall that the header of the rollback journal records the original 580 Recall that the header of the rollback journal records the original 578 size of the database file prior to the start of the aborted 581 size of the database file prior to the start of the aborted 579 transaction. SQLite uses this information to truncate the 582 transaction. SQLite uses this information to truncate the 580 database file back to its original size in cases where the 583 database file back to its original size in cases where the 581 incomplete transaction caused the database to grow. At the 584 incomplete transaction caused the database to grow. At the 582 end of this step, the database should be the same size and 585 end of this step, the database should be the same size and 583 contain the same information as it did before the start of 586 contain the same information as it did before the start of ................................................................................................................................................................................ 592 played back into the database file (and flushed to disk in case 595 played back into the database file (and flushed to disk in case 593 we encounter yet another power failure), the hot rollback journal 596 we encounter yet another power failure), the hot rollback journal 594 can be deleted.</p> 597 can be deleted.</p> 595 598 596 <p>As in <a href="#section_3_11">section 3.11</a>, the journal 599 <p>As in <a href="#section_3_11">section 3.11</a>, the journal 597 file might be truncated to zero length or its header might 600 file might be truncated to zero length or its header might 598 be overwritten with zeros as an optimization on systems where 601 be overwritten with zeros as an optimization on systems where 599 deleting a file is expense. Either way, the journal is no | 602 deleting a file is expensive. Either way, the journal is no 600 long hot after this step.</p> | 603 longer hot after this step.</p> 601 604 602 <br clear="both"> 605 <br clear="both"> 603 <tcl>hd_fragment cont</tcl> 606 <tcl>hd_fragment cont</tcl> 604 <h3>4.6 Continue As If The Uncompleted Writes Had Never Happened</h3> 607 <h3>4.6 Continue As If The Uncompleted Writes Had Never Happened</h3> 605 <img src="images/ac/rollback-5.gif" align="right" hspace="15"> 608 <img src="images/ac/rollback-5.gif" align="right" hspace="15"> 606 609 607 <p>The final recovery step is to reduce the exclusive lock back 610 <p>The final recovery step is to reduce the exclusive lock back ................................................................................................................................................................................ 948 951 949 <a name="section_7_2"></a> 952 <a name="section_7_2"></a> 950 <h3>7.2 Exclusive Access Mode</h3> 953 <h3>7.2 Exclusive Access Mode</h3> 951 954 952 <p>SQLite version 3.3.14 adds the concept of "Exclusive Access Mode". 955 <p>SQLite version 3.3.14 adds the concept of "Exclusive Access Mode". 953 In exclusive access mode, SQLite retains the exclusive 956 In exclusive access mode, SQLite retains the exclusive 954 database lock at the conclusion of each transaction. This prevents 957 database lock at the conclusion of each transaction. This prevents 955 other processes for accessing the database, but in many deployments | 958 other processes from accessing the database, but in many deployments 956 only a single process is using a database so this is not a 959 only a single process is using a database so this is not a 957 serious problem. The advantage of exclusive access mode is that 960 serious problem. The advantage of exclusive access mode is that 958 disk I/O can be reduced in three ways:</p> 961 disk I/O can be reduced in three ways:</p> 959 962 960 <ol> 963 <ol> 961 <li><p>It is not necessary to increment the change counter in the 964 <li><p>It is not necessary to increment the change counter in the 962 database header for transactions after the first transaction. This 965 database header for transactions after the first transaction. This ................................................................................................................................................................................ 1030 writes. When power is lost, the drive uses energy stored in capacitors 1033 writes. When power is lost, the drive uses energy stored in capacitors 1031 and/or the angular momentum of the disk platter to provide power to 1034 and/or the angular momentum of the disk platter to provide power to 1032 complete any operation in progress. Nevertheless, there are so many 1035 complete any operation in progress. Nevertheless, there are so many 1033 layers in between the write system call and the on-board disk drive 1036 layers in between the write system call and the on-board disk drive 1034 electronics that we take the safe approach in both Unix and w32 VFS 1037 electronics that we take the safe approach in both Unix and w32 VFS 1035 implementations and assume that sector writes are not atomic. On the 1038 implementations and assume that sector writes are not atomic. On the 1036 other hand, device 1039 other hand, device 1037 manufactures with more control over their filesystems might want | 1040 manufacturers with more control over their filesystems might want 1038 to consider enabling the atomic write property of xDeviceCharacteristics 1041 to consider enabling the atomic write property of xDeviceCharacteristics 1039 if their hardware really does do atomic writes.</p> 1042 if their hardware really does do atomic writes.</p> 1040 1043 1041 <p>When sector writes are atomic and the page size of a database is 1044 <p>When sector writes are atomic and the page size of a database is 1042 the same as a sector size, and when there is a database change that 1045 the same as a sector size, and when there is a database change that 1043 only touches a single database page, then SQLite skips the whole 1046 only touches a single database page, then SQLite skips the whole 1044 journaling and syncing process and simply writes the modified page 1047 journaling and syncing process and simply writes the modified page ................................................................................................................................................................................ 1278 <tcl>hd_fragment mvhotjrnl</tcl> 1281 <tcl>hd_fragment mvhotjrnl</tcl> 1279 <h3>9.5 Deleting Or Renaming A Hot Journal</h3> 1282 <h3>9.5 Deleting Or Renaming A Hot Journal</h3> 1280 1283 1281 <p>If a crash or power loss does occur and a hot journal is left on 1284 <p>If a crash or power loss does occur and a hot journal is left on 1282 the disk, it is essential that the original database file and the hot 1285 the disk, it is essential that the original database file and the hot 1283 journal remain on disk with their original names until the database 1286 journal remain on disk with their original names until the database 1284 file is opened by another SQLite process and rolled back. 1287 file is opened by another SQLite process and rolled back. 1285 During recovery at <a href="section_4_2">step 4.2</a> SQLite locates | 1288 During recovery at <a href="#section_4_2">step 4.2</a> SQLite locates 1286 the hot journal by looking for a file in the same directory as the 1289 the hot journal by looking for a file in the same directory as the 1287 database being opened and whose name is derived from the name of the 1290 database being opened and whose name is derived from the name of the 1288 file being opened. If either the original database file or the 1291 file being opened. If either the original database file or the 1289 hot journal have been moved or renamed, then the hot journal will 1292 hot journal have been moved or renamed, then the hot journal will 1290 not be seen and the database will not be rolled back.</p> 1293 not be seen and the database will not be rolled back.</p> 1291 1294 1292 <p>We suspect that a common failure mode for SQLite recovery happens 1295 <p>We suspect that a common failure mode for SQLite recovery happens
Changes to pages/changes.in
38 http://www.sqlite.org/src/timeline</a>.</p> 38 http://www.sqlite.org/src/timeline</a>.</p> 39 } 39 } 40 hd_close_aux 40 hd_close_aux 41 hd_enable_main 1 41 hd_enable_main 1 42 } 42 } 43 } 43 } 44 44 45 chng {2013-03-12 (3.7.16)} { | 45 chng {2013-03-18 (3.7.16)} { 46 <li>Added the [PRAGMA foreign_key_check] command. 46 <li>Added the [PRAGMA foreign_key_check] command. 47 <li>Added new extended error codes for all SQLITE_CONSTRAINT errors 47 <li>Added new extended error codes for all SQLITE_CONSTRAINT errors 48 <li>Added SQL functions [unicode(A)] and [char(X1,...,XN)]. 48 <li>Added SQL functions [unicode(A)] and [char(X1,...,XN)]. 49 <li>Performance improvements for [PRAGMA incremental_vacuum], especially in 49 <li>Performance improvements for [PRAGMA incremental_vacuum], especially in 50 cases where the number of free pages is greater than what will fit on a 50 cases where the number of free pages is greater than what will fit on a 51 single trunk page of the freelist. 51 single trunk page of the freelist. 52 <li>Improved optimization of queries containing aggregate min() or max(). 52 <li>Improved optimization of queries containing aggregate min() or max().