On 3-3-2019 15:01, Richard Damon wrote:
On Mar 3, 2019, at 8:32 AM, Simon Slavin <slav...@bigfraud.org> 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
