Handling ROLLBACK

classic Classic list List threaded Threaded
18 messages Options
Reply | Threaded
Open this post in threaded view
|

Handling ROLLBACK

Simon Slavin-3
I have run experiments in the shell tool, using different journal modes, but I would like to know whether my observations are cannon or just one-offs.  Contributions welcome from all, especially those who have read SQLite source code, and SQL language-lawyers whether or not you're familiar with how other SQL implementations work.


Does ROLLBACK release the transaction lock on the database ?

Does ROLLBACK cancel the BEGIN ?  Or do I need to issue END ?

Suppose ROLLBACK does not cancel the BEGIN, can a programmer reliably issue more SQL commands, including another ROLLBACK ?  Will SQLite continue to react correctly to other ROLLBACKs, and to SQL commands which result in "(516) SQLITE_ABORT_ROLLBACK".


If you think I've missed a relevant point, please don't hesitate to bring it up.
_______________________________________________
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: Handling ROLLBACK

D Burgess
if I understand the question:

On Sun, Mar 3, 2019 at 10:12 AM Simon Slavin <[hidden email]> wrote:

> Does ROLLBACK release the transaction lock on the database ?
>
I think so or my code would not be working.

>
> Does ROLLBACK cancel the BEGIN ?  Or do I need to issue END ?
>
END is a synonym for commit . So
begin transaction;
stuff
commit OR rollback.

rollback cancels the begin

>
>
Suppose ROLLBACK does not cancel the BEGIN, can a programmer reliably issue
more SQL commands, including another ROLLBACK ?  Will SQLite continue to
react correctly to other ROLLBACKs, and to SQL commands which result in
"(516) SQLITE_ABORT_ROLLBACK".

>
>
> If you think I've missed a relevant point, please don't hesitate to bring
> it up.
> _______________________________________________
> 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: Handling ROLLBACK

D Burgess
In reply to this post by Simon Slavin-3
Does ROLLBACK release the transaction lock on the database ?

I checked scripts on  this. Yes.
_______________________________________________
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: Handling ROLLBACK

Chris Smith
No


On Sat, Mar 2, 2019, 18:50 D Burgess <[hidden email]> wrote:

> Does ROLLBACK release the transaction lock on the database ?
>
> I checked scripts on  this. Yes.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
Cheers,
Chris
_______________________________________________
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: Handling ROLLBACK

Keith Medcalf
In reply to this post by Simon Slavin-3

BEGIN TRANSACTION
 ... issued commands inside transaction
COMMIT or ROLLBACK
-- transaction is now closed

That is,
  BEGIN "opens" a transaction
  COMMIT "closes" the transaction in progress and saves the changes made during that transaction.
  ROLLBACK "closes" the transaction in progress and discards the changes made during that transaction.

While a transaction is "in effect", the "view" of the database is stable relative to all other CONNECTIONS to the database.  Changes made to the database in transactions on OTHER CONNECTIONS are not visible, and *unless you specify otherwise* you have REPEATABLE READ isolation.

Within a transaction you may nest SAVEPOINTs.  Savepoints are transactions within transactions and can be viewed as a stack.  If no TRANSACTION is in process then BEGIN TRANSACTION and SAVEPOINT are identical -- that is, a SAVEPOINT can only exist within a transaction and a SAVEPOINT where no transaction is in progress will start an "implicit" transaction if necessary.

SAVEPOINT <xxxxx> -- create a "context" named "xxxxx" (and an "implicit" transaction if one is not already in progress)

ROLLBACK TO <xxxxx> -- rollback the "context" to the state that it was in when the context was created.  Do not pop the context <xxxxx> off the stack but pop any contexts off the stack that were created "after" context <xxxxx> was created.  You are still in a transaction and the context <xxxxx> still exists.

