As far as I know you have to purchase the documents. ----- Original Message ----- From: Aaron Elkins <threc...@yahoo.com> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Sent: Monday, July 9, 2018, 14:57:07 Subject: [sqlite] What happens when a call contains two SQL statement
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 > 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
