>A transaction is ended when you issue the COMMIT command. A transaction >fails if any command which changes the database in it fails due to >violating the schema. If a transaction fails then all commands in it are >automatically ignored. There's no need to use ROLLBACK. You correctly >grouped commands together into a transaction and SQL knows that if any of >them fail none of them must be executed.
This is incorrect. A statement may fail, however that does not affect other statements within the transaction. You still have to end a transaction with either a commit (to commit the changes made by statements WHICH DID NOT FAIL) or rollback to discard the changes made by the statements which did not fail. If you however submit a statement BATCH (that is, say 400 INSERT statements as a single line of text) and ONE of them ABORTS, then the batch is aborted (that is, no further statements in the batch are executed, from that point on -- statements which have executed successfully are NOT rolled back by magic). COMMIT will result in committing the database operations in the batch prior to the ABORT, and ROLLBACK will roll them all back. The default conflict resolution method for INSERT is ABORT. That means that the current statement is aborted and no further statements in the same batch (text string submitted for execution) are executed. It has no effect whatsoever on the state of the transaction, which is still open. This is why there is a conflict resolution method IGNORE: >sqlite < abort2.sql Error: near line 2: no such table: foo create table foo( value text, constraint uk unique( value ) ); begin transaction; insert or ignore into foo( value ) values( 'bar' ); commit; begin transaction; insert or ignore into foo( value ) values( 'bar' ); commit; begin transaction; insert or ignore into foo( value ) values( 'baz' ); commit; begin transaction; insert or ignore into foo( value ) values( 'baz' ); commit; ** In my original example, abort1.sql contains: .echo on drop 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' ); commit; begin transaction; insert into foo( value ) values( 'baz' ); commit; begin transaction; insert into foo( value ) values( 'baz' ); commit; ** abort2.sql now contains (INSERT changed to INSERT OR IGNORE): .echo on drop table foo; create table foo( value text, constraint uk unique( value ) ); begin transaction; insert or ignore into foo( value ) values( 'bar' ); commit; begin transaction; insert or ignore into foo( value ) values( 'bar' ); commit; begin transaction; insert or ignore into foo( value ) values( 'baz' ); commit; begin transaction; insert or ignore into foo( value ) values( 'baz' ); commit; --- 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.
