[sqlite] bug in transactions implementation ?
Pardon the long post everyone, but it seems from the other posts there
is a large misconception to address here and I will attempt to do so as
briefly as possible (Also, feel free to chime in where I am vague or wrong):
On 2015-03-18 11:24 PM, Jason Vas Dias wrote:
> OK, I discovered the OR clause of the INSERT statement, so I'm trying:
>
> BEGIN TRANSACTION;
> INSERT OR ROLLBACK INTO db VALUES("This breaks a constraint");
> COMMIT;
>
> But now I get another error after the constraint violation :
>'Error: cannot commit - no transaction is active'
This is exactly what should happen and exactly what you've asked for.
You started a transaction, instructed it to INSERT some values but
roll-back the transaction if the insert fails. So then the insert fails
and your transaction got rolled back... and then you told it to commit,
which can't possibly work because the transaction that was is now no
more, it got rolled back - remember?
SQL is not a programming language, it doesn't execute lines of code. It
can follow DATA instructions and you may string those together in a list
(as you do) for easier usage. If a transaction fails however, this is a
problem, not an opportunity for more coding, one typically needs to read
that error code in your program/connector/ODBC/list processor/whatever
and make a decision on what to do next (such as roll back and re-start
the transaction or do something else etc.) and then ask SQL to do it
based on your decision. SQL itself is not a decision-making program,
it's just a Database engine which tries to do stuff with Data that you
ask it to, and it will error out very unceremoniously if what you ask is
bogus.
Your job is to see what went wrong, fix it so that next time you ask it
to do the thing, it doesn't fail (i.e. you should have a mechanism in
place to make sure you are not asking it to insert duplicates, and if
this error still happens, you need to fix your mechanism). There is no
way to ask via SQL only to try this or that, and if it fails, maybe try
something else please - this is the decision-making domain of programs.
(btw: This is true for ALL SQL engines, not just SQLite)
You can however tell it to ignore duplicated requests, such as:
INSERT OR IGNORE INTO "t" (myVal) VALUES ('This is a duplicate');
Which will simply NOT insert the value when the duplication occurs and
continue without rolling back or moaning about it. You have to be sure
this is what you want to happen though. If you need it to error out,
then it will, but you can't ask it to pick another rabbit hole to go
down whence an error might have occurred.
> This seems very buggy to me.
That's ok - It only seems that way. SQLite is amongst the least-buggy of
the SQL engines (though not free of it - but the above is not a bug in
any way).
Also, Feel free to post questions - if you can say exactly what you want
to happen, lots of people here will gladly help you achieve that in the
best possible way or offer methods to get to the goal in more and better
ways than ever seen before. However, calling "bug" due to
misunderstanding will probably have the opposite effect.
Best of luck !
Ryan
[sqlite] bug in transactions implementation ?
> On Mar 18, 2015, at 10:24 PM, Jason Vas Dias > wrote: > > This seems very buggy to me. Correct. http://www.styleite.com/wp-content/uploads/2014/11/legallyblonde.gif
[sqlite] bug in transactions implementation ?
> On Mar 18, 2015, at 9:45 PM, Jason Vas Dias > wrote: > > Would you care to expand on that ? As it says on the tin [1]: you cannot start a transactions inside another transaction (use savepoint if you want that), so? create table foo( value text, constraint uk unique( value ) ); begin transaction; insert into foo( value ) values( 'bar' ); commit; ? ok begin transaction; insert into foo( value ) values( 'bar' ); commit; ? Error: UNIQUE constraint failed: foo.value begin transaction; insert into foo( value ) values( 'baz' ); commit; ? Error: cannot start a transaction within a transaction rollback; ? ok begin transaction; insert into foo( value ) values( 'baz' ); commit; ? ok [1] https://www.sqlite.org/lang_transaction.html
[sqlite] bug in transactions implementation ?
On 18 Mar 2015, at 9:24pm, Jason Vas Dias wrote: > But now I get another error after the constraint violation : > 'Error: cannot commit - no transaction is active' > which gets back to the original point of this post, > which is that SQLite is evidently not considering > all text within 'BEGIN TRANSACTION; ... ; $EOT;' As I explained to you, the way you are passing your commands to the shell tool is messing them up. You are packaging together multiple commands in a pipe and your Unix shell is not passing them to sqlite3 correctly. I don't know which Unix shell you're using, or whether it is processing single and double-quotes as you expect, but something somewhere is messing up your stream of commands before your commands are seen by the sqlite3 program. In my earlier post I showed you two different ways of executing your own series of SQL commands and getting the right results in the right places. If you use either of the methods I showed you with this new INSERT OR ROLLBACK command you will get the right result in the right place. While I'm here, I noticed that you are quoting a text string in your command using double-quotes. This is not correct for SQL, which uses single quotes (non-directional apostrophes) around text strings. Double-quotes have a different meaning in SQL. However, this is not (as far as I can tell) causing the problem you are reporting. Simon.
[sqlite] bug in transactions implementation ?
> On Mar 18, 2015, at 9:11 PM, Jason Vas Dias > wrote: > > am I missing something? rollback?
[sqlite] bug in transactions implementation ?
OK, I discovered the OR clause of the INSERT statement, so I'm trying:
BEGIN TRANSACTION;
INSERT OR ROLLBACK INTO db VALUES("This breaks a constraint");
COMMIT;
But now I get another error after the constraint violation :
'Error: cannot commit - no transaction is active'
which gets back to the original point of this post,
which is that SQLite is evidently not considering
all text within 'BEGIN TRANSACTION; ... ; $EOT;'
to be part of the same transaction (for $EOT in COMMIT, ROLLBACK, END
TRANSACTION etc.) ; or if a transaction fails, it considers the next
transaction to be part of the failed transaction (and so disallows
another BEGIN TRANSACTION ).
This seems very buggy to me.
On 18/03/2015, Simon Slavin wrote:
>
> On 18 Mar 2015, at 8:11pm, Jason Vas Dias wrote:
>
>> The problem is, if this transaction runs in a session, then
>> NO transactions can ever run again in that session - eg.
>> if I try to run the same transaction twice :
>> On linux command line:
>> $ echo '
>> BEGIN TRANSACTION;
>> INSERT INTO db VALUES("This breaks a constraint");
>> COMMIT;
>> BEGIN TRANSACTION;
>> INSERT INTO db VALUES("This breaks a constraint");
>> COMMIT;
>> ' | sqlite3 my_db_file.db
>> Error: near line 1: UNIQUE constraint failed: db.some_field
>> Error: near line 2: cannot start a transaction within a tranaction
>>
>> It is the second error that worries me, since it shows that the
>> first failed transaction was not closed, even though I had clearly
>> written "COMMIT;" at the end of it, and no transactions
>> can ever run again in the same session.
>
> Which Unix/Linux command shell are you using ? I'm guessing that what
> you're seeing makes no sense because of the way the sqlite3 is receiving the
> lines from the shell. If I execute the commands singly I get this:
>
> 164:Desktop simon$ sqlite3 ~/Desktop/test.sdb
> SQLite version 3.8.5 2014-08-15 22:37:57
> Enter ".help" for usage hints.
> sqlite> CREATE TABLE myTable (myCol TEXT UNIQUE);
> sqlite> INSERT INTO myTable VALUES ('This breaks a constraint.');
> sqlite> BEGIN;
> sqlite> INSERT INTO myTable VALUES ('This breaks a constraint.');
> Error: UNIQUE constraint failed: myTable.myCol
> sqlite> COMMIT;
> sqlite> BEGIN;
> sqlite> INSERT INTO myTable VALUES ('This breaks a constraint.');
> Error: UNIQUE constraint failed: myTable.myCol
> sqlite> COMMIT;
> sqlite>
>
> which is fine. If I then use your command in bash I get this:
>
> 164:Desktop simon$ echo '
>> BEGIN TRANSACTION;
>> INSERT INTO myTable VALUES("This breaks a constraint.");
>> COMMIT;
>> BEGIN TRANSACTION;
>> INSERT INTO myTable VALUES("This breaks a constraint.");
>> COMMIT;
>> ' | sqlite3 ~/Desktop/test.sdb
> Error: near line 3: UNIQUE constraint failed: myTable.myCol
> Error: near line 6: UNIQUE constraint failed: myTable.myCol
> 164:Desktop simon$
>
> which is again fine.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
[sqlite] bug in transactions implementation ?
On 18 Mar 2015, at 8:11pm, Jason Vas Dias wrote:
> The problem is, if this transaction runs in a session, then
> NO transactions can ever run again in that session - eg.
> if I try to run the same transaction twice :
> On linux command line:
> $ echo '
> BEGIN TRANSACTION;
> INSERT INTO db VALUES("This breaks a constraint");
> COMMIT;
> BEGIN TRANSACTION;
> INSERT INTO db VALUES("This breaks a constraint");
> COMMIT;
> ' | sqlite3 my_db_file.db
> Error: near line 1: UNIQUE constraint failed: db.some_field
> Error: near line 2: cannot start a transaction within a tranaction
>
> It is the second error that worries me, since it shows that the
> first failed transaction was not closed, even though I had clearly
> written "COMMIT;" at the end of it, and no transactions
> can ever run again in the same session.
Which Unix/Linux command shell are you using ? I'm guessing that what you're
seeing makes no sense because of the way the sqlite3 is receiving the lines
from the shell. If I execute the commands singly I get this:
164:Desktop simon$ sqlite3 ~/Desktop/test.sdb
SQLite version 3.8.5 2014-08-15 22:37:57
Enter ".help" for usage hints.
sqlite> CREATE TABLE myTable (myCol TEXT UNIQUE);
sqlite> INSERT INTO myTable VALUES ('This breaks a constraint.');
sqlite> BEGIN;
sqlite> INSERT INTO myTable VALUES ('This breaks a constraint.');
Error: UNIQUE constraint failed: myTable.myCol
sqlite> COMMIT;
sqlite> BEGIN;
sqlite> INSERT INTO myTable VALUES ('This breaks a constraint.');
Error: UNIQUE constraint failed: myTable.myCol
sqlite> COMMIT;
sqlite>
which is fine. If I then use your command in bash I get this:
164:Desktop simon$ echo '
> BEGIN TRANSACTION;
> INSERT INTO myTable VALUES("This breaks a constraint.");
> COMMIT;
> BEGIN TRANSACTION;
> INSERT INTO myTable VALUES("This breaks a constraint.");
> COMMIT;
> ' | sqlite3 ~/Desktop/test.sdb
Error: near line 3: UNIQUE constraint failed: myTable.myCol
Error: near line 6: UNIQUE constraint failed: myTable.myCol
164:Desktop simon$
which is again fine.
Simon.
[sqlite] bug in transactions implementation ?
On 18/03/2015, Petite Abeille wrote: > >> On Mar 18, 2015, at 9:11 PM, Jason Vas Dias >> wrote: >> >> am I missing something? > > rollback? > Would you care to expand on that ? How is the script consisting of just the two insert statements shown meant to determine if the first transaction has failed or not ? I'm just using the sqlite3 shell to run sql scripts . How can one determine the status of the previous transaction in the shell, or if the current transaction needs to be rolled back or not ? Thanks & Regards, Jason > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] bug in transactions implementation ?
Good day -
There appears to be a bug in the way SQLite implements transactions :
I have an insert transaction which breaks a uniqueness constraint on an index:
BEGIN TRANSACTION;
INSERT INTO db VALUES("This breaks a constraint");
COMMIT;
The problem is, if this transaction runs in a session, then
NO transactions can ever run again in that session - eg.
if I try to run the same transaction twice :
On linux command line:
$ echo '
BEGIN TRANSACTION;
INSERT INTO db VALUES("This breaks a constraint");
COMMIT;
BEGIN TRANSACTION;
INSERT INTO db VALUES("This breaks a constraint");
COMMIT;
' | sqlite3 my_db_file.db
Error: near line 1: UNIQUE constraint failed: db.some_field
Error: near line 2: cannot start a transaction within a tranaction
It is the second error that worries me, since it shows that the
first failed transaction was not closed, even though I had clearly
written "COMMIT;" at the end of it, and no transactions
can ever run again in the same session.
I thought that if a transaction fails, its effects are meant to be
rolled back, and subsequent further transactions can proceed.
Is this bug in the sqlite transaction implementation or am I missing something?
Thanks for any responses,
Regards,
Jason
[sqlite] bug in transactions implementation ?
Works perfectly fine for me. Note that if you are submitting the commands in a single batch, then the abort action of INSERT OR ABORT aborts the batch. Submitting individual statements works just fine. You are submitting the script as a single sql statement rather than multiple statements (ie, save the script into a file with one command per line and it will work just peachy ... 2015-03-18 18:13:30 [D:\Temp] >sqlite < abort1.sql Error: near line 2: no such table: foo create table foo( value text, constraint uk unique( value ) ); begin transaction; insert into foo( value ) values( 'bar' ); commit; begin transaction; insert into foo( value ) values( 'bar' ); Error: near line 8: UNIQUE constraint failed: foo.value commit; begin transaction; insert into foo( value ) values( 'baz' ); commit; begin transaction; insert into foo( value ) values( 'baz' ); Error: near line 14: UNIQUE constraint failed: foo.value commit; 2015-03-18 18:13:53 [D:\Temp] >sqlite < abort2.sql Error: near line 2: no such table: foo create table foo( value text, constraint uk unique( value ) ); begin transaction; insert into foo( value ) values( 'bar' ); commit; begin transaction; insert into foo( value ) values( 'bar' ); Error: near line 5: UNIQUE constraint failed: foo.value begin transaction; Error: near line 6: cannot start a transaction within a transaction begin transaction; Error: near line 7: cannot start a transaction within a transaction --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-Original Message- >From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- >bounces at mailinglists.sqlite.org] On Behalf Of Petite Abeille >Sent: Wednesday, 18 March, 2015 15:02 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] bug in transactions implementation ? > > >> On Mar 18, 2015, at 9:45 PM, Jason Vas Dias >wrote: >> >> Would you care to expand on that ? > >As it says on the tin [1]: you cannot start a transactions inside another >transaction (use savepoint if you want that), so? > >create table foo( value text, constraint uk unique( value ) ); >begin transaction; insert into foo( value ) values( 'bar' ); commit; ? ok >begin transaction; insert into foo( value ) values( 'bar' ); commit; ? >Error: UNIQUE constraint failed: foo.value >begin transaction; insert into foo( value ) values( 'baz' ); commit; ? >Error: cannot start a transaction within a transaction >rollback; ? ok >begin transaction; insert into foo( value ) values( 'baz' ); commit; ? ok > > >[1] https://www.sqlite.org/lang_transaction.html > > >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
