1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
3
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
4
<title>SQLite Query Language: BEGIN TRANSACTION</title>
5
<style type="text/css">
8
font-family: Verdana, sans-serif;
13
a:visited { color: #734559 }
15
.logo { position:absolute; margin:3px; }
31
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
32
.toolbar a:visited { color: white; }
33
.toolbar a:hover { color: #044a64; background: white; }
35
.content { margin: 5%; }
36
.content dt { font-weight:bold; }
37
.content dd { margin-bottom: 25px; margin-left:20%; }
38
.content ul { padding:0px; padding-left: 15px; margin:0px; }
41
.se { background: url(images/se.gif) 100% 100% no-repeat #044a64}
42
.sw { background: url(images/sw.gif) 0% 100% no-repeat }
43
.ne { background: url(images/ne.gif) 100% 0% no-repeat }
44
.nw { background: url(images/nw.gif) 0% 0% no-repeat }
46
/* Things for "fancyformat" documents start here. */
47
.fancy img+p {font-style:italic}
48
.fancy .codeblock i { color: darkblue; }
49
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
50
.fancy h2 { margin-left: 10px }
51
.fancy h3 { margin-left: 20px }
52
.fancy h4 { margin-left: 30px }
53
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
54
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
55
.fancy #toc a { color: darkblue ; text-decoration: none }
56
.fancy .todo { color: #AA3333 ; font-style : italic }
57
.fancy .todo:before { content: 'TODO:' }
58
.fancy p.todo { border: solid #AA3333 1px; padding: 1ex }
59
.fancy img { display:block; }
60
.fancy :link:hover, .fancy :visited:hover { background: wheat }
61
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
62
.fancy li p { margin: 1em 0 }
63
/* End of "fancyformat" specific rules. */
69
<div><!-- container div to satisfy validator -->
72
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
74
<div><!-- IE hack to prevent disappearing logo--></div>
75
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
77
<table width=100% style="clear:both"><tr><td>
78
<div class="se"><div class="sw"><div class="ne"><div class="nw">
79
<table width=100% style="padding:0;margin:0;cell-spacing:0"><tr>
82
<a href="about.html">About</a>
83
<a href="sitemap.html">Sitemap</a>
84
<a href="docs.html">Documentation</a>
85
<a href="download.html">Download</a>
86
<a href="copyright.html">License</a>
87
<a href="news.html">News</a>
88
<a href="support.html">Support</a>
91
gMsg = "Search SQLite Docs..."
92
function entersearch() {
93
var q = document.getElementById("q");
94
if( q.value == gMsg ) { q.value = "" }
95
q.style.color = "black"
96
q.style.fontStyle = "normal"
98
function leavesearch() {
99
var q = document.getElementById("q");
100
if( q.value == "" ) {
102
q.style.color = "#044a64"
103
q.style.fontStyle = "italic"
108
<div style="padding:0 1em 0px 0;white-space:nowrap">
109
<form name=f method="GET" action="http://www.sqlite.org/search">
110
<input id=q name=q type=text
111
onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
112
<input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
116
</div></div></div></div>
118
<div class=startsearch></div>
120
<h1 align="center">SQL As Understood By SQLite</h1><p><a href="lang.html">[Top]</a></p><h2>BEGIN TRANSACTION</h2><h4><a href="syntaxdiagrams.html#begin-stmt">begin-stmt:</a></h4><blockquote> <img alt="syntax diagram begin-stmt" src="images/syntax/begin-stmt.gif"></img> </blockquote>
121
<h4><a href="syntaxdiagrams.html#commit-stmt">commit-stmt:</a></h4><blockquote> <img alt="syntax diagram commit-stmt" src="images/syntax/commit-stmt.gif"></img> </blockquote>
122
<h4><a href="syntaxdiagrams.html#rollback-stmt">rollback-stmt:</a></h4><blockquote> <img alt="syntax diagram rollback-stmt" src="images/syntax/rollback-stmt.gif"></img> </blockquote>
126
No changes can be made to the database except within a transaction.
127
Any command that changes the database (basically, any SQL command
128
other than <a href="lang_select.html">SELECT</a>) will automatically start a transaction if
129
one is not already in effect. Automatically started transactions
130
are committed when the last query finishes.
134
Transactions can be started manually using the BEGIN
135
command. Such transactions usually persist until the next
136
COMMIT or ROLLBACK command. But a transaction will also
137
ROLLBACK if the database is closed or if an error occurs
138
and the ROLLBACK conflict resolution algorithm is specified.
139
See the documentation on the <a href="lang_conflict.html">ON CONFLICT</a>
140
clause for additional information about the ROLLBACK
141
conflict resolution algorithm.
145
END TRANSACTION is an alias for COMMIT.
148
<p> Transactions created using BEGIN...COMMIT do not nest.
149
For nested transactions, use the <a href="lang_savepoint.html">SAVEPOINT</a> and <a href="lang_savepoint.html">RELEASE</a> commands.
150
The "TO SAVEPOINT <i>name</i>" clause of the ROLLBACK command shown
151
in the syntax diagram above is only applicable to <a href="lang_savepoint.html">SAVEPOINT</a>
152
transactions. An attempt to invoke the BEGIN command within
153
a transaction will fail with an error, regardless of whether
154
the transaction was started by <a href="lang_savepoint.html">SAVEPOINT</a> or a prior BEGIN.
155
The COMMIT command and the ROLLBACK command without the TO clause
156
work the same on <a href="lang_savepoint.html">SAVEPOINT</a> transactions as they do with transactions
157
started by BEGIN.</p>
160
Transactions can be deferred, immediate, or exclusive.
161
The default transaction behavior is deferred.
162
Deferred means that no locks are acquired
163
on the database until the database is first accessed. Thus with a
164
deferred transaction, the BEGIN statement itself does nothing to the
166
are not acquired until the first read or write operation. The first read
167
operation against a database creates a <a href="lockingv3.html#shared_lock">SHARED</a> lock and the first
168
write operation creates a <a href="lockingv3.html#reserved_lock">RESERVED</a> lock. Because the acquisition of
169
locks is deferred until they are needed, it is possible that another
170
thread or process could create a separate transaction and write to
171
the database after the BEGIN on the current thread has executed.
172
If the transaction is immediate, then <a href="lockingv3.html#reserved_lock">RESERVED</a> locks
173
are acquired on all databases as soon as the BEGIN command is
174
executed, without waiting for the
175
database to be used. After a BEGIN IMMEDIATE,
176
no other <a href="c3ref/sqlite3.html">database connection</a> will be able to write to the database or
177
do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue
178
to read from the database, however. An exclusive transaction causes
179
<a href="lockingv3.html#excl_lock">EXCLUSIVE</a> locks to be acquired on all databases. After a BEGIN
180
EXCLUSIVE, no other <a href="c3ref/sqlite3.html">database connection</a> except for <a href="pragma.html#pragma_read_uncommitted">read_uncommitted</a>
181
connections will be able to read the database and no other connection without
182
exception will be able to write the database until the transaction is
187
An implicit transaction (a transaction that is started automatically,
188
not a transaction started by BEGIN) is committed automatically when
189
the last active statement finishes. A statement finishes when its
190
prepared statement is <a href="c3ref/reset.html">reset</a> or
191
<a href="c3ref/finalize.html">finalized</a>. An open <a href="c3ref/blob.html">sqlite3_blob</a> used for
192
incremental BLOB I/O counts as an unfinished statement. The <a href="c3ref/blob.html">sqlite3_blob</a>
193
finishes when it is <a href="c3ref/blob_close.html">closed</a>.
197
The explicit COMMIT command runs immediately, even if there are
198
pending <a href="lang_select.html">SELECT</a> statements. However, if there are pending
199
write operations, the COMMIT command
200
will fail with an error code <a href="c3ref/c_abort.html">SQLITE_BUSY</a>.
204
An attempt to execute COMMIT might also result in an <a href="c3ref/c_abort.html">SQLITE_BUSY</a> return code
205
if an another thread or process has a <a href="lockingv3.html#shared_lock">shared lock</a> on the database
206
that prevented the database from being updated. When COMMIT fails in this
207
way, the transaction remains active and the COMMIT can be retried later
208
after the reader has had a chance to clear.
212
The ROLLBACK will fail with an error code <a href="c3ref/c_abort.html">SQLITE_BUSY</a> if there
213
are any pending queries. Both read-only and read/write queries will
214
cause a ROLLBACK to fail. A ROLLBACK must fail if there are pending
215
read operations (unlike COMMIT which can succeed) because bad things
216
will happen if the in-memory image of the database is changed out from under
221
If <a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode</a> is set to OFF (thus disabling the rollback journal
222
file) then the behavior of the ROLLBACK command is undefined.
225
<h3>Response To Errors Within A Transaction</h3>
227
<p> If certain kinds of errors occur within a transaction, the
228
transaction may or may not be rolled back automatically. The
229
errors that cause the behavior include:</p>
232
<li> <a href="c3ref/c_abort.html">SQLITE_FULL</a>: database or disk full
233
<li> <a href="c3ref/c_abort.html">SQLITE_IOERR</a>: disk I/O error
234
<li> <a href="c3ref/c_abort.html">SQLITE_BUSY</a>: database in use by another process
235
<li> <a href="c3ref/c_abort.html">SQLITE_NOMEM</a>: out or memory
236
<li> <a href="c3ref/c_abort.html">SQLITE_INTERRUPT</a>: processing <a href="c3ref/interrupt.html">interrupted</a>
237
by application request
241
For all of these errors, SQLite attempts to undo just the one statement
242
it was working on and leave changes from prior statements within the
243
same transaction intact and continue with the transaction. However,
244
depending on the statement being evaluated and the point at which the
245
error occurs, it might be necessary for SQLite to rollback and
246
cancel the entire transaction. An application can tell which
247
course of action SQLite took by using the
248
<a href="c3ref/get_autocommit.html">sqlite3_get_autocommit()</a> C-language interface.</p>
250
<p>It is recommended that applications respond to the errors
251
listed above by explicitly issuing a ROLLBACK command. If the
252
transaction has already been rolled back automatically
253
by the error response, then the ROLLBACK command will fail with an
254
error, but no harm is caused by this.</p>
256
<p>Future versions of SQLite may extend the list of errors which
257
might cause automatic transaction rollback. Future versions of
258
SQLite might change the error response. In particular, we may
259
choose to simplify the interface in future versions of SQLite by
260
causing the errors above to force an unconditional rollback.</p>
added
removed
lang_transaction.html
