CODEWITHSUNDEEP
androidtransactionsandroid-sqlite
DroidOS
DroidOS

Reputation: 8900

How do SQLite transactions on Android work?

My understanding of SQLite transactions on Android is based largely on this article. In its gist it suggests that

if you do not wrap calls to SQLite in an explicit transaction it will create an implicit transaction for you. A consequence of these implicit transactions is a loss of speed

.

That observation is correct - I started using transactions to fix just that issue:speed. In my own Android app I use a number of rather complex SQLite tables to store JSON data which I manipulate via the SQLite JSON1 extension - I use SQLCipher which has JSON1 built in.

At any given time I have to manipulate - insert, update or delete - rows in several tables. Given the complexity of the JSON I do this with the help of temporary tables I create for each table manipulation. The start of the manipulation begins with SQL along the lines of

DROP TABLE IF EXISTS h1;
CREATE TEMP TABLE h1(v1 TEXT,v2 TEXT,v3 TEXT,v4 TEXT,v5 TEXT);

Some tables require just one table - which I usually call h1 - others need two in which case I call them h1 and h2.

The entire sequence of operations in any single set of manipulations takes the form

begin transaction
    manipulate Table 1 which
        which creates its own temp tables, h1[h2], 
        then extracts relevant existing JSON from Table 1 into the temps
        manipulates h1[h2]
        performs inserts, updates, deletes in Table 1
    on to the next table, Table 2 where the same sequence is repeated
    continue with a variable list of such tables - never more than 5
end transaction

My questions

I have to admit to not being an expert with SQL, even less so with SQLite and quite a newbie when it comes to using transactions. The SQLite JSON extension is very powerful but introduces a whole new level of complexity when manipulating data.

Upvotes: 1

Views: 209

Answers (1)

MikeT
MikeT

Reputation: 57043

The main reason to use transactions is to reduce the overheads of writing to the disk.

So if you don't wrap multiple changes (inserts, deletes and updates) in a transaction then each will result in the database being written to disk and the overheads involved.

If you wrap them in a transaction and the in-memory version will be written only when the transaction is completed (note that if using the SQLiteDatabase beginTransaction/endTransaction methods, that you should, as part of ending the transaction use the setTransactionSuccessful method and then use the endTransaction method).

That is, the SQLiteDatabase method are is different to doing this via pure SQL when you'd begin the transaction and then end/commit it/them (i.e. the SQLiteDatabase methods would otherwise automatically rollback the transactions).

Saying that the statement :-

if you do not wrap calls to SQLite in an explicit transaction it will create an implicit transaction for you. A consequence of these implicit transactions is a loss of speed

basically reiterates :-

Any command that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed when the last query finishes.

SQL As Understood By SQLite - BEGIN TRANSACTION i.e. it's not Android specific.

does this sound like an efficient way to do things or would it be better to wrap each individual table operation in its own transaction?

Doing all the operations in a single transaction will be more efficient as there is just the single write to disk operation.

it is not clear to me what happens to my DROP TABLE/CREATE TEMP TABLE calls. If I end up with h1[h2] temp tables that are pre-populated with data from manipulating Table(n - 1) when working with Table(n) then the updates on Table(n) will go totally wrong. I am assuming that the DROP TABLE bit I have is taking care of this issue. Am I right in assuming this?

Dropping the tables will ensure data integrity (i.e. you should, by the sound of it, do this), you could also use :-

CREATE TEMP TABLE IF NOT EXISTS h1(v1 TEXT,v2 TEXT,v3 TEXT,v4 TEXT,v5 TEXT);
DELETE FROM h1;

Upvotes: 1

Related Questions