How to implement rollback of multi-statement transaction in SQLite?

Question

Using sqlite3 3.43.2 on macOS, I feed the following into an otherwise-empty in-memory database:

.mode markdown
.headers on

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

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;

I expect to get nothing from the final select because there is a check violation in the second insert inside a transaction, but instead the other two insert statements have executed:

Runtime error near line 12: CHECK constraint failed: billable > 0.0 (19)
|   name    | billable |
|-----------|----------|
| calibrate | 1.5      |
| clean     | 0.5      |

1. Removing on conflict abort from the table definition does not change this behavior.
2. Replacing on conflict abort with on conflict rollback in the table definition does not change this behavior.

All right: let's move the error handling to the insert statements. In a fresh in-memory database:

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

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

select * from job;

The output is:

Runtime error near line 12: CHECK constraint failed: billable > 0.0 (19)
Runtime error near line 14: cannot commit - no transaction is active
| name  | billable |
|-------|----------|
| clean | 0.5      |

which shows that SQLite carried on and executed the third statement inside the transaction (which inserted clean) after the error in the second statement in that transaction. I expected that when the error occurred in the second statement, the entire transaction would abort, i.e., SQLite wouldn't even try the third insert. (My mental model is exceptions being raised and caught in programming languages.)

Finally, in yet another fresh database:

insert or rollback into job values ("calibrate", 1.5);
insert or rollback into job values ("reset", -0.5);
insert or rollback into job values ("clean", 0.5);

produces:

Runtime error near line 11: CHECK constraint failed: billable > 0.0 (19)
|   name    | billable |
|-----------|----------|
| calibrate | 1.5      |
| clean     | 0.5      |

which is what I expect, but not what I want.

Is there a way to create a true multi-statement transaction in SQLite? If so, what is the syntax? I want something like:

-- this is not valid SQLite syntax
begin transaction;
insert into job values ("calibrate", 1.5);
insert into job values ("reset", -0.5);
insert into job values ("clean", 0.5);
commit or rollback;

but as noted, that doesn't parse. The desired effect is that if any statement inside the transaction fails, the entire transaction is rolled back.

Answer 1

Is this maybe an alternative acceptable behavior?

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

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

-- Runtime error: CHECK constraint failed: billable > 0.0 (19)
select * from job; -- zero rows

edit for clarity; you need to decide how to handle successful statements:

begin transaction;
insert into job values ("calibrate", 1.5);
insert into job values ("reset", -0.5);
-- error: CHECK constraint failed: billable > 0.0 (19)
insert into job values ("clean", 0.5);
rollback;
select * from job; -- zero rows

or

begin transaction;
insert into job values ("calibrate", 1.5);
insert into job values ("reset", -0.5);
-- error: CHECK constraint failed: billable > 0.0 (19)
insert into job values ("clean", 0.5);
commit;
select * from job;
/*
calibrate|1.5
clean|0.5
*/