Documentation Source Text

Check-in [ead5e472f0]
Login

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: ead5e472f053dea7591f20dfecc6f14a93b0e9cd673010f166db558d1c116d83
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
Unified Diff Ignore Whitespace Patch
Changes to pages/lang.in.
625
626
627
628
629
630
631
632
633

634
635
636
637
638
639
640
641
642
643

RecursiveBubbleDiagram begin-stmt
RecursiveBubbleDiagram commit-stmt
RecursiveBubbleDiagram rollback-stmt
</tcl>

<p>
^No changes can be made to the database except within a transaction.
^Any command that changes the database (basically, any SQL command

other than [SELECT]) will automatically start a transaction if
one is not already in effect.  ^Automatically started transactions
are committed when the last query 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







|
|
>
|

|







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
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
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>
^Transactions can be deferred, immediate, or exclusive.  
^The default transaction behavior is deferred.
^Deferred means that no locks are acquired
on the database until the database is first accessed.  ^Thus with a
deferred transaction, the BEGIN statement itself does nothing to the
filesystem.  ^Locks
are not acquired until the first read or write operation.  ^The first read
operation against a database creates a [SHARED] lock and the first
write operation creates a [RESERVED] lock.   ^Because the acquisition of
locks is deferred until they are needed, it is possible that another
thread or process could create a separate transaction and write to
the database after the BEGIN on the current thread has executed.
^If the transaction is immediate, then [RESERVED] locks
are acquired on all databases as soon as the BEGIN command is
executed, without waiting for the
database to be used.  ^After a BEGIN IMMEDIATE, 
no other [database connection] will be able to write to the database or
do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE.  ^Other processes can continue
to read from the database, however.  ^An exclusive transaction causes
[EXCLUSIVE] locks to be acquired on all databases.  ^After a BEGIN
EXCLUSIVE, no other [database connection] except for [read_uncommitted]
connections will be able to read the database and no other connection without
exception will be able to write the database until the transaction is
complete.
</p>



<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

prepared statement is [sqlite3_reset() | reset] or
[sqlite3_finalize() | finalized].  An open [sqlite3_blob] used for




incremental BLOB I/O 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 a [shared lock] on the database
that prevented the database from being updated.  ^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 








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

<
<
<
<
<
<
<
<
<
<
<
<
|
<
<
|
<
<
<
|
|
<
|
<


>
>

|


>

|
>
>
>
>
|
|











|
|







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