I see Gunter already answered this for the specific case, I'll just add
some simple rules of thumb for transactions in general:
1. SQLite automagically starts a transaction ONLY when faced with a
single statement. The second statement in your query/list/script is in
its own transaction and not the same one as the first statement.
2. If at any point you need more than one statement to be run together
in a single transaction, you need to start with BEGIN TRANSACTION, add
all statements and end it with either END, COMMIT or ROLLBACK.
3. Point 2 is True for ALL SQL Engines.
4. Typically once you've started a transaction, you control when it is
committed or rolled back. This is typically done in your code, but you
can of course set SQL conflict handling to have a vote in what happens.
In SQLite specifically, the word "TRANSACTION" is optional, i.e. BEGIN
TRANSACTION is equivalent to just BEGIN, and END TRANSACTION is the same
as just END. Ditto for ROLLBACK TRANSACTION and just ROLLBACK.
END is also equivalent to COMMIT - it saves the changes and busts out of
the current transaction (Important note: It does this EVEN if there were
errors. Unless specifically instructed to roll back, a transaction is
perfectly happy to complete and commit the successful bits - this is
true for most (if not all) SQL Engines).
In any INSERT/UPDATE data altering statement, you can add an ON CONFLICT
clause which specifically instructs how to handle the current statement
and transaction flow.
The options are:
- ON CONFLICT ABORT (the default if nothing is specified) - which tells
the engine to Stop and Undo this current command's updates, but do not
stop the transaction, keep changes by previous statements and keep going
with the next statements. [Kill me, but not the others]
- ON CONFLICT FAIL - which tells the engine the current update failure
should STOP the current statement in its tracks, but not undo any
changes up to now caused by the current statement, and still continue
with any next statements. [Don't let me continue, but don't kill me and
don't kill the others]
- ON CONFLICT IGNORE - which tells the engine that this fail is o.k. and
it may BOTH continue with updates for this current statement AND soldier
on with the next statements in the transaction. [No harm done, Forgive
and Forget, Peace and Love, let's move on...]
- ON CONFLICT ROLLBACK - which undoes the entire set of changes since
the BEGIN. [This is SPARTAAAA! Kill everything!]
- ON CONFLICT REPLACE - this one is more complicated but essentially it
tries to remove the old data (where needed) and shove the new data in
there, but in all cases it doesn't stop the TRANSACTION. [Please don't
kill us... I'll do anything you want!]
Note also that a statement "failing" here means it ran into a constraint
conflict and so cannot fulfill its correctly-understood obligation. If
the statement fails programmatically because it is a nonsense statement
that cannot be correctly understood by the parser/query planner, then in
my experience the transaction is rolled back automatically (much the
same as if there was a power-failure, an IO error, etc.). I'm not sure
this is 100% always the case?
Some things to watch out for:
- Unlike some other SQL engines, in SQLite transactions do not nest.
[There can be only one!]. Nesting behaviour is however possible using
SAVEPOINT instructions. (See the docs).
- SQLite wrappers in your favourite programming environment will often
automatically start transactions or automatically group statements in
transactions unbeknown to the programmer - check your wrapper's
documentation and config.
- SQLite CONFLICT clauses can be set upon table creation for
constraints, for example the table schema you are writing a transaction
for may have a declaration like:
CREATE TABLE t1 (
ID INT PRIMARY KEY ON CONFLICT ROLLBACK,
Name TEXT CHECK len(Name) > 0 ON CONFLICT FAIL,
...
etc.
);
Now you can override this in the transaction by specifying your own ON
CONFLICT clause when doing an INSERT, for example, but it's worth noting
that in case you don't have a conflict clause, and as such are expecting
the default behaviour of "ON CONFLICT ABORT", you might be surprised by
an entire transaction roll-back when INSERTing a duplicate ID, or the
current statement stopping when one empty Name value occurs.
That's basically the short version of what you need to know about
transactions in SQLite.
Cheers!
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
