[sqlite] System.Data.SQLite version 1.0.110.0 released

[Joe Mistachkin]

System.Data.SQLite version 1.0.110.0 (with SQLite 3.27.2) is now available
on the System.Data.SQLite website:

 https://system.data.sqlite.org/

Further information about this release can be seen at:

 https://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki

Please post on the SQLite mailing list (sqlite-users at
mailinglists.sqlite.org) if you encounter any problems with this release.

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling ROLLBACK

[Luuk]

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

On 3 Mar 2019, at 2:29pm, Luuk  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
sqlite-users@mailinglists.sqlite.org
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling ROLLBACK

[Simon Slavin]

On 3 Mar 2019, at 2:29pm, Luuk  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling ROLLBACK

[Luuk]

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

On Mar 3, 2019, at 8:32 AM, Simon Slavin  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling ROLLBACK

[Richard Damon]

On Mar 3, 2019, at 8:32 AM, Simon Slavin  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Documentation change request

[Simon Slavin]

talks about

PRAGMA read_uncommitted = boolean;

Explicitly stating that the argument should be boolean.  However the 
explanation of the effect of the PRAGMA talks about its argument as if it is 
SERIALIZABLE.  No boolean value to indicate SERIALIZABLE is stated.

I agree that it's possible to infer this from the name of the setting but 
perhaps an OFF or ON could be added for those ESL readers who don't immediately 
understand the word SERIALIZABLE.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling ROLLBACK

[Simon Slavin]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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-
>boun...@mailinglists.sqlite.org] 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-
>>boun...@mailinglists.sqlite.org] 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
>>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