Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Trying to improve the documentation on transactions. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | branch-3.29 |
Files: | files | file ages | folders |
SHA3-256: |
ead5e472f053dea7591f20dfecc6f14a |
User & Date: | drh 2019-08-12 11:54:09.232 |
Context
2019-08-15
| ||
16:03 | More precise description of when SQLITE_MASTER can be updated in the FAQ. (check-in: 5a6c1fea3c user: drh tags: branch-3.29) | |
2019-08-12
| ||
11:54 | Trying to improve the documentation on transactions. (check-in: ead5e472f0 user: drh tags: branch-3.29) | |
2019-08-06
| ||
17:02 | Improved documentation on how EXPLAIN only affects run-time but some PRAGMA statements operate at prepare-time and are thus unaffected by EXPLAIN. (check-in: e167521765 user: drh tags: branch-3.29) | |
Changes
Changes to pages/lang.in.
︙ | ︙ | |||
625 626 627 628 629 630 631 | RecursiveBubbleDiagram begin-stmt RecursiveBubbleDiagram commit-stmt RecursiveBubbleDiagram rollback-stmt </tcl> <p> | | | > | | | 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 | RecursiveBubbleDiagram begin-stmt RecursiveBubbleDiagram commit-stmt RecursiveBubbleDiagram rollback-stmt </tcl> <p> ^No reads or writes occur except within a transaction. ^Any command that accesses the database (basically, any SQL command, except a few [PRAGMA] statements) will automatically start a transaction if one is not already in effect. ^Automatically started transactions are committed when the last SQL statement finishes. </p> <p> ^Transactions can be started manually using the BEGIN command. ^(Such transactions usually persist until the next COMMIT or ROLLBACK command. But a transaction will also ROLLBACK if the database is closed or if an error occurs |
︙ | ︙ | |||
657 658 659 660 661 662 663 664 665 666 | in the syntax diagram above is only applicable to [SAVEPOINT] transactions. ^An attempt to invoke the BEGIN command within a transaction will fail with an error, regardless of whether the transaction was started by [SAVEPOINT] or a prior BEGIN. ^The COMMIT command and the ROLLBACK command without the TO clause work the same on [SAVEPOINT] transactions as they do with transactions started by BEGIN.</p> <tcl>hd_fragment immediate {BEGIN IMMEDIATE} {BEGIN EXCLUSIVE}</tcl> <p> | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > < < < < < < < < < < < < | < < | < < < | | < | < > > | > | > > > > | | | | | 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 | in the syntax diagram above is only applicable to [SAVEPOINT] transactions. ^An attempt to invoke the BEGIN command within a transaction will fail with an error, regardless of whether the transaction was started by [SAVEPOINT] or a prior BEGIN. ^The COMMIT command and the ROLLBACK command without the TO clause work the same on [SAVEPOINT] transactions as they do with transactions started by BEGIN.</p> <h3>Read transactions versus write transactions</h3> <p>SQLite current supports multiple simultaneous read transactions coming from separate database connections, possibly in separate threads or processes, but only one simultaneous write transaction. <p> <p>A read transaction is used for reading only. A write transaction allows both reading and writing. A read transaction is started by a SELECT statement, and a write transaction is started by statements like CREATE, DELETE, DROP, INSERT, or UPDATE (collectively "write statements"). If a write statement occurs while a read transaction is active, then the read transaction is upgraded to write transaction if possible. If some other database connection has already modified the database or is already in the process of modifying the database, then upgrading to a write transaction is not possible and the write statement will fail with [SQLITE_BUSY]. </p> <p> While a read transaction is active, any changes to the database that are implemented by separate database connections will not be seen by the database connection that started the read transaction. If database connection X is holding a read transaction, it is possible that some other database connection Y might change the content of the database while X's transaction is still open, however X will not be able to see those changes until after the transaction ends. While its read transaction is active, X will continue to see an historic snapshot the database prior to the changes implemented by Y. </p> <tcl>hd_fragment immediate {BEGIN IMMEDIATE} {BEGIN EXCLUSIVE}</tcl> <h3>DEFERRED, IMMEDIATE, and EXCLUSIVE transactions</h3> <p> ^Transactions can be DEFERRED, IMMEDIATE, or EXCLUSIVE. ^The default transaction behavior is DEFERRED. </p> <p> ^DEFERRED means that the transaction does not actually start until the database is first accessed. ^Internally, the BEGIN DEFERRRED statement merely sets a flag on the database connection that turns off the automatic commit that would normally occur when the last statement finishes. This causes the transaction that is automatically started to persist until an explicit COMMIT or ROLLBACK or until a rollback is provoked by an error or an ON CONFLICT ROLLBACK clause. If the first statement after BEGIN DEFERRED is a SELECT, then a read transaction is started. Subsequent write statements will upgrade the transaction to a write transaction if possible, or return SQLITE_BUSY. If the first statement after BEGIN DEFERRED is a write statement, then a write transaction is started. </p> <p> ^IMMEDIATE cause the database connection to start a new write immediately, without waiting for a writes statement. The BEGIN IMMEDIATE might fail with [SQLITE_BUSY] if another write transaction is already active on another database connection. </p> <p> ^EXCLUSIVE is similar to IMMEDIATE in that a write transaction is started immediately. EXCLUSIVE and IMMEDIATE are the same in [WAL mode], but in other journaling modes, EXCLUSIVE prevents other database connections from reading the database while the transaction is underway. </p> <h3>Implicit versus explicit transactions</h3> <p> An implicit transaction (a transaction that is started automatically, not a transaction started by BEGIN) is committed automatically when the last active statement finishes. A statement finishes when its last cursor closes, which is guaranteed to happen when the prepared statement is [sqlite3_reset() | reset] or [sqlite3_finalize() | finalized]. Some statements might "finish" for the purpose of transaction control prior to being reset or finalized, but there is no guarantee of this. The only way to ensure that a statement as "finished" is to invoke [sqlite3_reset()] or [sqlite3_finalize()] on that statement. An open [sqlite3_blob] used for incremental BLOB I/O also counts as an unfinished statement. The [sqlite3_blob] finishes when it is [sqlite3_blob_close() | closed]. </p> <p> ^The explicit COMMIT command runs immediately, even if there are pending [SELECT] statements. ^However, if there are pending write operations, the COMMIT command will fail with an error code [SQLITE_BUSY]. </p> <p> ^An attempt to execute COMMIT might also result in an [SQLITE_BUSY] return code if an another thread or process has an open read connection. ^When COMMIT fails in this way, the transaction remains active and the COMMIT can be retried later after the reader has had a chance to clear. </p> <p> In very old versions of SQLite (before version 3.7.11 - [dateof:3.7.11]) the ROLLBACK will fail with an error code |
︙ | ︙ |