SpatiaLite logo

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:
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:

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

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

GNU logo 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.