Hi Ryan, 

Thank you for the interesting explanations for SQL specific cases, and I am 
also interested in the SQL standard document after reading your email, can you 
point me to the right place? 

Sorry, I did not trust myself to find the official SQL standard by googling.

- Aaron

> On Jul 9, 2018, at 8:35 PM, R Smith <ryansmit...@gmail.com> wrote:
> 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 
> 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:
>   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

sqlite-users mailing list

Reply via email to