RELEASE <xxxxx> -- commit all the changes that occurred after the specified context to the outer context.  Pop <xxxxx> from the context stack and also pop any contexts created after context <xxxxx> was declared (that is, all changes made after the declaration of the context are "moved into" the next higher context and all references to the named context and contained contexts are removed.  If you have "RELEASED" the last SAVEPOINT (ie, the first on the stack) and the containing transaction was created implicitly (rather than explicitly with BEGIN), then that implicit outer transaction is also committed and no transaction is any longer in effect.  If the out transaction was explicitly commenced (with BEGIN) then that transaction is still in effect and must be COMMITTED or ROLLBACKed in order to end it.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Simon Slavin
>Sent: Saturday, 2 March, 2019 16:12
>To: SQLite mailing list
>Subject: [sqlite] Handling ROLLBACK
>
>I have run experiments in the shell tool, using different journal
>modes, but I would like to know whether my observations are cannon or
>just one-offs.  Contributions welcome from all, especially those who
>have read SQLite source code, and SQL language-lawyers whether or not
>you're familiar with how other SQL implementations work.
>
>
>Does ROLLBACK release the transaction lock on the database ?
>
>Does ROLLBACK cancel the BEGIN ?  Or do I need to issue END ?
>
>Suppose ROLLBACK does not cancel the BEGIN, can a programmer reliably
>issue more SQL commands, including another ROLLBACK ?  Will SQLite
>continue to react correctly to other ROLLBACKs, and to SQL commands
>which result in "(516) SQLITE_ABORT_ROLLBACK".
>
>
>If you think I've missed a relevant point, please don't hesitate to
>bring it up.
>_______________________________________________
>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: Handling ROLLBACK

Keith Medcalf
In reply to this post by Simon Slavin-3

>Suppose ROLLBACK does not cancel the BEGIN, can a programmer reliably
>issue more SQL commands, including another ROLLBACK ?  Will SQLite
>continue to react correctly to other ROLLBACKs, and to SQL commands
>which result in "(516) SQLITE_ABORT_ROLLBACK".

A successful ROLLBACK on a transaction discards all changes that were made during the transaction (rolls back to the state at the time the transaction began), and ends the transaction.  It does not "cancel" the "BEGIN", it merely exits the transaction and discards changes made during the transaction.  Since the BEGIN occurred in the past, it cannot be "cancelled" ...

Full Stop End of Line.

However, a ROLLBACK command which produces the error "(516) SQLITE_ABORT_ROLLBACK" would indicate that the ROLLBACK was NOT PERFORMED due to an error.  Therefore the transaction is STILL IN EFFECT and has neither been COMMITed nor ROLLBACKed.  Once the programming error which prevented the rollback is cleared the ROLLBACK command can be retried.  Once a transaction is opened it remains in effect until ROLLBACK or COMMIT complete successfuly (or the heat death of the universe, whichever shall come first).

Note that this discussion does not include "fatal errors" which may occur within the context of a transaction that abort the transaction itself.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
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: Handling ROLLBACK

Keith Medcalf

Actually, in current versions of SQLite3 the error "(516) SQLITE_ABORT_ROLLBACK" is returned by "other commands" and not by the rollback command.  If the ROLLBACK command completed successfully (that is, it returned YaHoo! SQLITE_OK) then the transaction changes have been rolled back.  If "some other command" returns an error indicating that it was ABORTed then that other command was aborted and that has no bearing on whether or not changes to the database were rolled back.  They were.

I presume that if a SELECT was in progress during the time that a ROLLBACK was processed on the SAME CONNECTION and that the SELECT is allowed to proceed, that it will maintain the REPEATABLE READ isolation that it was commenced with and that when that statement is finalized, the transaction context will be released.  

Whether the connection is still "inside" the transaction or not can be determined by using the sqlite3_autocommit() API.  

Since this is kind of an undocumented grey area, I would suggest that you do not make the programming errors which would result in this situation (a Schrodinger Transaction state, for lack of a better descriptive).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Keith Medcalf
>Sent: Saturday, 2 March, 2019 19:32
>To: SQLite mailing list
>Subject: Re: [sqlite] Handling ROLLBACK
>
>
>>Suppose ROLLBACK does not cancel the BEGIN, can a programmer
>reliably
>>issue more SQL commands, including another ROLLBACK ?  Will SQLite
>>continue to react correctly to other ROLLBACKs, and to SQL commands
>>which result in "(516) SQLITE_ABORT_ROLLBACK".
>
>A successful ROLLBACK on a transaction discards all changes that were
>made during the transaction (rolls back to the state at the time the
>transaction began), and ends the transaction.  It does not "cancel"
>the "BEGIN", it merely exits the transaction and discards changes
>made during the transaction.  Since the BEGIN occurred in the past,
>it cannot be "cancelled" ...
>
>Full Stop End of Line.
>
>However, a ROLLBACK command which produces the error "(516)
>SQLITE_ABORT_ROLLBACK" would indicate that the ROLLBACK was NOT
>PERFORMED due to an error.  Therefore the transaction is STILL IN
>EFFECT and has neither been COMMITed nor ROLLBACKed.  Once the
>programming error which prevented the rollback is cleared the
>ROLLBACK command can be retried.  Once a transaction is opened it
>remains in effect until ROLLBACK or COMMIT complete successfuly (or
>the heat death of the universe, whichever shall come first).
>
>Note that this discussion does not include "fatal errors" which may
>occur within the context of a transaction that abort the transaction
>itself.
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>
>
>_______________________________________________
>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: Handling ROLLBACK

Keith Medcalf

My observation (on the current tip version 3.28.0) of Schrodingers Transactions is that if there is (for example) a transaction in progress and that is COMMIT or ROLLBACK, then the changes are either committed or rolled back and the explicit transaction is ended (that is, autocommit becomes True).

Statements which were in progress that were permitted to proceed (ie, where the next step did not return an abort error) continue with a read lock in place (ie, as if they were part of an implicit transaction on the connection) and once all those statements are completed, the read locks are released.  You can BEGIN another transaction on the same connection (or another connection) and the locks will be escalated as you requested in the same fashion as would normally be expected for an in-progress implicit transaction.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Keith Medcalf
>Sent: Saturday, 2 March, 2019 19:52
>To: SQLite mailing list
>Subject: Re: [sqlite] Handling ROLLBACK
>
>
>Actually, in current versions of SQLite3 the error "(516)
>SQLITE_ABORT_ROLLBACK" is returned by "other commands" and not by the
>rollback command.  If the ROLLBACK command completed successfully
>(that is, it returned YaHoo! SQLITE_OK) then the transaction changes
>have been rolled back.  If "some other command" returns an error
>indicating that it was ABORTed then that other command was aborted
>and that has no bearing on whether or not changes to the database
>were rolled back.  They were.
>
>I presume that if a SELECT was in progress during the time that a
>ROLLBACK was processed on the SAME CONNECTION and that the SELECT is
>allowed to proceed, that it will maintain the REPEATABLE READ
>isolation that it was commenced with and that when that statement is
>finalized, the transaction context will be released.
>
>Whether the connection is still "inside" the transaction or not can
>be determined by using the sqlite3_autocommit() API.
>
>Since this is kind of an undocumented grey area, I would suggest that
>you do not make the programming errors which would result in this
>situation (a Schrodinger Transaction state, for lack of a better
>descriptive).
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>
>>-----Original Message-----
>>From: sqlite-users [mailto:sqlite-users-
>>[hidden email]] On Behalf Of Keith Medcalf
>>Sent: Saturday, 2 March, 2019 19:32
>>To: SQLite mailing list
>>Subject: Re: [sqlite] Handling ROLLBACK
>>
>>
>>>Suppose ROLLBACK does not cancel the BEGIN, can a programmer
>>reliably
>>>issue more SQL commands, including another ROLLBACK ?  Will SQLite
>>>continue to react correctly to other ROLLBACKs, and to SQL commands
>>>which result in "(516) SQLITE_ABORT_ROLLBACK".
>>
>>A successful ROLLBACK on a transaction discards all changes that
>were
>>made during the transaction (rolls back to the state at the time the
>>transaction began), and ends the transaction.  It does not "cancel"
>>the "BEGIN", it merely exits the transaction and discards changes
>>made during the transaction.  Since the BEGIN occurred in the past,
>>it cannot be "cancelled" ...
>>
>>Full Stop End of Line.
>>
>>However, a ROLLBACK command which produces the error "(516)
>>SQLITE_ABORT_ROLLBACK" would indicate that the ROLLBACK was NOT
>>PERFORMED due to an error.  Therefore the transaction is STILL IN
>>EFFECT and has neither been COMMITed nor ROLLBACKed.  Once the
>>programming error which prevented the rollback is cleared the
>>ROLLBACK command can be retried.  Once a transaction is opened it
>>remains in effect until ROLLBACK or COMMIT complete successfuly (or
>>the heat death of the universe, whichever shall come first).
>>
>>Note that this discussion does not include "fatal errors" which may
>>occur within the context of a transaction that abort the transaction
>>itself.
>>
>>---
>>The fact that there's a Highway to Hell but only a Stairway to
>Heaven
>>says a lot about anticipated traffic volume.
>>
>>
>>
>>_______________________________________________
>>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: Handling ROLLBACK

Simon Slavin-3
To summarize, the list feels that this is an incorrect model

    BEGIN;
    ... first set of commands
    ROLLBACK;
    ... second set of commands
    END;

whereas this is how things are meant to work:

    BEGIN;
    ... first set of commands
    ROLLBACK;
    BEGIN;
    ... second set of commands
    END;

and that since ROLLBACK ends a transaction, it releases locks.  The above is correct for all journalling models except for OFF, where the effect of ROLLBACK is undefined.  (I'm ignoring SAVEPOINTs for now.)

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: Handling ROLLBACK

Richard Damon
On Mar 3, 2019, at 8:32 AM, Simon Slavin <[hidden email]> wrote:

>
> To summarize, the list feels that this is an incorrect model
>
>    BEGIN;
>    ... first set of commands
>    ROLLBACK;
>    ... second set of commands
>    END;
>
> whereas this is how things are meant to work:
>
>    BEGIN;
>    ... first set of commands
>    ROLLBACK;
>    BEGIN;
>    ... second set of commands
>    END;
>
> and that since ROLLBACK ends a transaction, it releases locks.  The above is correct for all journalling models except for OFF, where the effect of ROLLBACK is undefined.  (I'm ignoring SAVEPOINTs for now.)
>
> Simon.
>

And the way to get the first pattern is to set a SAVEPOINT right after the BEGIN and restore back to it instead of using ROLLBACK.
_______________________________________________
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: Handling ROLLBACK

Luuk

On 3-3-2019 15:01, Richard Damon wrote:

> On Mar 3, 2019, at 8:32 AM, Simon Slavin <[hidden email]> wrote:
>> To summarize, the list feels that this is an incorrect model
>>
>>     BEGIN;
>>     ... first set of commands
>>     ROLLBACK;
>>     ... second set of commands
>>     END;
>>
>> whereas this is how things are meant to work:
>>
>>     BEGIN;
>>     ... first set of commands
>>     ROLLBACK;
>>     BEGIN;
>>     ... second set of commands
>>     END;
>>
>> and that since ROLLBACK ends a transaction, it releases locks.  The above is correct for all journalling models except for OFF, where the effect of ROLLBACK is undefined.  (I'm ignoring SAVEPOINTs for now.)
>>
>> Simon.
>>
> And the way to get the first pattern is to set a SAVEPOINT right after the BEGIN and restore back to it instead of using ROLLBACK.
> _______________________________________________

Conclusion: RESTORE does not end TRANSACTION ?

or am i missing something important in this discussion ;)

sqlite> DELETE FROM test WHERE i>=11;
sqlite> .dump test
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(i int primary key);
INSERT INTO test VALUES(1);
INSERT INTO test VALUES(2);
INSERT INTO test VALUES(3);
INSERT INTO test VALUES(4);
INSERT INTO test VALUES(10);
COMMIT;
sqlite>
sqlite> BEGIN;
sqlite> SAVEPOINT point1;
sqlite> INSERT INTO test VALUES (10);
Error: UNIQUE constraint failed: test.i
sqlite> ROLLBACK;
sqlite> INSERT INTO test VALUES (11);
sqlite> INSERT INTO test VALUES (12);
sqlite> END;
Error: cannot commit - no transaction is active
sqlite> SELECT * FROM test;
1
2
3
4
10
11
12
sqlite>
sqlite>
sqlite>
sqlite>
sqlite> DELETE FROM test WHERE i>=11;
sqlite> .dump test
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(i int primary key);
INSERT INTO test VALUES(1);
INSERT INTO test VALUES(2);
INSERT INTO test VALUES(3);
INSERT INTO test VALUES(4);
INSERT INTO test VALUES(10);
COMMIT;
sqlite>
sqlite> BEGIN;
sqlite> SAVEPOINT point1;
sqlite> INSERT INTO test VALUES (10);
Error: UNIQUE constraint failed: test.i
sqlite> RELEASE point1;
sqlite> INSERT INTO test VALUES (11);
sqlite> INSERT INTO test VALUES (12);
sqlite> END;
sqlite> SELECT * FROM test;
1
2
3
4
10
11
12
sqlite>

_______________________________________________
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: Handling ROLLBACK

Simon Slavin-3
On 3 Mar 2019, at 2:29pm, Luuk <[hidden email]> wrote:

> Conclusion: RESTORE does not end TRANSACTION ?

Your statement is correct.  However, RESTORE is a partner of SAVEPOINT.  My question does not consider SAVEPOINTs.

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: Handling ROLLBACK

Luuk

On 3-3-2019 15:33, Simon Slavin wrote:

> On 3 Mar 2019, at 2:29pm, Luuk <[hidden email]> wrote:
>
>> Conclusion: RESTORE does not end TRANSACTION ?
> Your statement is correct.  However, RESTORE is a partner of SAVEPOINT.  My question does not consider SAVEPOINTs.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Something like this (SAVEPOINT/RELEASE):

sqlite> DELETE FROM test WHERE i>=11;
sqlite> .dump test
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(i int primary key);
INSERT INTO test VALUES(1);
INSERT INTO test VALUES(2);
INSERT INTO test VALUES(3);
INSERT INTO test VALUES(4);
INSERT INTO test VALUES(10);
COMMIT;
sqlite>
sqlite> BEGIN;
sqlite> SAVEPOINT point1;
sqlite> INSERT INTO test VALUES (10);
Error: UNIQUE constraint failed: test.i
sqlite> RELEASE point1;
sqlite> INSERT INTO test VALUES (11);
sqlite> INSERT INTO test VALUES (12);
sqlite> END;
sqlite> SELECT * FROM test;
1
2
3
4
10
11
12
sqlite>

Something like this (NO SAVEPOINT/RELEASE):

sqlite> DELETE FROM test WHERE i>=11;
sqlite> .dump test
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(i int primary key);
INSERT INTO test VALUES(1);
INSERT INTO test VALUES(2);
INSERT INTO test VALUES(3);
INSERT INTO test VALUES(4);
INSERT INTO test VALUES(10);
COMMIT;
sqlite>
sqlite> BEGIN;
sqlite>
sqlite> INSERT INTO test VALUES (10);
Error: UNIQUE constraint failed: test.i
sqlite> RELEASE point1;
Error: no such savepoint: point1
sqlite> INSERT INTO test VALUES (11);
sqlite> ROLLBACK;
sqlite> INSERT INTO test VALUES (12);
sqlite> END;
Error: cannot commit - no transaction is active
sqlite> SELECT * FROM test;
1
2
3
4
10
12
sqlite>

Record '11' is missing, seems OK because off ROLLBACK

But i'am a bit confused about the error "Error: cannot commit - no
transaction is active"

_______________________________________________
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: Handling ROLLBACK

Jesse Rittner
It didn't work correctly because the sequence of commands you sent doesn't
make sense.

BEGIN;
...
RELEASE point1;
...
ROLLBACK;
...
END;

First you began an explicit transaction. Then you tried to release a
savepoint that you never created. (Hence the "no such savepoint" error
message.) Then you rolled back the explicit transaction. Then you tried to
end (i.e., commit) the explicit transaction, but you already rolled back, so
this is invalid. (Hence the "no transaction is active" error message.)

I'm not quite sure what you were trying to do. It appears you want a nested
transaction, which can only be accomplished with savepoints.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
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: Handling ROLLBACK

Luuk

On 3-3-2019 16:27, Jesse Rittner wrote:

> It didn't work correctly because the sequence of commands you sent doesn't
> make sense.
>
> BEGIN;
> ...
> RELEASE point1;
> ...
> ROLLBACK;
> ...
> END;
>
> First you began an explicit transaction. Then you tried to release a
> savepoint that you never created. (Hence the "no such savepoint" error
> message.) Then you rolled back the explicit transaction. Then you tried to
> end (i.e., commit) the explicit transaction, but you already rolled back, so
> this is invalid. (Hence the "no transaction is active" error message.)
>
> I'm not quite sure what you were trying to do. It appears you want a nested
> transaction, which can only be accomplished with savepoints.
>
>
i was just 'testing' a bit with transactions (and savepoints),

in order to try to understand the question of Simon.

But it is still no very clear to /me what the meaning of his question
is/was.


_______________________________________________
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: Handling ROLLBACK

Rowan Worth-2
In reply to this post by Keith Medcalf
On Sun, 3 Mar 2019 at 20:53, Keith Medcalf <[hidden email]> wrote:

> My observation (on the current tip version 3.28.0) of Schrodingers
> Transactions is that if there is (for example) a transaction in progress
> and that is COMMIT or ROLLBACK, then the changes are either committed or
> rolled back and the explicit transaction is ended (that is, autocommit
> becomes True).
>

You kind of covered this in a previous email where you talked about "COMMIT
or ROLLBACK command completing successfully", but sqlite has a special case
around COMMIT which I think is worth mentioning in detail:

If COMMIT fails with SQLITE_BUSY, it means the EXCLUSIVE lock could not be
obtained within the configured timeout, because of other concurrent
activity on the DB. In this case, the transaction's changes are not
committed or rolled back -- it _remains open_. It is then up to the
programmer to decide whether to ROLLBACK and give up, or try to COMMIT
again at a later date.


> Statements which were in progress that were permitted to proceed (ie,
> where the next step did not return an abort error) continue with a read
> lock in place (ie, as if they were part of an implicit transaction on the
> connection) and once all those statements are completed, the read locks are
> released.  You can BEGIN another transaction on the same connection (or
> another connection) and the locks will be escalated as you requested in the
> same fashion as would normally be expected for an in-progress implicit
> transaction.
>

Wait what? If I've understood correctly you're describing a situation where
statements outlive their transaction context? Something like:


    sqlite3 *db; // initialised elsewhere

    sqlite3_stmt *stmt;
    int rc;

    sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL);
    stmt = sqlite3_prepare_v2(db, "SELECT * FROM some_table", -1, &stmt, 0);
    rc = sqlite3_step(stmt); // advance to first row
    sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);

    rc = sqlite3_step(stmt); // advance to second row?
    ...
    sqlite3_finalize(stmt);

And the sqlite3_step() following the transaction acquires a new read-lock?
Or it prevents the COMMIT from dropping the read-lock?

It seems bizarre that this is even possible, so I may have misunderstood!
-Rowan
_______________________________________________
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: Handling ROLLBACK

Keith Medcalf
On Monday, 4 March, 2019 20:23, Rowan Worth <[hidden email]> wrote:

>On Sun, 3 Mar 2019 at 20:53, Keith Medcalf <[hidden email]> wrote:

>> Statements which were in progress that were permitted to proceed
>> (ie, where the next step did not return an abort error) continue
>> with a read lock in place (ie, as if they were part of an implicit
>> transaction on the connection) and once all those statements are
>> completed, the read locks are released.  You can BEGIN another
>> transaction on the same connection (or another connection)
>> and the locks will be escalated as you requested in the
>> same fashion as would normally be expected for an in-progress
>> implicit transaction.

> Wait what? If I've understood correctly you're describing a
> situation where statements outlive their transaction context?

>Something like:
>
>
>    sqlite3 *db; // initialised elsewhere
>
>    sqlite3_stmt *stmt;
>    int rc;
>
>    sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL);
>    stmt = sqlite3_prepare_v2(db, "SELECT * FROM some_table", -1,
>&stmt, 0);
>    rc = sqlite3_step(stmt); // advance to first row
>    sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
>
>    rc = sqlite3_step(stmt); // advance to second row?
>    ...
>    sqlite3_finalize(stmt);
>
>And the sqlite3_step() following the transaction acquires a new read-
>lock?
>Or it prevents the COMMIT from dropping the read-lock?

>It seems bizarre that this is even possible, so I may have
>misunderstood!

It appears that changes are committed (assuming that the COMMIT was successful) however the read lock is not released (that is, after the commit the changes are visible to other connections).  However, there is no longer a transaction in progress on the original connection (the one the commit was issued against) and it is in autocommit mode and still holding a read lock (and is still repeatable read with respect to changes committed on another connection).  

I believe this is consistent with the documentation and operates appropriately (that is, as would be expected) for the journaling mode (delete or wal).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.





_______________________________________________
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: Handling ROLLBACK

Simon Slavin-3
In reply to this post by Rowan Worth-2
On 5 Mar 2019, at 3:22am, Rowan Worth <[hidden email]> wrote:

> Wait what? If I've understood correctly you're describing a situation where statements outlive their transaction context?

The documentation on transactions covers this possibility.  This includes continuing to do _step() for one statement while another executes END or ROLLBACK.  Your guess about how it is treated is correct.

Nevertheless I consider this behaviour extremely high-risk and would reject any production program which depended on it as the product of a poor programmer.

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