What happens when a call contains two SQL statement

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

What happens when a call contains two SQL statement

Cecil Westerhof-5
I am working with Tcl. The best is of-course a general answer, but if it is
depending on the used language I will be satisfied with the Tcl answer. ;-)

Say I have the following code:
    set SQLCmd "
        DELETE FROM testing
        WHERE  key = 12
        ;
        INSERT INTO testing
        (key, value)
        VALUES
        (12, 'Just some text')
    "
    db eval ${sqlCmd}

If the insert goes wrong, will the delete be rolled back, or not?

I could use INSERT OR REPLACE, but the above code would be database
independent.

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] What happens when a call contains two SQL statement

Hick Gunter
Why should a failure in transaction #2 rollback transaction #1?

If you want this behaviour, do "begin; delete ...; insert ... on conflict rollback; commit;" to make both statements run in one transaction

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Cecil Westerhof
Gesendet: Montag, 09. Juli 2018 08:21
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] [sqlite] What happens when a call contains two SQL statement

I am working with Tcl. The best is of-course a general answer, but if it is depending on the used language I will be satisfied with the Tcl answer. ;-)

Say I have the following code:
    set SQLCmd "
        DELETE FROM testing
        WHERE  key = 12
        ;
        INSERT INTO testing
        (key, value)
        VALUES
        (12, 'Just some text')
    "
    db eval ${sqlCmd}

If the insert goes wrong, will the delete be rolled back, or not?

I could use INSERT OR REPLACE, but the above code would be database independent.

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] What happens when a call contains two SQL statement

Cecil Westerhof-5
2018-07-09 8:28 GMT+02:00 Hick Gunter <[hidden email]>:

> Why should a failure in transaction #2 rollback transaction #1?
>

​I was thinking that, but was not sure. I thought that maybe everything in
a call would be seen as a transaction. But that is not the case then: every
statement in a call is its own transaction.

​​


> If you want this behaviour, do "begin; delete ...; insert ... on conflict
> rollback; commit;" to make both statements run in one transaction
>

Is this standard SQL, or SQLite specific?



> -----Ursprüngliche Nachricht-----
> Von: sqlite-users [mailto:[hidden email]]
> Im Auftrag von Cecil Westerhof
> Gesendet: Montag, 09. Juli 2018 08:21
> An: SQLite mailing list <[hidden email]>
> Betreff: [EXTERNAL] [sqlite] What happens when a call contains two SQL
> statement
>
> I am working with Tcl. The best is of-course a general answer, but if it
> is depending on the used language I will be satisfied with the Tcl answer.
> ;-)
>
> Say I have the following code:
>     set SQLCmd "
>         DELETE FROM testing
>         WHERE  key = 12
>         ;
>         INSERT INTO testing
>         (key, value)
>         VALUES
>         (12, 'Just some text')
>     "
>     db eval ${sqlCmd}
>
> If the insert goes wrong, will the delete be rolled back, or not?
>
> I could use INSERT OR REPLACE, but the above code would be database
> independent.
>

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] What happens when a call contains two SQL statement

Hick Gunter
Only the "on conflict" clause

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Cecil Westerhof
Gesendet: Montag, 09. Juli 2018 08:38
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] [EXTERNAL] What happens when a call contains two SQL statement

2018-07-09 8:28 GMT+02:00 Hick Gunter <[hidden email]>:

> Why should a failure in transaction #2 rollback transaction #1?
>

​I was thinking that, but was not sure. I thought that maybe everything in a call would be seen as a transaction. But that is not the case then: every statement in a call is its own transaction.

​​


> If you want this behaviour, do "begin; delete ...; insert ... on
> conflict rollback; commit;" to make both statements run in one
> transaction
>

Is this standard SQL, or SQLite specific?



> -----Ursprüngliche Nachricht-----
> Von: sqlite-users
> [mailto:[hidden email]]
> Im Auftrag von Cecil Westerhof
> Gesendet: Montag, 09. Juli 2018 08:21
> An: SQLite mailing list <[hidden email]>
> Betreff: [EXTERNAL] [sqlite] What happens when a call contains two SQL
> statement
>
> I am working with Tcl. The best is of-course a general answer, but if
> it is depending on the used language I will be satisfied with the Tcl answer.
> ;-)
>
> Say I have the following code:
>     set SQLCmd "
>         DELETE FROM testing
>         WHERE  key = 12
>         ;
>         INSERT INTO testing
>         (key, value)
>         VALUES
>         (12, 'Just some text')
>     "
>     db eval ${sqlCmd}
>
> If the insert goes wrong, will the delete be rolled back, or not?
>
> I could use INSERT OR REPLACE, but the above code would be database
> independent.
>

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: What happens when a call contains two SQL statement

R Smith-2
In reply to this post by Cecil Westerhof-5
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: What happens when a call contains two SQL statement

Aaron Elkins
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: What happens when a call contains two SQL statement

Thomas Kurz
As far as I know you have to purchase the documents.

----- Original Message -----
From: Aaron Elkins <[hidden email]>
To: SQLite mailing list <[hidden email]>
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: What happens when a call contains two SQL statement

Simon Slavin-3
In reply to this post by Aaron Elkins
On 9 Jul 2018, at 1:57pm, Aaron Elkins <[hidden email]> wrote:

> 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?

SQL-86 was the first widely-adopted SQL standard.  However later standards added much-needed features to the language, giving it its dominant position throughout database programming.  The 1992 version of SQL was the first and last document for what has become standard SQL:

<http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt>

You will note, for example, that there are still no DATE, TIME or DATETIME types.  If you look at a later standard and don't know why it's weird, go back to this 1992 standard and see how the things in this standard guided what changes could be made without breaking compatibility.

You can find (especially if you're willing to pay) standards for SQL since the 1992 one: 1999, 2003, 2006, 2008 etc..  However, they quickly became so large and complicated that no implementation of SQL implemented as much of the later standards as was done for the 1992 version.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: What happens when a call contains two SQL statement

Simon Slavin-3
On 9 Jul 2018, at 4:13pm, Simon Slavin <[hidden email]> wrote:

> You can find (especially if you're willing to pay) standards for SQL since the 1992 one: 1999, 2003, 2006, 2008 etc..  However, they quickly became so large and complicated that no implementation of SQL implemented as much of the later standards as was done for the 1992 version.

I wanted to emphasise a point that Thomas Kurz made.  The 1992 standard was made publicly available for free.  Therefore many implementors got hold of it and followed it, and discussion of behaviour could refer back to the standard to clarify what implementations should and should not be doing.  It's fully in the spirit of Open Standard.

The 2008 version of the standard is not bad.  But you're meant to pay the ISO for a copy, and there are many limitations on posting quotes in public.  As you can imagine, this greatly hampers discussion of behaviour and identification of bugs.

Part 1 of the current standard, ISO/IEC 9075-1:2016, for example, is 78 pages long and costs CHF178 (= 150 Euro = US$180).  I didn't look up all the prices, but part 14 (sic) is 444 (sic) pages long and costs CHF198 (= 170 Euro = US$200).  I would encounter some difficulty in writing software to conform to a 444 page document if I couldn't discuss that document with people outside my organisation.

Far from an Open Standard.  Which is why you will rarely see it quoted.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users