CODEWITHSUNDEEP
sqlitetransactions
Max
Max

Reputation: 131

How to write sqlite transaction that rolls back on any error

I have searched extensively on this and I have found a lot of people asking the question but no answers that included code examples to help me understand.

I'd like to write a transaction (in sql using the command line sqlite3 interface) that performs several update statements, and if any of them fail for any reason, rolls back the transaction. The default behaviour appears to be to roll-back the statement that failed but commit the others.

This tutorial appears to advise that it's sufficient to add begin; and rollback; before and after the statements, but that's not true because I've tried it with deliberate errors and the non-error statements were definitely committed (which I don't want).

This example really confuses me because the two interlocutors seem to give conflicting advice at the end - one says that you need to write error handling (without giving any examples) whereas the other says that no error handling is needed.

My MWE is as follows:

create table if not exists accounts (
id integer primary key not null,
balance decimal not null default 0
);

insert into accounts (id, balance) values (1,200),(2,300);

begin transaction;
update accounts set balance = field1 - 100 where id = 1;
update accounts set balance = field1 + 100 where id = 2;
update accounts set foo = 23; //Deliberate error
commit;

The idea is that none of these changes should be committed.

Upvotes: 3

Views: 2863

Answers (2)

Juan
Juan

Reputation: 5589

If you are executing line by line, then the idea is that you first run these commands:

create table if not exists accounts (
id integer primary key not null,
balance decimal not null default 0
);

insert into accounts (id, balance) values (1,200),(2,300);

begin transaction;
update accounts set balance = field1 - 100 where id = 1;
update accounts set balance = field1 + 100 where id = 2;
update accounts set foo = 23; //Deliberate error

At this point, if you have no errors, you run the commit:

commit;

All the updates should be visible if you open a second connection and query the table.

On another hand if you got an error, instead of committing you rollback:

rollback;

All the updates should be rolled back;

If you are doing it programatically in java you would enclose the updates in a try - catch block, and commit at the end of the try, or rollback inside the catch.

Upvotes: -1

CL.
CL.

Reputation: 180030

The sqlite3 command-line shell is intended to be used interactively, so it allows you to continue after an error.

To abort on the first error instead, use the -bail option:

sqlite3 -bail my.db < mwe.sql

Upvotes: 4

Related Questions