[sqlite] bug in transactions implementation ?

[Jason Vas Dias]

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