Note: these pages are no longer maintained

Never the less, much of the information is still relevant.
Beware, however, that some of the command syntax is from older versions, and thus may no longer work as expected.
Also: external links, from external sources, inside these pages may no longer function.

	Recipe #9: ACIDity: undestranding Transactions
2011 January 28

Previous Slide

Table of Contents

Next Slide

ACID has nothing to do with chemistry (pH, hydrogen and hydroxide ions and so on).
In the DBMS context this one is an acronym meaning:

Atomicity
Consistency
Insulation
Durability

Very simply explained:

a DBMS is designed to store complex data: sophisticated relations and constraints have to be carefully checked and validated.
Data self-consistency has to be strongly preserved anyway.
each time an INSERT, UPDATE or DELETE statement is performed, data self-consistency is at risk.
If one single change fails (for any reason), this may leave the whole DB in an inconsistent state.
any properly ACID compliant DBMS brilliantly resolves any such potential issue.

The underlying concept is based on a TRANSACTION-based approach:

a TRANSACTION encloses an arbitrary group of SQL statements.
a TRANSACTION is granted to be performed as an atomic unit, adopting an all-or-nothing approach.
- if any statement enclosed within a TRANSACTION successfully completes, than the TRANSACTION itself can successfully complete.
- but if a single statement fails, then the whole TRANSACTION will fail: and the DB will be left exactly in the previous state, as it was before the TRASACTION started.
that's not all: any change occurred in a TRANSACTION context is absolutely invisible to any other DBMB connection, because a TRANSACTION defines an insulated private context.

Anyway, performing some direct test surely is the simplest way to understand TRANSACTIONs.

BEGIN;

CREATE TABLE test (
num INTEGER,
string TEXT);

INSERT INTO test (num, string)
VALUES (1, 'aaaa');

INSERT INTO test (num, string)
VALUES (2, 'bbbb');

INSERT INTO test (num, string)
VALUES (3, 'cccc');

The BEGIN statement will start a TRANSACTION:

after this declaration, any subsequent statement will be handled within the current TRANSACTION context.
you can also use the BEGIN TRANSACTION alias, but this is redundantly verbose, and not often used.
SQLite forbids multiple nested transactions: you can simply declare an unique pending TRANSACTION at each time.

SELECT *
FROM test;

You can now check your work: there is nothing odd in this, isn't ?
Absolutely anything looks as expected.

Anyway, some relevant consequence arises from the initial BEGIN declaration:

now you have a still pending (unfinished, not completed) TRANSACTION
you can perform a first simple check:
- open a second spatialite_gui instance, connecting the same DB
- are you able to see the test table ?
- NO: because this table has been created in the private (insulated) context of the first spatialite_gui instance, and so for any other different connection this table simply does not yet exists.
and than you can perform a second check:
- quit both spatialite_gui instances.
- then launch again spatialite_gui.
- there is no test table at all: it seems disappeared, completely vanishing.
- but all this is easily explained: the corresponding TRANSACTION was never confirmed.
- and when the holding connection terminated, then SQLite invalidated any operation within this TRANSACTION, so to leave the DB exactly in the previous state.

COMMIT;

ROLLBACK;

Once you BEGIN a TRANSACTION, any subsequent statement will be left in a pending (uncommitted) state.
Before or after you are expected to:

close positively the TRANSACTION (confirm), by declaring a COMMIT statement.
- any change applied to the DB will be confirmed and consolidated in a definitive way.
- such changes will become immediately visible to other connections.
close negatively the TRANSACTION (invalidate), by declaring a ROLLBACK statement.
- any change applied to the DB will be rejected: the DB will be reverted to the previous state.
if you omit declaring neither COMMIT nor ROLLBACK, then SQLite prudentially assumes that the still pending TRANSACTION is an invalid one, and a ROLLBACK will be implicitly performed.
if any error or exception is encountered within a TRANSACTION context, than the whole TRANSACTION is invalidated, and a ROLLBACK is implicitly performed.

Performance Hints

Handling TRANSACTIONs seems too much complex to you ? so you are thinking "I'll simply ignore all this ..."
Well, carefully consider that SQLite is a full ACID DBMS, so it's purposely designed to handle TRANSACTIONs. And that's not all.
SQLite actually is completely unable to operate outside a TRANSACTION context.
Each time you miss to explicitly declare some BEGIN / COMMIT, then SQLite implicitly enters the so called AUTOCOMMIT mode:
each single statement will be handled as a self-standing TRANSACTION.

i.e. when you declare e.g. some simple INSERT INTO ... statement, then SQLite silently translates this into:
BEGIN;
INSERT INTO ...;
COMMIT;

Please note well: this is absolutely safe and acceptable when you are inserting few rows by hand-writing.
But when some C / C++ / Java / Python process attempts to INSERT many and many rows (maybe many million rows), this will impose an unacceptable overhead.
In other words, your process will perform very poorly, taking an unneeded long time to complete: and all this is simply caused by not declaring an explicit TRANSACTION.

The strongly suggested way to perform fast INSERTs (UPDATE, DELETE ...) is the following one:

explicitly start a TRANSACTION (BEGIN)
loop on INSERT as long as required.
confirm the pending TRANSACTION (COMMIT).

And this simple trick will grant you very brilliant performances.

Connectors oddities (true life tales)

Developers, be warned: different languages, different connectors, different default settings ...

C / C++ developers will directly use the SQLite's API: in this environment the developer is expected to explicitly declare TRANSACTIONs as required, by calling:

sqlite3_exec (db_handle, "BEGIN", NULL, NULL, &err_msg);
sqlite3_exec (db_handle, "COMMIT", NULL, NULL, &err_msg);

Java / JDBC connectors more or less follow the same approach: the developer is expected to explicitly quit the AUTOCOMMIT mode, then declaring a COMMIT when required and appropriate:

conn.setAutoCommit(false);
conn.commit();

Shortly said: in C / C++ and Java the developer is required to start a TRANSACTION in order to perform fast DB INSERTs.
Omitting this step will cause very slow performance. But at least any change will surely affect the underlying DB.

Python follows a completely different approach: a TRANSACTION is silently active at each time.
Performance always is optimal.
But forgetting to explicitly call conn.commit() before quitting, any applied change will be lost forever immediately after terminating the connection.
And this may really be puzzling for beginners, I suppose.

Previous Slide

Table of Contents

Next Slide

	Author: Alessandro Furieri a.furieri@lqt.it
	This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license.

	Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts.