The desired effect is that if any statement inside the transaction fails, the entire transaction is rolled back.

Then you should say that in your CREATE TABLE (I've also corrected your quote usage, as using double-quotes for literal strings will get you an error in more recent SQLite versions):

create table job(
    name text not null,
    billable real not null,
    check(billable > 0.0) on conflict rollback
);

begin transaction;
insert into job values ('calibrate', 1.5);
insert into job values ('reset', -0.5);
insert into job values ('clean', 0.5);
commit;

select * from job;

Do read the ON CONFLICT page for details on the difference between ROLLBACK and ABORT. The behavior you see is both correct and SQL-standard.

Thanks for the note about quote usage - old habits die hard :-). I tried on conflict rollback (apologies for not including that in the original post), but it doesn't seem to handle the multi-table case I mentioned. My full application wants to do something like this (using on conflict rollback to illustrate, but on conflict abort produces the same result):

create table job(
    name text not null,
    billable real not null,
    check(billable > 0.0) on conflict rollback
);
create table person(
   name text not null,
   job text not null
);

begin transaction;
insert into job values ('calibrate', 1.5);
insert into person values ('adrian', 'calibrate');
insert into job values ('reset', -0.5); -- this fails
insert into person values ('greg', 'reset');
commit;

If I run this and then select * from each table in turn:

|   name    | billable |
|-----------|----------|
| calibrate | 1.5      |

|  name  |    job    |
|--------|-----------|
| adrian | calibrate |
| greg   | reset     |

So the insert into job with an illegal value was rolled back, but what I want is to roll back the entire transaction: both tables should be empty. Table-level on conflict doesn't seem to do this (which I guess is reasonable); can I get the multi-table behavior I want, and if so, how?

In https://www.sqlite.org/lang_createtable.html click Show under table-constraint: to see that conflict_clause is not available to Check constraints, only Primary Key and Unique.

Thank you, but returning to my original problem: how then do I implement a multi-statement transaction that only-but-always rolls back if an error occurs in any of the enclosed statements? (See my second post on this thread showing inserts into two different tables, one of which fails deliberately.)

Which seems to conflict (pun intended) with this statement in the ON CONFLICT page:

The ON CONFLICT clause applies to UNIQUE, NOT NULL, CHECK, and PRIMARY KEY constraints.

Even more "conflicts"!

In https://sqlite.org/lang_createtable.html#ckconst and below § 4.1 "Response to constraint violations" one can read:

The conflict resolution algorithm for CHECK constraints is always ABORT. (For historical compatibility only, table CHECK constraints are allowed to have a conflict resolution clause, but that has no effect.)

Good catch, that'll learn me to read docs in the dead of night. :)

Thanks for your feedback:

1. I am running these commands from a file rather than interactively in order to save typing: sqlite3 example.db < example.sql.

2. I don't want SQLite to stop at the first error: I want to roll back the entirety of a particular multi-table transaction without affecting (in particular, without aborting or rolling back) anything outside that transaction in the same SQL file. Is that possible?

SQLite does not work that way.

What you are asking for is some like this: If any command anywhere in a transaction failed, then when doing the COMMIT for the transaction, do a ROLLBACK instead.

With SQLite, each command succeeds or fails on its own. The transaction does not try to remember whether or not any commands have failed during that transaction. The COMMIT causes all commands that succeeded during the transaction to be committed.

this from someone who doesn't fully understand the nitty gritty of transactions (but wants to), if I understand you correctly, the following would be the SQLite way?

BEGIN TRANSACTION;
WITHDRAW 10000 FROM savings; ←-- succeeds
DEPOSIT 10000 INTO checking; ←-- fails
COMMIT;

edited to add a more db-like example

BEGIN TRANSACTION;
UPDATE savings SET balance = balance - 10000 WHERE rowid = 5;
-- failure happens here ← 
UPDATE checking SET balance = balance + 10000 where rowid = 88;
COMMIT;

What would happen differently if the above was not wrapped in that transaction?

It seems either way (with or without transactions) I am out 10000? I (perhaps mistakenly) thought the whole purpose of wrapping commands in a transaction was to get "all or nothing".

I realize the OP is working with an in-memory db, but for more normal, on disk dbs, I thought the reason transactions were so fast was because SQLite would save only when the transaction would successfully complete. Any failure in the transaction would mean nothing would get written to the db. Seems like I have misunderstood transactions all this time.

Your application sees that deposit fails and does not issue commit.

You get nothing.

which is exactly the behavior I was expecting from SQLite since it is the one providing the transaction mechanism. As I said, seems I have been completely misunderstanding this so far…

What transactions get you is the ability to rollback, and for other connections to not see anything until you commit. If one of your queries fail and you plow on regardless, executing queries anyway, that's on you. Whatever program you have needs to account for queries failing and handle it appropriately. SQLite's job is to try to execute queries; if you give it a query you do not want it to execute, that's your fault.