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:
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.

sqlite-users mailing list

Reply via email to