Android, SQLite connections and transactions

Question

I'm currently grokking the SQLiteDatabase and related classes with the purpose of getting a better understanding of how it handles concurrency (my current solution works, but I got the impression I might actually be doing redundant work, since its done with the presumption that concurrency is not handled for me).

Right, about the android documentation for SQLiteOpenHelper states the following about the getWriteableDatabase method

Once opened successfully, the database is cached, so you can call this method every time you need to write to the database. (Make sure to call close() when you no longer need the database.)

It's a little unclear to me what is being cached(so I currently have a wrapper that does the caching for me). So, what is going on here? Are the two SQLiteDatabase instances wrappers around a cached/static instance?

Also, how is the actual locking handled? If I have two separate SQLiteDatabase instances, created by the same SQLiteOpenHelper instance, are these then transactional safe? As in if i begin a transaction in exclusive mode on one transaction, and then start another transaction in another thread, on the other SQLiteDatabase instance, I would expect the second one to not start until the first one is done. Is this how it works?

Answer 1

"Caching" .. well, I guess you could call it that. The SQLiteOpenHelper typically has a single SQLiteDatabase stored internally (since the last call to create a db), so calling #getWritableDatabase() twice will give you the same object the 2nd time (unless you closed the first instance - then it will create it again). Actually, calling #getReadableDatabase() will attempt to open the database r/w and only fall back on returning a read-only instance if that fails.

Note, closing databases & calling #getWritableDatabase() is not thread-safe - i.e. the SQLiteOpenHelper can return closed databases if you mess with it in many threads.

In short, you're going to have a single instance of SQLiteDatabase - but you can enable locking - making it thread-safe - by calling SQLiteDatabase#setLockingEnabled(true).